The Great SQL LOADER UTILITY IN ORACLE

Abstract: This article focus on inserting millions of records in a single go in a single or multiple table.

Introduction

Relational DBMS provide us with many features which we generally are not aware of in real life, if you are lucky you will be informed by colleagues, learnt threw watching tutorial, books or else when you are stuck in day to day escalation from support team that the utility you gave for inserting is running slow.

Agenda:
Inserting a bulk upload from CSV file to the database using SQL Loader utility.

Here in the article I will inserting 2 lakh records in the table, which I was finding an overhead in my day to day development life.

Let’s get started, I have a csv files with 200999 records. In Order to insert the same.

I will follow below steps:

  1. Go to database and search for table( ex TblEmployee)
  2. Right click on table and select import data.


Figure1.0 Demonstrating Import data feature in sql developer.

  1. Search for the file you want to insert and click on ok.


Figure2.0 selecting your csv file.

  1. Following below window will open, you can define the delimiter as per your need, csv file has by default ‘,’ as a delimiter


Figure3.0 Checking the records from csv file.

Cross verify your records, if you find any redundant data that you don’t want to insert, than please correct the same in the file.

Once checked than click on Next button


Figure4.0 Select the import method.

  1. Select Import method as SQL loader utility as shown below:


Figure5.0 SQL Loader Utility

To remove the row limit clear the 100 value from the textbox.

Click on next:

Now in Column definition you need to map your csv headers with table column


Figure6.0 mapping csv headers with table column.

Click Next


Figure7.0 setting up configuration path for the log file and ctl file.

Now set the path for the log file, bad file and batch file, as shown below:


Figure8.0 setting up configuration path for the log file and ctl file.

Now once you are done with path configuration click on next.


Figure9.0 verifying the configuration done in the SQL loader.

Click on verify to verify the configuration, if you have mapped a two csv headers with same column, this window will give you an error.


Figure10.0 verified successfully.

Once everything is verified. Click on Finish

We can see the files are created to our configured folder:


Figure11.0 Batch File generated.

Now before executing I want to you to note down one more requirement. What if one of my column is using oracle sequences for the column value, than how should we proceed to do the same.

In ctl file generated we need to add a simple one line to achieve the same, as shown below:

load data

infile ‘C:\Users\Saillesh.pawar\Desktop\RRP_B_E_01082016new.csv’ “str ‘\r\n'”

append

into table tableName

fields terminated by ‘,’

OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘

trailing nullcols

( EMPLOYEE_ID CHAR(4000),

FIRST_NAME CHAR(4000),

EMAIL_ID CHAR(4000),

MOBILE_NO CHAR(4000),

CITY CHAR(4000),

STATE CHAR(4000),

DEVICE_TYPE CHAR(4000),

FLAG CHAR(4000),

UD CHAR(4000),

COUPON_CODE CHAR(4000),

ADDED_DATE DATE “dd-mm-yy”

)

After adding Sequence command:

load data

infile ‘C:\Users\Saillesh.pawar\Desktop\RRP_B_E_01082016new.csv’ “str ‘\r\n'”

append

into table REFERRAL_TRANS_DATA

fields terminated by ‘,’

OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘

trailing nullcols

( EMPLOYEE_ID CHAR(4000),

FIRST_NAME CHAR(4000),

EMAIL_ID CHAR(4000),

MOBILE_NO CHAR(4000),

CITY CHAR(4000),

STATE CHAR(4000),

DEVICE_TYPE CHAR(4000),

FLAG CHAR(4000),

UD CHAR(4000),

COUPON_CODE CHAR(4000),

ADDED_DATE DATE “dd-mm-yy”,

     SR_NO “REFERRAL_TV_SR_NO.nextval”

)

Once we have resolved this problem

Let look into our one more problem what if I don’t have access to the server where db is installed and I don’t have tnsnames.ora files for the tns of database

For the same I will just pass the full tns to the batch file, I will edit the batch file and insert following commands after sqldr.

userid=’RPOS_PRODUCTION/rpos@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.01)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=tns)))’

And after all the command write pause for pausing cmd.

sqlldr userid=’RPOS_PRODUCTION/rpos@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.01)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=tns)))’

CONTROL=RRP_B_E_01082016new.ctl LOG=D:\bulkUploadSqlDeveloper\RRP_B_E_01082016new.log BAD=D:\bulkUploadSqlDeveloper\RRP_B_E_01082016new.bad skip=1

Pause

Once we are done with this we are ready to execute our batch file.


Figure12.0 Running Batch File.

After running the batch file, you will get the following output:


Figure13.0 Records are getting inserted

You can see your table records for the status of job. The SQL loader Utility helps you upload bulk data from the csv or other types of file in an easy and efficient manner.


Figure14.0 SQL loader gets completed.

Once the all the records are inserted we can check the same in our db.


Figure15.0 count reconciled with database.

Now what if we want to insert the same values into another table, then we need to add some more configuration in our cdl file below the first table as shown below:

into table tableName

fields terminated by ‘,’

OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘

trailing nullcols

(

“Empl ID” FILLER POSITION(1) CHAR, –start from the first location of csv header

” Name” FILLER, –by pass this header

“Email ID” filler, –by pass

mobileno “Substr(:mobileno,1,10)”, first header

STATUS CONSTANT ‘Email invitation to be sent’, –inserting hardcoded values to the column

City filler, –by pass

State filler,–by pass

Type filler,–by pass

flag filler,–by pass

ud filler,–by pass

pmsg_Val ,

email_id “:pmsg_Val”, –using above value for the other columns.

employee_id “:pmsg_Val”,

pmsg_status “1”,

item_count “1”,

pmsg_id “41”,

create_dt “SYSDATE+1”,

product_id “1000461”,

expiry_date “SYSDATE+14”

)

I hope this article was useful for uploading bulk upload from csv file using SQL loader.

References

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.