Database Reference
In-Depth Information
Lastly, we get to the LOCATION section of the external table definition:
location
(
'demo1.ctl'
)
) REJECT LIMIT UNLIMITED
This tells Oracle the name of the file to load, which is demo1.ctl in this case since we used INFILE * in the
original control file. The next statement in the control file is the default INSERT that can be used to load the table from
the external table itself:
INSERT statements used to load internal tables:
-----------------------------------------------
INSERT /*+ append */ INTO DEPT
(
DEPTNO,
DNAME,
LOC
)
SELECT
"DEPTNO",
"DNAME",
"LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT"
This would perform the logical equivalent of a direct path load if possible (assuming the APPEND hint may be
obeyed; the existence of triggers or foreign key constraints may prevent the direct path operation from taking place).
Lastly, in the log file, we'll see statements that may be used to remove the objects SQLLDR would have us create
after the load was complete:
statements to cleanup objects created by previous statements:
-------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
And that is it. If we take that log file and insert / where appropriate to make it a valid SQL*Plus script, then we
should be ready to go—or not, depending on the permissions in place. For example, assuming the schema I log
into has the CREATE ANY DIRECTORY privilege or READ and WRITE access to an existing directory, I might observe the
following:
EODA@ORA12CR1> INSERT /*+ append */ INTO DEPT
2 (
3 DEPTNO,
4 DNAME,
5 LOC
6 )
7 SELECT
8 "DEPTNO",
9 "DNAME",
10 "LOC"
 
Search WWH ::




Custom Search