SQL*Loader - (Parameter|Control) File

Card Puncher Data Processing

About

The control file describes the data file and the data operations that must be performed.

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)
)

See Syntax (SQL*Loader Syntax Diagrams)

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')",

Documentation/Reference





Discover More
Card Puncher Data Processing
Oracle - SQL*Loader

A utility data loader for the Oracle database Reason: NLS_NUMERIC_CHARACTERS is equal to ',.' or '.,' based upon the Territory (NLS_LANG) you are in. Example: The NLS_LANG...
Card Puncher Data Processing
SQL Loader - Lob File

How to load a lob file with sql loader where: FILLER is a field that fill a variable named ext_fname. It takes the relative path from the sqlloader running directory to the file to load RESUME...
Card Puncher Data Processing
SQL*Loader - Direct-Path Load

The direct path load in SQLLoader To start SQLLoader in direct path load mode, set the DIRECT parameter to true on the command line or in the parameter file, if used, in the format: Using...



Share this page:
Follow us:
Task Runner