External table

An external table is a flat file outside the Oracle database. If properly described in Oracle, it can be read as an Oracle table.

The syntax consists of 3 elements:

• a create table part
• an ORGANIZATION EXTERNAL part that indicates that we are discussing an external table
• a set of attributes that discusses
  • how the data are read (ORACLE_LOADER),
  • how the data are accessed – between parenthesis you find how the records are read, how the fields are separated and
  • what the fields are and
  • finally what the location is.

CREATE TABLE fam_external_table
 (
 nmbr NUMBER,
 fname VARCHAR2(10),
 lname VARCHAR2(20)
 )
 ORGANIZATION EXTERNAL
 (
 TYPE ORACLE_LOADER DEFAULT DIRECTORY EXTERN
 ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE
 FIELDS terminated BY ";"
 REJECT ROWS WITH ALL NULL
 FIELDS(nmbr, fname,lname) )
 LOCATION ('familie.txt')
 );

Doing so, we the data as a normal Oracle table:

A brilliant example is given in https://asktom.oracle.com/pls/asktom/asktom.search?tag=external-tables-200206

A nice feature is provided in SQL Developer. Via the import facility one may generate an external file. The usage is straight forward.

The generated script then looks like:

–CREATE OR REPLACE DIRECTORY EXTERNORA AS ‘C:\Users\tomva\externora’;
–GRANT READ ON DIRECTORY EXTERNORA TO USER;
–GRANT WRITE ON DIRECTORY EXTERNORA TO USER;
–drop table PRUT;
CREATE TABLE PRUT
( EMPLOYEE_ID NUMBER(38),
FIRST_NAME VARCHAR2(128),
LAST_NAME VARCHAR2(128),
EMAIL VARCHAR2(128),
PHONE_NUMBER VARCHAR2(128),
HIRE_DATE NUMBER(38),
JOB_ID VARCHAR2(128),
SALARY NUMBER(38),
COMMISSION_PCT NUMBER(38, 2),
MANAGER_ID NUMBER(38),
DEPARTMENT_ID NUMBER(38))
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTERNORA
ACCESS PARAMETERS
(records delimited BY ‘\r\n’
NOBADFILE
NODISCARDFILE
NOLOGFILE
skip 1
fields terminated BY ‘|’
OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘
lrtrim
missing field VALUES are NULL
( EMPLOYEE_ID CHAR(4000),
FIRST_NAME CHAR(4000),
LAST_NAME CHAR(4000),
EMAIL CHAR(4000),
PHONE_NUMBER CHAR(4000),
HIRE_DATE CHAR(4000),
JOB_ID CHAR(4000),
SALARY CHAR(4000),
COMMISSION_PCT CHAR(4000),
MANAGER_ID CHAR(4000),
DEPARTMENT_ID CHAR(4000)
)
)
LOCATION (‘export.csv’)
)
REJECT LIMIT UNLIMITED;