PL/SQL SQL*Loader

 PL/SQL SQL*Loader 

    SQL*Loader is an Oracle-supplied utility that allows we to load data from one or more file into one or more database tables.

 Below files are used in SQL*Loader :-  

Sr.No	File Name	Description
1)	DATA FILE	This is the input text file that contains the data which needs to be loaded into an oracle table. Note:-Data file is optional as we can put data in control file at last in begin data section.
2)	CONTROL FILE	This file written by the programmer or developer to load the data from data file to database table. This file contains the instructions to the sql loader utility. This tells the location of the input file, the format of the input file, and other optional meta data information required by the sql loader. Note:-We can also define (name and path) of discard file and bad file here.
3)	BAD FILE	This file is created by SQL*Loader utility and this file has the all rejected records. Note:-char/varchar data will not be loaded into NUMBER fields, and numbers will not be loaded into DATE fields. Records that doesn't convert to the destination datatype are rejected and written to the bad file. It is created automatically having extension .bad
4)	DISCARD FILE	SQL*Loader allows us to load selective data by WHEN clause. we can specify conditions under which a record will be accepted. Records not meeting those conditions are not loaded in database these records written into the discard file. It is created automatically having extension .dsc
5)	LOG FILE	It contains status of load process such as number of rows processed and number of rows loaded, number of rows discarded.

 Data Types for SQL Loader :-  

  char, decimal external, integer external.

 Loading Mode :-  

Sr.No	Mode	Description
1)	Insert	It works only when target table is empty.
2)	Append	It allow to insert data into non-empty table.
3)	Replace	It delete all records first then load the data. The user must have DELETE privilege.
4)	Truncate	It truncate table first then load the data and can not be rolled back.

 Option Clause:  

bindsize = n
direct = {TRUE | FALSE}
errors = n
load   = n
multithreading = {TRUE | FALSE}
parallel = {TRUE |FALSE}
readsize = n
resumable = {TRUE | FALSE}
resumable_name = 'ant_string'
resumable_timeout = n
rows = n
silent = {HEADER | FEEDBACK | ERRORS | DISCARDS | PARTITIONS | ALL}
skip = n
skip_index_maintenance = { TRUE | FALSE}
skip_unusable_indexes = { TRUE | FALSE}
streamsize = n

Note:- We can get all options from "help" command on command prompt.

 Syntax of Control File:  

OPTIONS ( ERRORS = n, SILENT = ( FEEDBACK))
LOAD DATA
INFILE ''
{INSERT | APPEND | TRUNCATE | REPLACE } into table 
field terminated by "," optionally enclosed by " "
WHEN
(  position(:) , position(:) ,...) 

Note:

If data file and control file are in same path then no need to mention the path of data file in control file. $sqlldr -help. Before going to use SQL*Loader utility , we have ensure that target table is already created in database.

 Example1 :- data file for loading in database :-  

 employee.txt (Data File)  

10,Akash,HomeLoanSales,15000
20,Kufu,Life Insurance,11000
30,Krishna,Sales,17000
40,Amol,Marketing,19500
50,Satya,Antivirus,16000

 example1.ctl (Control File)  

 LOAD DATA
 INFILE 'd:\dataupload\employee.txt'
 INSERT into table employee
 field terminated by "," optionally enclosed by " "
 ( id, name, dept, salary) 

Note:

Always put data and control file in same path so that no need to mention data file path in control file. if both files are in same path then control file will be as below.

 example1.ctl (Control File)  

 LOAD DATA
 INFILE 'employee.txt'
 INSERT into table employee
 field terminated by "," optionally enclosed by " "
 ( id, name, dept, salary) 

 The above control file indicates the following:  

Sr.No	Contains	Description
1)	infile	Indicates the location of the input data file.
2)	into table	Indicates the table name where this data should be inserted
3)	fields terminated by	Indicates the delimiter that is used in the input file to separate the fields.
4)	optionally enclosed by	Indicate field data enclosed by that character.
5)	( id, name, dept, salary )	Lists the name of the column names in the table into which the data should be uploaded Firstly need to check that table is available in database or not.

 If table does not exists then create first.

 Table Script :-  

 create table employee ( 
    id integer,
    name varchar2(50),
    dept varchar2(50),
    salary integer,
    hiredon date
);

Note:

If we don’t have the table created, we’ll get the following error message: SQL*Loader-941: Error during describe of table EMPLOYEE ORA-04043: object EMPLOYEE does not exist.

 Now start the Command promt and Move to directory where data file and control file are placed using 'cd' command.
Run the following command to load the data.

 sqlldr scott/tiger@empdb control=example1.ctl 

 Select Query Script :-  

 select * from employee; 

ID	NAME	DEPT	SALARY	HIREDON
10	Akash	HomeLoanSales	15000
20	Kufu	Life Insurance	11000
30	Krishna	Sales	17000
40	Amol	Marketing	19500
50	Satya	Antivirus	16000

 This will create the output log file in the same name as the data file, but with the .log extension (instead of .ctl). Partial output shown below.

 example1.log (Log File)  

 Control File:   D:\sqlLoader\example1.ctl
 Data File:     D:\sqlLoader\employee.txt
 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.
 Elapsed time was:     00:00:00.04
 CPU time was:         00:00:00.00

 Example2 : Inserting Additional Records :-  

  Let us say we want to add two new employees to the employee table from the following employee-new.txt file.

 employee-new.txt (Data File)  

 60,Santhosh,Personal Loan,11500	
 70,Mahesh,Car Loan,13000

  If we create a similar control file like the previous example, we might get the following error message.

 example2-add-more.ctl (Control File)  

 LOAD DATA
 INFILE 'employee-New.txt'
 append into table employee
 field terminated by ','
 ( id, name, dept, salary) 

Now, we do sqlldr this will append the data.

 sqlldr scott/tiger@empdb control=example2-add-more.ctl 

 Select Query Script :-  

 select * from employee; 

ID	NAME	DEPT	SALARY	HIREDON
10	Akash	HomeLoanSales	15000
20	Kufu	Life Insurance	11000
30	Krishna	Sales	17000
40	Amol	Marketing	19500
50	Satya	Antivirus	16000
60	Santhosh	Personal Loan	11500
70	Mahesh	Car Loan	13000

 Example 3 : Data inside the Control File using BEGINDATA  

  We can also specify the data directly inside the control file itself using BEGINDATA keyword. i.e Anything that comes after BEGINDATA will be treated as data to be uploaded to the table as shown below.

 example3-add-with-BEGINDATA.ctl (Control File)  

LOAD DATA
INFILE *
append into table employee
field terminated by ','
( id, name, dept, salary)
begindata
10,Akash,HomeLoanSales,15000
20,Kufu,Life Insurance,11000
30,Krishna,Sales,17000
40,Amol,Marketing,19500
50,Satya,Antivirus,16000 

Note:

The infile will say '*' in this case, as there is no input data file name for this example. Execute sqlldr to upload the data from the control file.

Now, we do sqlldr this will append the data.

 sqlldr scott/tiger@empdb control=example3-add-with-BEGINDATA.ctl 

 Example 4 :Date format and Different Delimiter  

  In this example shows how to specify a date format in the control file and how to handle different delimiters in a data file.
The following example has different delimiters ($ after name, ^ after department).

 employee-date.txt (Data File)  

10,Akash$HomeLoanSales^15000,31-JAN-2018
20,Kufu$Life Insurance^11000,01-Feb-2019
30,Krishna$Sales^17000,10-Aug-2019
40,Amol$Marketing^19500,12-Dec-2018
50,Satya$Antivirus^16000,01-JAN-2019

Create the following control file and indicate the field delimiters for each and every field using "terminated by" as shown below.

 example4-date.ctl (Control File)  

LOAD DATA
INFILE 'employee-date.txt'
truncate into table employee
field terminated by ','
( id, name terminated by '$' , dept terminated by '^'  , salary DATE "dd-mon-yyyy" ) 

Load the data using sqlldr as shown below.

 sqlldr scott/tiger@empdb control=example4-date.ctl 

Verify that the data got loaded properly as shown below.

 Select Query Script :-  

 select * from employee; 

ID	NAME	DEPT	SALARY	HIREDON
10	Akash	HomeLoanSales	15000	31-JAN-2018
20	Kufu	Life Insurance	11000	01-Feb-2019
30	Krishna	Sales	17000	10-Aug-2019
40	Amol	Marketing	19500	12-Dec-2018
50	Satya	Antivirus	16000	01-JAN-2019

 Example 5 :Date format and Different Delimiter  

  If we have a data file without data that are fixed length (i.e without any delimiter), we can use this example to upload this data. For this example, let us use the following file which has data that are of fixed length. For example, 1st three characters are always employee number, Next 5 characters are always employee name, etc.

 employee-fixed.txt (Data File)  

10Akash  HomeLoanSales 15000
20Kufu   Life Insurance11000
30KrishnaSales		   17000
40Amol   Marketing     19500
50Satya  Antivirus     16000

Create the following control file, where we specific the position of each and every field as shown below using the "Position(start:end)" syntax.

 example5-fixed.ctl (Control File)  

LOAD DATA
INFILE 'employee-fixed.txt'
truncate into table employee
field terminated by ','
( id position(1:3), name position(4:8), dept position(9:18), salary position(19:22) ) 

Load this fixed length data using the sqlldr as shown below.

 sqlldr scott/tiger@empdb control=example5-fixed.ctl 

Verify that the data got loaded properly as shown below.

 Select Query Script :-  

 select * from employee; 

ID	NAME	DEPT	SALARY	HIREDON
10	Akash	HomeLoanSales	15000
20	Kufu	Life Insurance	11000
30	Krishna	Sales	17000
40	Amol	Marketing	19500
50	Satya	Antivirus	16000

 Example 6 :Change the data during upload  

  We can also massage the data and change it during upload based on certain rules. In the following control file: id is incremented by 99 before uploading. i.e if the emp id is 10 in the data file, it will be loaded as 109 Convert the name to upper case and load it. This uses the upper function. If the department contains the value Technology" change it to "Techies". This uses decode function.

 example6-change-data.ctl (Control File)  

LOAD DATA
INFILE 'employee.txt'
truncate into table employee
field terminated by ','
( id ":id+99",name "upper(:name)",dept  "decode(:dept,'Technology','Techies', :dept)", salary ) 

Load the data using this control file which will massage the data before uploading it.

 sqlldr scott/tiger@empdb control=example6-change-data.ctl 

Verify that the data got changed while loading as per our rules.

 Select Query Script :-  

 select * from employee; 

ID	NAME	DEPT	SALARY	HIREDON
109	Akash	HomeLoanSales	15000
119	Kufu	Life Insurance	11000
129	Krishna	Sales	17000
139	Amol	Marketing	19500
149	Satya	Antivirus	16000

 Example 7 : Load data from multiple data files  

 To load data from multiple files, we just have to specify multiple infile in the control file. The following control file loads data from two different data files (employee.txt and employee-new.txt) to the employee table.

 example7-add-multiple.ctl (Control File)  

LOAD DATA
INFILE 'employee.txt'
INFILE 'employee-new.txt'
append into table employee
field terminated by ','
( id,name,dept, salary ) 

Load the data using this control file which will upload data from multiple data files as shown below.

 sqlldr scott/tiger@empdb control=example7-add-multiple.ctl 

 Example 8 : Load data to Multiple Tables  

 Create another table called bonus which will have employee id and bonus columns.

 If table does not exists then create table.

 Table Script :-  

 create table bonus ( 
    id integer,
    bonus integer,
);

Create the employee-bonus.txt data file that contains the fields: id, name, department, salary, bonus.

 employee-bonus.txt (Data File)  

10Akash  HomeLoanSales 15000 1000
20Kufu   Life Insurance11000 2000
30KrishnaSales		   17000 2000
40Amol   Marketing     19500 1000
50Satya  Antivirus     16000 3000

Create the control file as shown below, which will upload the data from the above file to two different tables. As shown below, you should have two "into table" commands, and specify the position of the data which needs to be used to upload the data to that column.

 example8-multiple-tables.ctl (Control File)  

LOAD DATA
INFILE 'employee.txt'
INFILE 'employee-new.txt'
append into table employee
field terminated by ','
( id position(1:3),name position(5:10),dept position(12:21), salary position(23:26) ) 
truncate into table bonus
( id position(1:3),bonus position(28:31))
    

Load the data to multiple tables using this control file as shown below.

 sqlldr scott/tiger@empdb control=example8-multiple-tables.ctl 

Verify that the data got changed while loading as per our rules.

 Select Query Script :-  

 select * from employee; 

ID	NAME	DEPT	SALARY	HIREDON
109	Akash	HomeLoanSales	15000
119	Kufu	Life Insurance	11000
129	Krishna	Sales	17000
139	Amol	Marketing	19500
149	Satya	Antivirus	16000

 select * from bonus; 

ID	BONUS
109	1000
119	2000
129	2000
139	1000
149	3000