Using of SQL Loader | Example of SQL Loader

|SQL*LOADER|

SQL Loader is a powerful tool to upload bulk external data to oracle database. It is oracle provided tool. Its performance is higher than the traditional upload data using Toad or any other tools. The SQL loader can be used to read data from external data. We can load data from multiple data files during the same load session using SQL Loader. There is an example that how you can upload data using SQL Loader.

Step-01:

 

Assume that HR default schema is available. Sample SQL to generate a CSV(Comma Separated Value) file:

select employee_id,first_name,last_name, hire_date,Salary

from employees

order by 1;

 

Save SQL output as a CSV file

Export the data as CSV and save the file as EMP_DATA.csv

Create a test table as EMP_TEST_DATA:

create table emp_test_data

as

select employee_id,first_name,last_name, to_char(hire_date,'DD-Mon-YYYY') hire_date,Salary

from employees

where 1=2;

Step-02:

Write a control file as below and save the file with EMP_DATA.ctl

LOAD DATA

INFILE 'EMP_DATA.csv'

BADFILE 'EMP_DATA'

DISCARDFILE 'EMP_DATA'

INSERT INTO TABLE emp_test_data

APPEND FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

(employee_id,first_name,last_name, hire_date,Salary)

 

Step-03:

Create a batch file using below code and save the code as Load_Data.bat

@echo off 

sqlldr userid = hr/hr@orcl control = emp_data.ctl log = emp_data.log 

pause

(Note: change the UserID or Connection string as you have available)

Now create a folder and put the above three files (EMP_DATA.csv, EMP_DATA.ctl & Load_Data.bat) in that folder. Now double click on Load_data.bat file. It will start to insert data into  EMP_TEST_DATA table. See the below picture:

 

 
SQL Loader CMD Console

A log file has created after completing the process. You can see the log file to ensure all the records are uploaded successfully or not. You will see 1 record is rejected which is the header of the CSV data.

Log file of SQL*Loader

Now see the data from EMP_TEST_DATA table. 

Final Output