SQL Loader in Oracle

SQL Loader is a utility provided by oracle to load your artifacts in to oracle tables.

While working with SQL Loader you might face a lot of issues something like

LRM-00112: multiple values not allowed for parameter 'control'

LRM-00112: multiple values not allowed for parameter 'control'

SQL*Loader-522: lfiopn failed for file

SQL*Loader-128: unable to begin a session

SQL*Loader-100: Syntax error on command-line

All these error comes if you are not running the sql loader in a correct way. In this exercise we will try to do a simple scenario where in we will create a table a control and log file and try to use the sqlldr command to upload the records from txt file to the table in oracle.

We will use scott/welcome1schema for this purpose.



Log in to sql plus using scott/welcome1 and create a table--> This table we will use to update from sqlldr command

create table employee(
EmpId integer,
Empname varchar2(25),
Dept varchar2(15),
Salary integer,
)



Now we have the table created , we will create a text file (a comma separated file) and a control file (for uploading the text in required format to the table) and a log file (holds all the details when we upload the csv file to oracle table).

First we will create a csv file

Create a Employee.txt file and copy the following contents in to the file

100,Arpit,Siebel,50000
200,Chandan,Oracle,5500
300,Gaurav,Andriod,7000
400,Ishupal,Marketing,7500
500,Ajay,Technology,6000

Next is create a log file Employee.log which will hold the logs for the sql upload

Thirdly create a control file which will have the command to load data from this text file to the table in oracle db

Create a file Employee.ctl and copy the following command


load data
INFILE 'C:\Users\XXXX\Desktop\Employee.txt'
into table employee
fields terminated by ","
(EmpId,Empame,Dept,Salary )

Save all the changes


NOw lets run the sqlldr command to load the data in to oracle table.

Go back to oracle home\bin path in your db server

and execute the following command

sqlldr scott/welcome1 control=C:\Users\XXXX\Desktop\Example.ctl log='C:\Users\XXXX\Desktop\Example.log'



Once execute and completed successfully check the table again and you can find the records in the table.



You can also check the log file Employee.log to get the details about this upload


Employee.log


SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 20 12:49:45 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Control File: C:\Users\xxx\Desktop\Example.ctl
Data File: C:\Users\xxx\Desktop\Employee.txt
Bad File: C:\Users\xxx\Desktop\Employee.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPID FIRST * , CHARACTER
EMPNAME NEXT * , CHARACTER
DEPT NEXT * , CHARACTER
SALARY NEXT * , CHARACTER


Table EMPLOYEE:
5 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 66048 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Fri Jun 20 12:49:45 2014
Run ended on Fri Jun 20 12:49:47 2014

Elapsed time was: 00:00:01.61
CPU time was: 00:00:00.03

There is an excellent article on the further use cases of sql loader.

Please refer to the following link to get more information on the sql loader capability