Database Reference
In-Depth Information
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'dept_%p.bad'
LOGFILE 'dept_%p.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"DEPTNO" CHAR(255),
"DNAME" CHAR(255),
"LOC" CHAR(255)
)
)
location
(
'dept.dat'
)
)REJECT LIMIT UNLIMITED
That is followed by a direct path
INSERT
statement that can be used to load data from the external table into the
regular database table (
DEPT
in this example):
INSERT /*+ append parallel(auto) */ INTO DEPT
(
DEPTNO,
DNAME,
LOC
)
SELECT
"DEPTNO",
"DNAME",
"LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT"
Lastly, the temporary table and directory object are dropped:
DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
The idea being that when you run SQLLDR in express mode with
EXTERNAL_TABLE=GENERATE_ONLY
, you can use
the contents of the log file to run the SQL statements manually from SQL (if so desired).
■
Tip
For full details of all options available with SQLLDr express mode, refer to the
Oracle Database Utilities
manual.
Dealing with Errors
In a perfect world, there would be no errors. The data in the input file would be perfect, and it would all load correctly.
That almost never happens. So, how can we track errors with this process?
