oracle sqlldr - ghdrako/doc_snipets GitHub Wiki

load data
infile '<file_path>' optionall enclosed by '"'
truncate into table <tab>
fields terminated by ";"trailing nullcols
(field1,field2,...,field<n>)

Trimming Whitespace

when you load fixed-width,columnar data is to trim trailing whitespace from each field in the input record. Trimming whitespace is one of the first thing SQL Loader does after reading each record of data.

PRESERVE BLANKS - prevent trimming trailing whitespace. When using PRESERVE BLANKS you'll need to use DEFAULTIF or NULLIF with any INTEGER_EXTERNAL,FLOAT_EXTERNAL,DECIMAL_EXTERNAL

Selective trimming of whitespace

load data
infile '...'
REPLACE PRESERVE BLANKS INTO TABLE X
(
  a POSITION(4) CHAR(44) "RTRIM(:a)"

Dealing with Nulls

NULLIF clause - sepcify one or more condition under with a field should be interpreted as a NULL
DEFAULTIF clause - sepcify one or more condition under with a field should take on either a null or a zero value depending on whether the fields a character field or a numeric field

$ sqlldr scott/tiger control=.ctl

NAZWA CHAR(45) "convert(:NAZWA,'WE8ISO8859P1')",

replace NULL value

home_points "NVL(:home_points,0)"

no terminator found after TERMINATED and ENCLOSED field

Load Data
APPEND
INTO TABLE MyDataTable
fields terminated by ","     ---- Noticed i omitted the "enclosed by"
TRAILING NULLCOLS
(
  column1 enclosed by '"',   --- Specified "enclosed by" here for all cols
  column2 enclosed by '"',
  HOME_ADD  "replace(:HOME_ADD, chr(34))", -- Omitted "enclosed by".  replace removes all doublequotes in string value.  chr(34) is charcode for doublequote
  column3 optionally enclosed by '"'
  FIRST_NAME "replace(substr(:FIRST_NAME,2, length(:FIRST_NAME)-2), chr(34) || chr(34), chr(34))", -- Omitted "enclosed by".  substr removes doublequotes, replace fixes double quotes showing up twice.  chr(34) is charcode for doublequote
DEPTNO CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  column4 enclosed by '"',
  column5 enclosed by '"'
)

SQLLDR Error : Field in data file exceeds maximum length

You have to specify the length in your control file if it exceeds 255 bytes. Specify CHAR(4000) in your field description in control

The default length for a CHAR field is 255. Your control file should look something like:

LOAD DATA
INFILE "test.txt"
INTO TABLE TEST replace
fields terminated "|" optionally enclosed by '"' TRAILING NULLCOLS
(
  DOCUMENTID,
  CUSTID,
  USERID ,
  FILENAME,
  LABEL,
  DESCRIPTION CHAR(2000),
  POSTDATE DATE "YYYY-MM-DD HH24:MI:SS" NULLIF POSTDATE=BLANKS,
  USERFILENAME,
  STORAGEPATH
)

Separator

fields terminated by X'9' optionally enclosed by X'1F'
TRAILING NULLCOLS {…}

RECORDS DELIMITED BY '\r\n'

Stream records

load data
infile "test3.dat" "str '**DLM**'"
into table T
TRUNCATE
fields terminated by 'XXXXX' optionally enclosed by '"'
(
TEXT
)

LOAD DATA
3    INFILE 'sample.dat'
4    BADFILE 'sample.bad'
5    DISCARDFILE 'sample.dsc'
6    APPEND
7    INTO TABLE emp
8    WHEN (57) = '.'
9    TRAILING NULLCOLS
10  (hiredate SYSDATE,
      deptno POSITION(1:2)  INTEGER EXTERNAL(2)
              NULLIF deptno=BLANKS,
       job    POSITION(7:14)  CHAR  TERMINATED BY WHITESPACE
              NULLIF job=BLANKS  "UPPER(:job)",
       mgr    POSITION(28:31) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
       ename  POSITION(34:41) CHAR 
              TERMINATED BY WHITESPACE  "UPPER(:ename)",
       empno  POSITION(45) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE,
       sal    POSITION(51) CHAR  TERMINATED BY WHITESPACE
              "TO_NUMBER(:sal,'$99,999.99')",
       comm   INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
              ":comm * 100"
    )

Loading a CONSTANT, RECNUM, and SYSDATE

OPTIONS (ERRORS=100, SILENT=(FEEDBACK))
 LOAD DATA
 INFILE *
REPLACE
INTO TABLE dept
 FIELDS TERMINATED BY ','
 OPTIONALLY ENCLOSED BY '"'
 (recno RECNUM, deptno  CONSTANT "XX", dname, loc, tdate SYSDATE)

Numbers with trailing + and - signs

LOAD DATA
 INFILE *
TRUNCATE
INTO TABLE loadnums (
col1 position(1:5),
 col2 position(7:16) "TO_NUMBER(:col2,'99,999.99MI')")
 BEGINDATA
 abcde 1,234.99-
 abcde 11,234.34+
 abcde 45.23-
 abcde 99,234.38-
 abcde 23,234.23+
 abcde 98,234.23+

Loading negative numeric values

LOAD DATA
 INFILE *
INTO TABLE emp
 REJECT ROWS WITH ALL NULL FIELDS
 (
 empno  POSITION(01:04) INTEGER EXTERNAL,
 ename  POSITION(06:15) CHAR,
 job    POSITION(17:25) CHAR,
 mgr    POSITION(27:30) INTEGER EXTERNAL,
 sal    POSITION(32:39) DECIMAL EXTERNAL,
 comm   POSITION(41:48) DECIMAL EXTERNAL,
 deptno POSITION(50:51) INTEGER EXTERNAL)
BEGINDATA
7781 CLARK      MANAGER   7838 -572.50           10
7839XKING       PRESIDENT      5500.00           10
7934 MILLER     CLERK     7782 -920.00           10
7566 JONES      MANAGER   7839 3123.75           20
7499 ALLEN      SALESMAN  7698 1600.00   300.01  30
7654 MARTIN     SALESMAN  7698 1312.50  1400.00  30
7658 CHAN       ANALYST   7566 3450.00           20

Record numbering with a SQLLoader using sequence

LOAD DATA
 INFILE *
APPEND
INTO TABLE emp
 FIELDS TERMINATED BY ","
 OPTIONALLY ENCLOSED BY '"'
 (<column_name>, <column_name> DATE "DD-Month-YYYY",
 <column_name> CHAR TERMINATED BY ':',
 <column_name> SEQUENCE(MAX,1))

Generate externa table definition from ctl file

sqlldr scott/tiger control=c: emp\sqlldr02 log=c: emp\ddl_file.txt external_table=generate_only

Logical record

Assembling Logical Records

CONCATENATE CONCATENATE <number_of_physical_records> 
CONCATENATE 3 
CONTINUEIF CONTINUEIF THIS [PRESERVE] (start_position:end_position) = value 
CONTINUEIF THIS (1:2) = '%%'
CONTINUEIF THIS PRESERVE (1:2) = '%%' 
CONTINUEIF CONTINUEIF NEXT [PRESERVE] (start_position:end_position) = value 
CONTINUEIF NEXT (1:2) = '%%'
CONTINUEIF NEXT PRESERVE (1:2) = '%%' 
CONTINUEIF CONTINUEIF LAST (start_position:end_position) = value 
-- Tests against the last non-blank character.
-- Allows only a single character for the test 

PRESERVE Preserves the CONTINUEIF characters

Decimal separator

"TO_NUMBER(:MyVariable, '9999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')"

set NLS_NUMERIC_CHARACTERS=,.

!!! NOTE nie zadziala bo sqlldr bedzie mial inna sesje ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",."

select * from nls_session_parameters;
select * from nls_database_parameters;
alter session set NLS_NUMERIC_CHARACTERS = ',.';
select to_number('100,12') from dual;
alter session set NLS_NUMERIC_CHARACTERS = '.,';
select to_number('100.12') from dual;

Load zip file

$ mknod named_pipe.dat p 
$gzip -dc myfile.gz > named_pipe.dat & sqlldr control=x.ctl

where x.ctl uses "named_pipe.dat" as the source data

$ mknod npipe p
$ gunzip -c < ascii_file.txt.gz > npipe &
$ sqlldr scott/tiger data=npipe

Error exit codes

https://thinktibits.blogspot.com/2011/03/sql-loader-automation-exit-codes.html

	Unix	Windows
Successful Execution	0	0
An unrecoverable failure has happened	1	3
At least one row got rejected	2	2
Any O/S specific Errors	3	4

To read this exit code and handle it in windows, the following batch script can be used:

---------------begin script ------------------------------
sqlldr userid/pwd CONTROL=load.ctl log=load.log
if errorlevel 0 echo SQL*Loader execution successful
if errorlevel 2 echo SQL*Loader got executed, but atleast some rows got rejected, check the log file.
if errorlevel 3 echo SQL*Loader encountered an unrecoverable failure,check the logfile for more details
if errorlevel 4 echo SQL*Loader execution encountered OS Specific Error
---------------end script ------------------------------

In Oracle 11GR2 the codes are as follows:

EX_SUCC 0
EX_FAIL 1
EX_WARN 2
EX_FTL 4

http://docs.oracle.com/cd/E14072_01/server.112/e10701/ldr_params.htm

Also, errorlevels should be tested in descending order:

http://support.microsoft.com/kb/39585