Oracle SQLLDR

SQLLDR is a basic ETL tool to load data from a text file into an Oracle database table. The control file (filename.ctl) maps the file data to the Oracle table structure and some basic transformations (Oracle functions or custom functions) can be called during load, which can have some impact on the load performance (TO_DATE(:last_sold_date, 'YYYYMMDD') in example below). For direct loads, there cannot be any transformations. The example shown below is to execute SQLLDR in Microsoft Windows environment. The same can be setup as a UNIX/Linux shell script with necessary OS settings (ORACLE_HOME, bin directory etc.). The data file in this example is in csv format.

SQLLDR Control File (loadinv.ctl)

-- loadinv.ctl (replace APPEND with TRUNCATE to delete and load)
LOAD DATA APPEND
--CHARACTERSET WE8EBCDIC500       --If data is EBCDIC characterset
INTO TABLE inventory_part_lookup
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(part_number,
 part_description,
 uom_code,
 qty_on_hand,
 unit_cost,
 stocking_cost,
 last_sold_date  "TO_DATE(:last_sold_date, 'YYYYMMDD')"
)

On execution of the batch file, the user will be prompted for password. When executed in an automated load setup, the password will be passed by a parameter file. The files will be uploaded to designated directories, setup as database directories

Batch File To Execute SQLLDR for Windows (loadinv.bat)

c:\oracle11\bin\sqlldr user_name@instance CONTROL=loadinv.ctl DATA=invdata.csv LOG=anyLog.log BAD=anyBad.bad

Typical File (invdata.csv)

10001A,"Angle Bracket",EA,65,15.45,0.15,20050815
10002A,"Angle Channel",EA,95,11.75,0.12,20091215
......
......

Oracle registered trademark of Oracle Corporation.

Last Revised On: February 14^th, 2021

23859