How to load data into table using SQL* Loader?

SQL* Loader is great functionality to load the data from flat file to the table. In this way user can upload huge amount of data into table.
Let see how it is possible.

Step 1:-
Create a table which hold employee details like EMPID (Employee ID), DOJ (Date of joining). The create table syntax is : –

CREATE TABLE TEST_EMP_DET(EMPID NUMBER,DOJ DATE);

Step 2:-
Let insert a huge amount of data into the table. For this case I wrote small PL/SQL block which will insert random amount of data.

Code Snippet:-
CREATE OR REPLACE PROCEDURETEST_INS_EMP_DET AS
l_empid NUMBER;
l_doj   DATE;
BEGIN
/*Set intial value for emp id and joining date*/
l_empid := 314565;
l_doj   := TO_DATE(’04-MAR-1999′,’DD-MON-YYYY’);
FOR i in 1 .. 1000 LOOP
/*Increment the emp id and joining date*/
l_empid := l_empid + 157;
l_doj   := l_doj + 18;
INSERT INTO TEST_EMP_DET (EMPID, DOJ) values (l_empid, l_doj);
END LOOP;
/*Commit the transaction*/
COMMIT;
END TEST_INS_EMP_DET;

Execute the above procedure using
SQL> exec TEST_INS_EMP_DET;
PL/SQLprocedure successfully completed
Executed in0.406 seconds

Check the count
SQL> selectcount(*) from TEST_EMP_DET;
COUNT(*)
———-
1000
Executed in0.579 seconds

Step 3:
So far so good. So data are loaded intotable(1000 record in this case). No w export the data to csv file. For this Iused PL/SQL Developer. You can go for any other tool like Toad, SQL Developeretc.
Once exported the table truncate the tableusing

SQL> TRUNCATE TABLE TEST_EMP_DET;
Table truncated
Executed in 0.375 seconds

Step 4:
Create the control file (.ctl). In this case loader.ctl file is used. The content will be

loader.ctl

load data
infile ‘C:\OracleTest\TEST_EMP_DET_TABLE_RECORDS.csv’ –-Path of the csv file
into tableTEST_EMP_DET
fieldsterminated by “,” optionally enclosed by ‘”‘ –Specify the terminator
(empid, dojDATE “MM/DD/YYYY”) –-As DOJ is date format we need to specify the format here
Now place both csv(TEST_EMP_DET_TABLE_RECORDS.csv)file and control file(loader.ctl) in same path. In this case it’s “C:\OracleTest”.

Step 5:-
Now open the command prompt and go to “C:\Oracle Test”. And run the sqlldr to loadthe data

sqlldr username/password@schema-name control=loader.ctl

SQL* Loader will automatically load thefile to the table and commit.
The output will look like:-

That’s it. Now check the count
SQL> select count(*) fromTEST_EMP_DET;
COUNT(*)
———-
1000
Executed in 0.563 seconds

While executing if it throws any error it will logged into log file. In this case it will be loader.log. And the rejected data will be in the bad file. In this case it willbe TEST_EMP_DET_TABLE_RECORDS.bad.

By default the log and bad file will be created in the same directory in where Control(loader.ctl) files available. Well you can specify the log and bad file.

You can do lot of thing in SQL* Loader. For more information check the following link : –

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_concepts.htm

http://www.orafaq.com/wiki/SQL*Loader_FAQ

Advertisements

Published by

Anirban

I am software engineer by profession. Sometimes i post some topics about technology and some other topics which i find out interesting. My hobbies include watching movies,music, reading books(both technical and story books(fiction only)).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s