The external tables feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database.
When you query the table, oracle reads the external table and returns the results just as if the data had been stored with in the database.
For example, suppose that you receive a daily .csv report from another department. Instead of writing a SQL*Loader script to import each day's .csv file into your database, you can simply create an external table and write an "insert ... select" SQL query to insert the data directly into your tables. Place the day's CSV file in the location specified in the external table definition, run the query, and you're done.
Creating an external table
Since an external table's data is in the operating system, its data file needs to be in a place Oracle can access it. So the first step is to create a directory and grant access to it.
Step 1: CREATE DIRECTORY
Step 1: CREATE DIRECTORY
CREATE OR REPLACE DIRECTORY MY_DIR AS 'D:\ORACLE';
Step 2: CHECK YOUR CSV file data "employee_report.csv"
001,Hutt,Jabba,896743856,jabba@thecompany.com,18
002,Simpson,Homer,382947382,homer@thecompany.com,20
003,Kent,Clark,082736194,superman@thecompany.com,5
004,Kid,Billy,928743627,billythkid@thecompany.com,9
005,Stranger,Perfect,389209831,nobody@thecompany.com,23
006,Zoidberg,Dr,094510283,crustacean@thecompany.com,1
Step 3: The last step is to create the table. The CREATE TABLE statement for an external table has two parts. The first part, like a normal CREATE TABLE, has the table name and field specs. This is followed by a block of syntax specific to external tables, which lets you tell Oracle how to interpret the data in the external file.
CREATE TABLE xtern_empl_rpt
(
empl_id VARCHAR2(3),
last_name VARCHAR2(50),
first_name VARCHAR2(50),
ssn VARCHAR2(9),
email_addr VARCHAR2(100),
years_of_service NUMBER(2, 0)
)
organization external ( DEFAULT directory xtern_data_dir ACCESS parameters(
records delimited BY
newline fields terminated BY ',' ) location (
'employee_report.csv') );
(
empl_id VARCHAR2(3),
last_name VARCHAR2(50),
first_name VARCHAR2(50),
ssn VARCHAR2(9),
email_addr VARCHAR2(100),
years_of_service NUMBER(2, 0)
)
organization external ( DEFAULT directory xtern_data_dir ACCESS parameters(
records delimited BY
newline fields terminated BY ',' ) location (
'employee_report.csv') );
Step 4: Check the table data
select * from xtern_empl_rpt ;
You can use ALTER TABLE to change the access parameters without dropping and redefining the whole table:
ALTER TABLE xtern_empl_rpt
access parameters
(records delimited BY newline
badfile xtern_log_dir : 'xtern_empl_rpt.bad'
LOGFILE xtern_log_dir : 'xtern_empl_rpt.log'
discardfile xtern_log_dir : 'xtern_empl_rpt.dsc'
fields terminated BY ','
);
access parameters
(records delimited BY newline
badfile xtern_log_dir : 'xtern_empl_rpt.bad'
LOGFILE xtern_log_dir : 'xtern_empl_rpt.log'
discardfile xtern_log_dir : 'xtern_empl_rpt.dsc'
fields terminated BY ','
);
Loading the data into your tables
Where external tables really shine are in the ease with which you can load their data into your tables. A particularly nice feature is that you can use any valid function that the current Oracle user has rights on to transform the raw data before loading it into your database tables. For example, suppose you had a function, get_bday_from_ssn (ssn in varchar2) that looked up an employee's birth date given their SSN. You can use that function to populate a BIRTH_DATE column in your local database table in the same step as you load the data into it.
CREATE TABLE empl_info AS
(SELECT empl_id,
last_name,
first_name,
ssn,
Get_bday_from_ssn (ssn) birth_dt
FROM xtern_empl_rpt);
Table created.
(SELECT empl_id,
last_name,
first_name,
ssn,
Get_bday_from_ssn (ssn) birth_dt
FROM xtern_empl_rpt);
Table created.
Now check the table data
SELECT * FROM empl_info;
EMP LAST_NAME FIRST_NAME SSN BIRTH_DT
--- ---------- ---------- --------- ----------
001 Hutt Jabba 896743856 03/11/1939
002 Simpson Homer 382947382 11/01/1967
003 Kent Clark 082736194 01/15/1925
004 Kid Billy 928743627 07/20/1954
005 Stranger Perfect 389209831 10/23/1980
006 Zoidberg Dr 094510283 04/04/2989
6 rows selected.
Unloading data into an external file
Oracle 10g lets you create a new external table from data in your database, which goes into a flat file pushed from the database using the ORACLE_DATAPUMP access driver. This flat file is in an Oracle-proprietary format that can be read by DataPump. The syntax is similar to the CREATE TABLE... ORGANIZATION EXTERNAL above, but simpler -- since you can't specify the data format, you can specify very few access_parameters. The key difference is that you must specify the access driver, ORACLE_DATAPUMP, since the access driver defaults to ORACLE_LOADER.
CREATE TABLE export_empl_info
organization external
( TYPE oracle_datapump DEFAULT directory xtern_data_dir
location ('empl_info_rpt.dmp') ) AS
SELECT *
FROM empl_info;
Table created.
organization external
( TYPE oracle_datapump DEFAULT directory xtern_data_dir
location ('empl_info_rpt.dmp') ) AS
SELECT *
FROM empl_info;
Table created.
Now Check the table:
select * from export_empl_info ;
EMPL_ID LAST_NAME FIRST_NAME SSN BIRTH_DT
------- --------------- --------------- --------- ----------
001 Hutt Jabba 896743856 01/01/1979
002 Simpson Homer 382947382 01/01/1979
003 Kent Clark 082736194 01/01/1979
004 Kid Billy 928743627 01/01/1979
005 Stranger Perfect 389209831 01/01/1979
006 Zoidberg Dr 094510283 01/01/1979
6 rows selected.
You can now move the file you just created, empl_info_rpt.dmp, to another system and create an external table to read the data:
CREATE TABLE import_empl_info
(
empl_id VARCHAR2(3),
last_name VARCHAR2(50),
first_name VARCHAR2(50),
ssn VARCHAR2(9),
birth_dt DATE
)
organization external
(
TYPE oracle_datapump DEFAULT directory xtern_data_dir location ('empl_info_rpt.dmp')) ;
TABLE created.
(
empl_id VARCHAR2(3),
last_name VARCHAR2(50),
first_name VARCHAR2(50),
ssn VARCHAR2(9),
birth_dt DATE
)
organization external
(
TYPE oracle_datapump DEFAULT directory xtern_data_dir location ('empl_info_rpt.dmp')) ;
TABLE created.
Now Check the table:
select * from import_empl_info ;
EMPL_ID LAST_NAME FIRST_NAME SSN BIRTH_DT
------- --------------- --------------- --------- ----------
001 Hutt Jabba 896743856 01/01/1979
002 Simpson Homer 382947382 01/01/1979
003 Kent Clark 082736194 01/01/1979
004 Kid Billy 928743627 01/01/1979
005 Stranger Perfect 389209831 01/01/1979
006 Zoidberg Dr 094510283 01/01/1979
6 rows selected.
LIMITATIONS ON EXTERNAL TABLES
1. You can not perform insert, update, and delete operations
2. Indexing not possible
3. Constraints not possible
1. You can not perform insert, update, and delete operations
2. Indexing not possible
3. Constraints not possible
BENEFITS OF EXTERNAL TABLES
1. Queries of external tables complete very quickly even though a full table scan id required with each access
2. You can join external tables to each other or to standard tables
1. Queries of external tables complete very quickly even though a full table scan id required with each access
2. You can join external tables to each other or to standard tables
