SQL Loader Utility In Oracle

Abstract: This article is about uploading the bulk of millions of pieces of data in a single go, in a single table.

Introduction

Relational DBMS gives us many features which we generally are not aware of in real life. If you are lucky, you will get to know some features through colleagues, or by watching tutorials; or when you are stuck in day to day escalation from the support team, when the utility, they have given for inserting, is running slowly.

Agenda: Uploading bulk data from CSV file to the database, using SQL Loader utility.

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

Let’s get started. I have a CSV file with 2,00,999 records. In order to insert the same, follow the below steps:

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

Figure 1.0: Demonstrating Import data feature in SQL developer.
Search for the file that you want to insert. Click OK.

Figure 2.0: Selecting your CSV file.
Now, the following window will open. You can define the delimiter as per your need. A CSV file has ‘,’ as a delimiter, by default.

Figure 3.0: Checking the records from CSV file.
Cross verify your records. If you find any redundant data that you don’t want to insert, then please correct the same in the file.

Once checked, click on Next.

Figure 4.0: Select the import method.
Select import method as SQL Loader Utility, as shown below:

Figure 5.0: SQL Loader Utility

To remove the row limit, clear the 100 value from the textbox and click Next.

Now, in Column definition, you need to map your CSV headers with the table column.

Figure 6.0: Mapping CSV headers with table column.

Click Next.

Figure 7.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:

Figure 8.0: Setting up configuration path for the log file and ctl file.

Once you are done with the path configuration, click on Next.

Figure 9.0: Verifying the configuration done in the SQL Loader.

Click on "Verify" to verify the configuration. If you have mapped two CSV headers with the same column, this window will throw an error.

Figure 10.0: Verified successfully.

Once everything is verified, click on Finish. We can see that the files are created to our configured folder.

Figure 11.0: Batch file is generated.

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

In ctl file generated, we need to add a simple 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\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's look into another 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?

In that case, I will just pass the full tns to the batch file. I will edit the batch file and insert the following commands after sqldr.

userid='user/user123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.01)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=tns)))'

After all the commands, write pause for pausing the cmd.

sqlldr userid='user/user123@(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:

Figure 13.0: Records are getting inserted

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

Figure 14.0: SQL Loader gets completed.

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

Figure 15.0: Count reconciled with database.

Now, 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
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 data from CSV file, using SQL Loader.

References

https://community.oracle.com/thread/699631?start=0&tstart=0
http://www.thatjeffsmith.com/archive/2012/08/using-oracle-sql-developer-to-setup-sqlloader-runs/