SQL Loader Overview

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. It supports various load formats, selective loading, and multi-table loads.

Below are the examples of loading data into Tables using different methods

Load Fixed length Data Records

LOAD DATA

INFILE *

INTO TABLE load_delimited_data

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

( data1,

data2

)

BEGINDATA

11111,AAAAAAAAAA

22222,"A,B,C,D,"

Load Positional Data Records

LOAD DATA

INFILE *

INTO TABLE load_positional_data

( data1 POSITION(1:5),

data2 POSITION(6:15)

)

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB

Skip First 2 Header Records while Loading

LOAD DATA

INFILE *

INTO TABLE load_positional_data

SKIP 2

( data1 POSITION(1:5),

data2 POSITION(6:15)

)

BEGINDATA

NNNNNNNNNNNNNNN

UUUUUUUUUUUUUUU

11111AAAAAAAAAA

22222BBBBBBBBBB

Loading Data into Multiple Tables

LOAD DATA

INFILE *

REPLACE

INTO TABLE emp

WHEN empno != ' '

( empno POSITION(1:4) INTEGER EXTERNAL,

ename POSITION(6:15) CHAR,

deptno POSITION(17:18) CHAR,

mgr POSITION(20:23) INTEGER EXTERNAL

)

INTO TABLE proj

WHEN projno != ' '

( projno POSITION(25:27) INTEGER EXTERNAL,

empno POSITION(1:4) INTEGER EXTERNAL

)

Skip Certain Columns while Loading Data

LOAD DATA

TRUNCATE INTO TABLE T1

FIELDS TERMINATED BY ','

( field1,

field2 FILLER,

field3

)

Erroneous data after loading with SQL Loader

Our Database is created with below settings

CHARACTER SET "UTF8"

NATIONAL CHARACTER SET "WE8ISO8859P1"

NLS_LANGUAGE = AMERICAN

NLS_TERRITORY = AMERICA

Include below code in control file to avoid junk data load using SQL loader

LOAD DATA

CHARACTERSET WE8ISO8859P1

APPEND INTO TABLE XXAP_GENERIC_INVOICES

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(operating_unit

,vendor_number

)

Using SQL Functions for Loader Columns

LOAD DATA

APPEND INTO TABLE

XXAR_INV_AT

FIELDS TERMINATED BY ""

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

DESCRIPTION "NVL(:DESCRIPTION,'Migrated from Legacy')",

TRX_DATE "TO_CHAR(TO_DATE(:TRX_DATE,'YYYYMMDD'),'DD-MON-YYYY')" ,

GL_DATE "TO_CHAR(TO_DATE(:GL_DATE,'YYYYMMDD'),'DD-MON-YYYY')" ,

TAX_RATE "REPLACE(:TAX_RATE,'%','')",

STAGING_TABLE_ID "XXAR_INV_AT_S1.NEXTVAL",

CREATION_DATE SYSDATE,

LAST_UPDATE_DATE SYSDATE

SOURCE "TRIM(REPLACE(:SOURCE,(SUBSTR(:SOURCE,LENGTH(:SOURCE),1)),''))"

)

/