How to generate External Table from sql loader

How to generate External Table from sql loader

In many cases we might want to use external table to load data rather than Sql*Loader because in external table we can load data parallel. In this post I will show how we can generate external table easily with help of sql loader. With this method we can easily get rid of writing too many codes for external tables.

Suppose my data file is 3.txt which is under C drive on my windows machine and contains a single record,
1, momin

The next step is to create a control file for SQL loader. I named it control.ctl made it as follows,
LOAD DATA
infile 'c:\3.txt'
into table std
fields terminated by ','
(id , name)

Now invoke the Sql loader as below. Note that addition word that need is external_table=generate_only. With this keyword only externa table will be created but will not load any data actually on table.

sqlldr user_id/password control=c:\control.ctl.txt log=5.txt external_table=generate_only

After invoking I open the log file and important contents from logfile is,

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'C:\'
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'c:\'


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_STD"
(
"ID" NUMBER,
"NAME" VARCHAR2(10)
)
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':'3.bad'
LOGFILE 'c:\5.txt_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY ",",
"NAME" CHAR(255)
TERMINATED BY ","
)
)
location
(
'3.txt'
)
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO STD
(
ID,
NAME
)
SELECT
"ID",
"NAME"
FROM "SYS_SQLLDR_X_EXT_STD"


statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_STD"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000