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
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:
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
SQL> create or replace directory xtern_data_dir
2 as '/oracle/feeds/xtern/mySID/data';
SQL> grant read,write on directory xtern_data_dir to bulk_load;
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
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)
9 organization external
10 ( default directory xtern_data_dir
11 access parameters
12 ( records delimited by newline
13 fields terminated by ','
15 location ('employee_report.csv')
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 email@example.com 18
002 Simpson Homer 382947382 firstname.lastname@example.org 20
003 Kent Clark 082736194 email@example.com 5
004 Kid Billy 928743627 firstname.lastname@example.org 9
005 Stranger Perfect 389209831 email@example.com 23
006 Zoidberg Dr 094510283 firstname.lastname@example.org 1
6 rows selected.