External Table and the whole story

Oracle External Tables is introduced in Oracle 9i. External tables are used to load data in to database. This is similar to oracle SQLLDR mechanism to load data.

In Oracle 10g, using external tables, we can also unload data.
There are the following scenarios where we cant use Oracle external tables.
1. If the source need to be loaded via network. External table requires the source file need to be in the database server. If not we need to use SQLLDR to load data.
2. LOBs cant be loaded using external tables

The following are the advantages of external tables over SQLLDR.
1. External tables open up the entire SQL set of functionality to data loading21
2. The ability to use complex WHERE conditions to selectivity load data. SQLLDR has WHEN clause but there are limitations like only = and 'AND' can be used. We cant use 'OR' or
other relational operator.
3. The ability to MERGE data.
4. Easier multiple inserts using INSERT.

The external table synax would be as below
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'D:\srikar\oracle'


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_EMP2"
(
"EMPNO" NUMBER,
"EMPNAME" VARCHAR2(2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'empdata.bad'
LOGFILE 'logg.txt_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255)
TERMINATED BY ",",
"EMPNAME" CHAR(255)
TERMINATED BY ","
)
)
location
(
'empdata.csv'
)
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO EMP2
(
EMPNO,
EMPNAME
)
SELECT
"EMPNO",
"EMPNAME"
FROM "SYS_SQLLDR_X_EXT_EMP2"

The above code is obtained from the control file in the command line with below command

D:\srikar\oracle>sqlldr scott/abcd@orcl control=emp_ctl.txt log=logg.txt external_table=generate_only

That produces the whole DDL in the logg.txt file
Here, 'external_table' takes 3 parameters
NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED).
'GENERATE_ONLY' creates the code as if it is executed (It does not execute the code)
'EXECUTE' executes the code hence loads the data.

If we look at the create external table script,

CREATE TABLE "SYS_SQLLDR_X_EXT_EMP2"
(
"EMPNO" NUMBER,
"EMPNAME" VARCHAR2(2)
)
ORGANIZATION external --> this is to make the table as external
(
TYPE oracle_loader
--> driver to load data,'DATA_PUMP' is used to unload data in 10g
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 --> directory name created
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'empdata.bad'
LOGFILE 'logg.txt_xt'
READSIZE 1048576 --> Buffer size to load the input file if we use shared memory - --the size is taken from SGA else from PGA
FIELDS TERMINATED BY "," LDRTRIM --LDRTRIM is loader trim, we can also use ltrim, -- rtrim, lrtrim
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255)
TERMINATED BY ",",
"EMPNAME" CHAR(255)
TERMINATED BY ","
)
)
location
(
'empdata.csv'
)
)REJECT LIMIT UNLIMITED

If we explicitly create load data, first of all we need to create directory
the syntax as below

create directory dd as 'D:\srikar'
The oracle software is the one which loads/writes data. So the oracle software must have permission ('read' in windows, 'read and execute' in UNIX) to read the directory. 'Write' permission on the directory where we save 'log', 'bad' and 'discard' file