Prerequisite: Creating an Oracle Directory and grant access for this directory to the Owner of External Table
Creating the directory
CREATE OR REPLACE DIRECTORY my_dir AS '/u01/extab_dir';
Granting access for this directory to Owner of external table
GRANT READ, WRITE ON DIRECTORY my_dir TO extab_owner;
Sample external table create statement for unix file with blank as column delimiter
CREATE TABLE my_table_ext ( field0 VARCHAR2(2000) , field1 VARCHAR2(2000) , field2 VARCHAR2(2000) , field3 VARCHAR2(2000) , field4 VARCHAR2(2000) , field5 VARCHAR2(2000) , field6 VARCHAR2(2000) , field7 VARCHAR2(2000) , field8 VARCHAR2(2000) , field9 VARCHAR2(2000) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY my_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY newline BADFILE my_dir:'my_table.bad' DISCARDFILE my_dir:'amy_table.dsc' LOGFILE my_dir:'my_table.log' CHARACTERSET AL32UTF8 SKIP 0 FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"' LRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ) LOCATION (my_dir:'my_table.txt') ) REJECT LIMIT 0;