Database Reference
In-Depth Information
(DEPTNO, DNAME, LOC)
(5): This tells SQLLDR what columns you are loading, their order in
the input data, and their datatypes. The datatypes are for the data in the
input
stream, not the
datatypes in the database. In this case, they are defaulting to
CHAR(255)
, which is sufficient.
•
BEGINDATA
(6): This tells SQLLDR you have finished describing the input data and that the very
next lines, lines 7 to 10, are the actual data to be loaded into the
DEPT
table.
•
This is a control file in one of its most simple and common formats: to load delimited data into a table. We will
take a look at some much more complex examples in this chapter, but this is a good one to get our feet wet with.
To use this control file, which we will name
demo1.ctl
, all we need to do is create an empty
DEPT
table:
EODA@ORA12CR1> create table dept
2 ( deptno number(2) constraint dept_pk primary key,
3 dname varchar2(14),
4 loc varchar2(13)
5 )
6 /
Table created.
And run the following command:
$ sqlldr userid=eoda control=demo1.ctl
Password:
SQL*Loader: Release 12.1.0.1.0 - Production on Sun Mar 9 12:03:26 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 4
Table DEPT:
4 Rows successfully loaded.
Check the log file:
demo1.log
If the table is not empty, we will receive an error message to the following effect:
SQL*Loader-601: For INSERT option, table must be empty. Error on table DEPT
This is because we allowed almost everything in the control file to default, and the default load option is
INSERT
(as opposed to
APPEND
,
TRUNCATE
, or
REPLACE
). To
INSERT
, SQLLDR assumes the table is empty. If we wanted to
add
records to the
DEPT
table, we could have specified
APPEND
; to replace the data in the
DEPT
table, we could have used
REPLACE
or
TRUNCATE. REPLACE
uses a conventional
DELETE FROM TABLE
statement to remove records; hence, if the
table to be loaded into already contains many records, it could be quite slow to perform.
TRUNCATE
uses the
TRUNCATE
SQL command and is typically faster, as it does not have to physically remove each row.
