Oracle - 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.
First create the directory in the operating system, or choose an existing directory. It must be a real directory, not a symlink. Make sure that the OS user that the Oracle binaries run as has read-write access to this directory. Note: Be sure not to use a directory you should be keeping secure, such as an Oracle datafile, program, log or configuration file directory. And if the data you'll be putting there is sensitive, make sure that other OS users don't have permissions on this directory.
$ cd /oracle/feeds/
$ mkdir xtern
$ mkdir xtern/mySID
$ mkdir xtern/mySID/data
$ ls -l /oracle/feeds/xtern/mySID
total 8
drwx------ 2 oracle oinstall 4096 Mar 1 17:05 data

Put the external table's data file in the data directory. In this example, I'll use the following CSV file:
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

You must actually move or copy the file to the data directory; symlinks won't cut it. Again, make sure that if the data is sensitive, only the Oracle user can read or write to it.
The next step is to create this directories in Oracle, and grant read/write access on it to the Oracle user who will be creating the external table. When you create the directory, be sure to use the directory's full path, and don't include any symlinks in the path -- use the actual full path.


SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create or replace directory xtern_data_dir
2 as '/oracle/feeds/xtern/mySID/data';
Directory created.
SQL> grant read,write on directory xtern_data_dir to bulk_load;
Grant succeeded.

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.
SQL> connect bulkload
Enter password:
Connected.
SQL> create table xtern_empl_rpt
2 ( empl_id varchar2(3),
3 last_name varchar2(50),
4 first_name varchar2(50),
5 ssn varchar2(9),
6 email_addr varchar2(100),
7 years_of_service number(2,0)
8 )
9 organization external
10 ( default directory xtern_data_dir
11 access parameters
12 ( records delimited by newline
13 fields terminated by ','
14 )
15 location ('employee_report.csv')
16 );
Table created.

At this point, Oracle hasn't actually tried to load any data. It doesn't attempt to check the validity of many of the external-table-specific parameters you pass it. The CREATE TABLE statement will succeed even if the external data file you specify doesn't actually exist.
With the create table statement, you've created table metadata in the data dictionary and instructed Oracle how to direct the ORACLE_LOADER access driver to parse the data in the datafile. Now, kick off the load by accessing the table:
SQL> select * from xtern_empl_rpt ;
EMP LAST_NAME FIRST_NAME SSN EMAIL_ADDR YEARS_OF_SERVICE
--- ---------- ---------- --------- ------------------------------ ----------------
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
6 rows selected.

Comment