How to use the SQL*Loader utility in Oracle ?

One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example:

sqlldr username@server/password control=loader.ctl

sqlldr username/password@server control=loader.ctl

This sample control file (loader.ctl) will load an external data file containing delimited data:

load data

 infile 'c:\data\mydata.csv'

 into table emp

 fields terminated by "," optionally enclosed by '"'          

 ( empno, empname, sal, deptno )

The mydata.csv file may look like this:

10001,"Scott Tiger", 1000, 40

10002,"Frank Naude", 500, 20

Optionally, you can work with tabulation delimited files by using one of the following syntaxes:

fields terminated by "\t"

fields terminated by X'09'

Additionally, if your file was in Unicode, you could make the following addition.

load data

 CHARACTERSET UTF16

 infile 'c:\data\mydata.csv'

 into table emp

 fields terminated by "," optionally enclosed by '"'          

 ( empno, empname, sal, deptno )

Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file:

load data

 infile *

 replace

 into table departments

 (  dept     position (02:05) char(4),

    deptname position (08:27) char(20)

 )

begindata

COSC  COMPUTER SCIENCE

ENGL  ENGLISH LITERATURE

MATH  MATHEMATICS

POLY  POLITICAL SCIENCE