About
The control file describes the data file and the data operations that must be performed.
Articles Related
Syntax
-- SQL Loader Command line option
OPTIONS(DIRECT=true)
-- What you will do
load data
-- Which file
infile 'myFile.csv' "str '\r\n'"
-- Which actions: INSERT,APPEND,LOAD,REPLACE
REPLACE
-- Into Which table
into table MYTABLE
-- How are the fields in the file terminated
fields terminated by ','
-- How are the field enclose (in case of you get a termination punctuation in the field)
OPTIONALLY ENCLOSED BY '"' AND '"'
-- Trailing null cols
trailing nullcols
-- Columns Mapping between the file and the database
(
ORDER_CODE CHAR(4000),
ORDER_OMSCHRIJVING CHAR(4000),
ORDER_DATUM_DEFINITIEF_GEREED DATE "DD-MM-YY HH24:MI",
ORDER_DATUM_TECHNISCHGEREED DATE "DD-MM-YY HH24:MI",
ORDER_STATUS CHAR(4000)
)
Articles Related
Operations
You cannot update with sqlldr.
Insert
Append
If data already exists in the table, SQL*Loader appends the new rows to it. If data does not already exist, the new rows are simply loaded. You must have SELECT privilege to use the APPEND option.
Replace
When REPLACE is specified, the entire table is replaced, not just individual rows. After the rows are successfully deleted, a COMMIT statement is issued.
With REPLACE, all rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table.
The row deletes cause any delete triggers defined on the table to fire. If DELETE CASCADE has been specified for the table, then the cascaded deletes are carried out.
The REPLACE method is a table replacement, not a replacement of individual rows.
SQL*Loader does not update existing records, even if they have null columns. To update existing rows, use the following procedure:
- Load your data into a work table.
- Use the SQL language UPDATE statement with correlated subqueries.
- Drop the work table.
Truncate
When TRUNCATE is specified, the entire table is replaced, not just individual rows. After the rows are successfully deleted, a COMMIT statement is issued.
For the TRUNCATE statement to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, SQL*Loader returns an error.
Once the integrity constraints have been disabled, DELETE CASCADE is no longer defined for the table. The table must be in your schema, or you must have the DROP ANY TABLE privilege.
Field
Filler
A filler field, specified by BOUNDFILLER or FILLER is a data file mapped field that does not correspond to a database column.
field2 FILLER,
field3 FILLER,
field4 ":field2 ||:field3)"
System
- current date
hiredate SYSDATE
- Sequence: Begin 1 increment 1
siteid SEQUENCE (1,1)
Separator/Position
- Position
deptno POSITION(1:2) CHAR
- Position *
siteid POSITION (*) SMALLINT
siteloc POSITION (*) INTEGER
If these were the first two column specifications, then siteid would begin in column 1, and siteloc would begin in the column immediately following.
Transformation
- Sql
column_name EXPRESSION "SQL string"
ename CHAR "UPPER(:ename)",
sal CHAR "TO_NUMBER(:sal,'$99,999.99')",
Delimiters
- TERMINATED BY WHITESPACE
sal POSITION(51) CHAR TERMINATED BY WHITESPACE "TO_NUMBER(:sal,'$99,999.99')",