sql loader for the new dba





sql loader is a tool from oracle that you can use to load data from files directly into the database. to use sql loader you need three things

a)the file from which you want to load the data and the table into which you want to load the data

b)The control file where you have to specify the file and table details

c)The sqlloader command that calls the control file and loads the data.

below is the sample commands that i used recently

when you run a sql loader script the outout will be as shown below
edbst01*siqdw-/home/oradb/sqlldr_sri :->./sqlldr_load_data.sh

SQL*Loader: Release 11.2.0.1.0 – Production on Tue Nov 9 10:36:57 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Load completed – logical record count 1722493.

SQL*Loader: Release 11.2.0.1.0 – Production on Tue Nov 9 10:39:40 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Load completed – logical record count 1711624.

SQL*Loader: Release 11.2.0.1.0 – Production on Tue Nov 9 10:42:10 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Load completed – logical record count 22974.

SQL*Loader: Release 11.2.0.1.0 – Production on Tue Nov 9 10:42:19 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Load completed – logical record count 328789.
the sql loader commands are written in a shell script as below

i am using the more command to display the contents of the file sqlldr_load_data.sh . you have to replace the password and the user with the user and password that you want to use to import the data.

edbst01*siqdw-/home/oradb/sqlldr_sri :->more sqlldr_load_data.sh

sqlldr userid=siq/password control=/home/oradb/sqlldr_sri/load_data_i4.ctl log=/dev/null direct=TRUE

the control file format will be as shown below

edbst01*siqdw-/home/oradb/sqlldr_sri :->more load_data_i4.ctl

OPTIONS (ERRORS=1)
Load Data
INFILE      ‘/dir1/dir2/load_file_i4.csv’
TRUNCATE
INTO TABLE siq.sridhar_i4
FIELDS TERMINATED BY ‘,’ optionally enclosed by ‘"’
TRAILING NULLCOLS
                      (

Date_last_modified ,
Extraction_date )

 for more information about sql loader you can read the below links

oracle sql loader

Author: admin