Let's begin with basics on SQL Loader topic.
SQL Loader: Its a utility which is used for loading data from operating system level to Database. Here data can be stored in any kind of text file.
Using SQL Loader , we can load data into multiple table in same load session and also we can add filters and sql functions according to our requirement.
SQL loader reads and process data based on instructions from control file.
Control File: Its a text file and this will tells SQL loader on how data need to be processed and where data is located and how to parse it and on which table we need to insert and many more.
Bad File: It will contain records which are rejected while loading. here reason could be any validation issue.
If you don't specify bad file and if there are any rejected records exists then automatically SQL Loader creates one bad file.It will have the same name as the data file, with .bad extension.
Log File: It will be created automatically when you run sql loader with same name as which you mentioned for control file with extension .log otherwise you can keep your own log name.
Discard File:
It will have records which are not loaded due when condition we specify in control file.
To know methods in loading data, Click Here
You can see how sql loader processed including any errors while processing loader.
creating table using emp table which we usually have in our schema.
SQL> create table emp_loader as select * from emp where 1=2;
Table created
Remember that we MUST have insert and delete privileges on whichever table you're trying to load.
eg: now here we are going to load on "emp_loader" table so we need privileges which mentioned above.
SQL> grant insert,delete on emp_loader to <Schema_Name>;
Grant succeeded
Assume we have csv file with following data to insert into "emp_loader" table.
101,VENKY,IT,105,05022018,2500,,50
102,VENKATESH,IT,105,05052018,3200,,50
and assume we have named this file "t_emp_file.CSV" and its stored in following path "C:\Users\Venky\Desktop\Loader\t_emp_file.CSV"
Lets start with basic commands .
Initially we need to create control file.
Scenario 1:
lets say we created control file with below content and control file name is "loader.ctl".
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
into table emp_loader
fields terminated by ","
(empno,ename,job,mgr,hiredate date "ddmmyyyy",sal,comm,deptno)
Juz keep a note on hiredate format in control file, this format match with csv file data.
Go to command prompt and type sqlldr to see list of options available for loader.
Syntax for running loader:
>sqlldr username/password@database name control=Control file path
Loading text file data using SQL Loader:
as you can see i'm at "C:\Users\Venky" path so my log file will be generated by default in this path as i didnt mention specific path in control file.
and also you can observe from screenshot ,2 records got read from csv file so verify from log file whether its loaded or not. If its loaded then query to verify.
so we had successfully loaded data from csv file to database.
Scenario 2:
How to append data ? or How to load data into existed table which has data?
Simple solution for this is , use APPEND command in control file as below.
Usually we get "SQL*Loader-601: For INSERT option, table must be empty" if already existed table contains records.
Solution:
New Control file:
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
append
into table emp_loader
fields terminated by ","
(empno,ename,job,mgr,hiredate date "ddmmyyyy",sal,comm,deptno)
just before into clause we are keeping "append" .
now lets run with csv file and its should have 4 records.
Loading text file data using SQL Loader:
Scenario 3:
How to use functions in SQL loader control file.
Eg: now i need to make it lower case while inserting or check if job is "IT" then insert as "Info_Tech" otherwise "In_Bench".
Solution:
new csv file:
103,Teja,IT,101,05MAY2018,2500,,50
104,Nikith,,101,10FEB2018,3200,,50
new control file:
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
append
into table emp_loader
fields terminated by ","
(empno,
ename "lower(:ename)",
job "decode(:job,'IT','Info_Tech','In_Bench')",
mgr,
hiredate date "ddmonyyyy",sal,comm,deptno)
Loading text file data using SQL Loader:
as you can see for new records "job" field having data as per our requirement and for ename column also in lower case so this is how we can use multiple functions as per our requirement in control file.
Scenario 4:
How to load data from multiple files ?.
one csv has following data:
file name: t_emp_file.CSV
105,Chinnu,IT,104,05MAY2018,2500,,50
another csv has following data:
file name: t_emp_file_1.CSV
106,Raju,IT,104,05MAY2016,5500,,50
Now look at Control file :
below is control file content , as you see can see there are 2 file names mentioned to load data.
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file_1.CSV'
append
into table emp_loader
fields terminated by ","
(empno,
ename "lower(:ename)",
job "decode(:job,'IT','Info_Tech','In_Bench')",
mgr,
hiredate date "ddmonyyyy",
sal,comm,deptno)
Loading text file data using SQL Loader:
we can see that data is loaded from 2 csv files in above screenshot.
Scenario 5:
How to load data into multiple tables ?.
lets create emp_personal table as below.
SQL> create table emp_personal(emp_id integer,dob date,native varchar2(100));
Table created
and also we will consider our previous table emp_loader here.
Note: Remember when we want to load into multiple table then we have to use "POSITION" keyword in control file to identify data for respective columns.
lets see control file here.
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
append
into table emp_loader
(empno position(1:3),
ename position(5:11),
job position(12:13),
mgr position(15:17),
hiredate position(19:27),
sal position(29:32),comm position(34),deptno position(35:36))
into table emp_personal
(emp_id position(1:3),
dob position(38:46),
native position(48:56))
csv file content:
105 Chinnu IT 104 05MAY2018 2500 50 05FEB1989 BANGALORE
Loading text file data using SQL Loader:
Data is loaded into 2 tables from single csv file.
Scenario 6:
How to load fixed length data in csv file ?.
In some scenario's clients will provide CSV files without any delimiter but with fixed length for each field in text file.
eg: First 3 characters are empid and next 7 characters are emp name and next 2 characters are JOB etc.
csv file content
105Chinnu IT10405MAY20182500 20 50
106LikithaIT10205APR20183500030050
Control file
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
append
into table emp_loader
(empno position(1:3),
ename position(4:10),
job position(11:12),
mgr position(13:15),
hiredate position(16:24),
sal position(25:29),
comm position(30:32),
deptno position(33:34))
data is loaded as per length we have specified in control file.
Scenario 7:
Can we change data while loading in database ?
for this scenario , lets create new table and one sequence as below.
SQL> create table loader_change_data(Id integer,name varchar2(20),DOB date,Country varchar2(20),Graduation_GPA number(3,2),Time varchar2(6));
Table created
SQL> create sequence seq_id start with 1 maxvalue 10000 nocache;
Sequence created
text file content:
venky 05021989789
Venkatesh 05021991916
and file name is "changing_data.txt"
Control File :
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\changing_data.txt'
APPEND
INTO TABLE loader_change_data
(Id "seq_id.NEXTVAL",
name POSITION (1:12) "UPPER(:name)",
DOB POSITION (13:20) "TO_DATE(:DOB,'DDMMYYYY')",
Country CONSTANT 'INDIA',
Graduation_GPA POSITION (21:23) ":Graduation_GPA/100",
Time "TO_CHAR(SYSDATE,'HH24:MI')"
)
we are loading data into "loader_change_data" and we are using POSITION keyword here as we got fixed length data in CSV file.
you can observer above control file, as we used sequence for Id Column , upper , to_date , to_char , Constant and also to taking decimal values for "Graduation_GPA".
Loading text file data using SQL Loader:
C:\Users\Venky>sqlldr phpmetreg/phpmetreg@jpac12c control=C:\Users\Venky\Desktop\Loader\loader_data_change.ctl log=C:\Users\Venky\Desktop\Loader\changing_data.LOG
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 15 16:04:07 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Scenario 8:
How to add conditions while loading data and how to handle null values in text file ?
We will take above table and with same csv file and scenario is , wherever name is "venky" , that data should not be loaded.
so for this scenario, control file would be as follows with "WHEN " clause.
venky 05021989789
Venkatesh 05021991916
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\changing_data.txt'
APPEND
INTO TABLE loader_change_data
WHEN name<>'venky'
(Id "seq_id.NEXTVAL",
name POSITION (1:12) "UPPER(:name)",
DOB POSITION (13:20) "TO_DATE(:DOB,'DDMMYYYY')",
Country CONSTANT 'INDIA',
Graduation_GPA POSITION (21:23) ":Graduation_GPA/100",
Time "TO_CHAR(SYSDATE,'HH24:MI')"
)
Loading text file data using SQL Loader:
C:\Users\Venky>sqlldr phpmetreg/phpmetreg@jpac12c control=C:\Users\Venky\Desktop\Loader\loader_data_change.ctl log=C:\Users\Venky\Desktop\Loader\changing_data.LOG
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 15 17:28:03 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
Scenario 9:
how to handle null values in text file ?
we can fix this issue by using "TRAILING NULLCOLS" in control file.
CSV file content:
:venky:05021989:789:
:Venkatesh:05021991:916:
We have 6 columns in table but above csv file has only four ":" to differentiate fields instead of Five, reason is we have constant value for one column in control file so we dont need it from csv file.
you may think for id column also we are taking from sequence and dob is from sysdate so we need to reduce 2 more ':' in csv file but here actual logic is its applicable only for CONSTANT.
Control File:
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\changing_data.txt'
APPEND
INTO TABLE loader_change_data
fields terminated by ':'
TRAILING NULLCOLS
(Id "seq_id.NEXTVAL",
name "UPPER(:name)",
DOB "TO_DATE(:DOB,'DDMMYYYY')",
Country CONSTANT 'INDIA',
Graduation_GPA ":Graduation_GPA/100",
Time "TO_CHAR(SYSDATE,'HH24:MI')"
)
Loading text file data using SQL Loader:
C:\Users\Venky>sqlldr phpmetreg/phpmetreg@jpac12c control=C:\Users\Venky\Desktop\Loader\loader_data_change.ctl log=C:\Users\Venky\Desktop\Loader\changing_data.LOG
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 15 17:43:10 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Scenario 10:
Whats parfile and how to use it.
This file is useful to keep all parameter which we pass in sqlldr command.
eg:
we created below "loader.par" parameter file with following content.
userid=scott/tiger@venkydb
control=C:\Users\Venky\Desktop\Loader\loader_data_change.ctl
log=C:\Users\Venky\Desktop\Loader\changing_data.LOG
SQL Loader: Its a utility which is used for loading data from operating system level to Database. Here data can be stored in any kind of text file.
Using SQL Loader , we can load data into multiple table in same load session and also we can add filters and sql functions according to our requirement.
SQL loader reads and process data based on instructions from control file.
 |
| SQL*Loader Overview |
Control File: Its a text file and this will tells SQL loader on how data need to be processed and where data is located and how to parse it and on which table we need to insert and many more.
Bad File: It will contain records which are rejected while loading. here reason could be any validation issue.
If you don't specify bad file and if there are any rejected records exists then automatically SQL Loader creates one bad file.It will have the same name as the data file, with .bad extension.
Log File: It will be created automatically when you run sql loader with same name as which you mentioned for control file with extension .log otherwise you can keep your own log name.
Discard File:
It will have records which are not loaded due when condition we specify in control file.
To know methods in loading data, Click Here
You can see how sql loader processed including any errors while processing loader.
creating table using emp table which we usually have in our schema.
SQL> create table emp_loader as select * from emp where 1=2;
Table created
Remember that we MUST have insert and delete privileges on whichever table you're trying to load.
eg: now here we are going to load on "emp_loader" table so we need privileges which mentioned above.
SQL> grant insert,delete on emp_loader to <Schema_Name>;
Grant succeeded
Assume we have csv file with following data to insert into "emp_loader" table.
101,VENKY,IT,105,05022018,2500,,50
102,VENKATESH,IT,105,05052018,3200,,50
and assume we have named this file "t_emp_file.CSV" and its stored in following path "C:\Users\Venky\Desktop\Loader\t_emp_file.CSV"
Lets start with basic commands .
Initially we need to create control file.
Scenario 1:
lets say we created control file with below content and control file name is "loader.ctl".
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
into table emp_loader
fields terminated by ","
(empno,ename,job,mgr,hiredate date "ddmmyyyy",sal,comm,deptno)
Juz keep a note on hiredate format in control file, this format match with csv file data.
Go to command prompt and type sqlldr to see list of options available for loader.
Syntax for running loader:
>sqlldr username/password@database name control=Control file path
Loading text file data using SQL Loader:
as you can see i'm at "C:\Users\Venky" path so my log file will be generated by default in this path as i didnt mention specific path in control file.
and also you can observe from screenshot ,2 records got read from csv file so verify from log file whether its loaded or not. If its loaded then query to verify.
so we had successfully loaded data from csv file to database.
Scenario 2:
How to append data ? or How to load data into existed table which has data?
Simple solution for this is , use APPEND command in control file as below.
Usually we get "SQL*Loader-601: For INSERT option, table must be empty" if already existed table contains records.
Solution:
New Control file:
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
append
into table emp_loader
fields terminated by ","
(empno,ename,job,mgr,hiredate date "ddmmyyyy",sal,comm,deptno)
just before into clause we are keeping "append" .
now lets run with csv file and its should have 4 records.
Loading text file data using SQL Loader:
Scenario 3:
How to use functions in SQL loader control file.
Eg: now i need to make it lower case while inserting or check if job is "IT" then insert as "Info_Tech" otherwise "In_Bench".
Solution:
new csv file:
103,Teja,IT,101,05MAY2018,2500,,50
104,Nikith,,101,10FEB2018,3200,,50
new control file:
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
append
into table emp_loader
fields terminated by ","
(empno,
ename "lower(:ename)",
job "decode(:job,'IT','Info_Tech','In_Bench')",
mgr,
hiredate date "ddmonyyyy",sal,comm,deptno)
Loading text file data using SQL Loader:
as you can see for new records "job" field having data as per our requirement and for ename column also in lower case so this is how we can use multiple functions as per our requirement in control file.
Scenario 4:
How to load data from multiple files ?.
one csv has following data:
file name: t_emp_file.CSV
105,Chinnu,IT,104,05MAY2018,2500,,50
another csv has following data:
file name: t_emp_file_1.CSV
106,Raju,IT,104,05MAY2016,5500,,50
Now look at Control file :
below is control file content , as you see can see there are 2 file names mentioned to load data.
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file_1.CSV'
append
into table emp_loader
fields terminated by ","
(empno,
ename "lower(:ename)",
job "decode(:job,'IT','Info_Tech','In_Bench')",
mgr,
hiredate date "ddmonyyyy",
sal,comm,deptno)
Loading text file data using SQL Loader:
we can see that data is loaded from 2 csv files in above screenshot.
Scenario 5:
How to load data into multiple tables ?.
lets create emp_personal table as below.
SQL> create table emp_personal(emp_id integer,dob date,native varchar2(100));
Table created
and also we will consider our previous table emp_loader here.
Note: Remember when we want to load into multiple table then we have to use "POSITION" keyword in control file to identify data for respective columns.
lets see control file here.
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
append
into table emp_loader
(empno position(1:3),
ename position(5:11),
job position(12:13),
mgr position(15:17),
hiredate position(19:27),
sal position(29:32),comm position(34),deptno position(35:36))
into table emp_personal
(emp_id position(1:3),
dob position(38:46),
native position(48:56))
csv file content:
105 Chinnu IT 104 05MAY2018 2500 50 05FEB1989 BANGALORE
Loading text file data using SQL Loader:
Data is loaded into 2 tables from single csv file.
Scenario 6:
How to load fixed length data in csv file ?.
In some scenario's clients will provide CSV files without any delimiter but with fixed length for each field in text file.
eg: First 3 characters are empid and next 7 characters are emp name and next 2 characters are JOB etc.
csv file content
105Chinnu IT10405MAY20182500 20 50
106LikithaIT10205APR20183500030050
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\t_emp_file.CSV'
append
into table emp_loader
(empno position(1:3),
ename position(4:10),
job position(11:12),
mgr position(13:15),
hiredate position(16:24),
sal position(25:29),
comm position(30:32),
deptno position(33:34))
as you observed we have text file without delimiter so we have POSITION keyword with particular length in Control file.
Loading text file data using SQL Loader:
Loading text file data using SQL Loader:
data is loaded as per length we have specified in control file.
Scenario 7:
Can we change data while loading in database ?
for this scenario , lets create new table and one sequence as below.
Table created
SQL> create sequence seq_id start with 1 maxvalue 10000 nocache;
Sequence created
text file content:
venky 05021989789
Venkatesh 05021991916
and file name is "changing_data.txt"
Control File :
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\changing_data.txt'
APPEND
INTO TABLE loader_change_data
(Id "seq_id.NEXTVAL",
name POSITION (1:12) "UPPER(:name)",
DOB POSITION (13:20) "TO_DATE(:DOB,'DDMMYYYY')",
Country CONSTANT 'INDIA',
Graduation_GPA POSITION (21:23) ":Graduation_GPA/100",
Time "TO_CHAR(SYSDATE,'HH24:MI')"
)
we are loading data into "loader_change_data" and we are using POSITION keyword here as we got fixed length data in CSV file.
you can observer above control file, as we used sequence for Id Column , upper , to_date , to_char , Constant and also to taking decimal values for "Graduation_GPA".
Loading text file data using SQL Loader:
C:\Users\Venky>sqlldr phpmetreg/phpmetreg@jpac12c control=C:\Users\Venky\Desktop\Loader\loader_data_change.ctl log=C:\Users\Venky\Desktop\Loader\changing_data.LOG
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 15 16:04:07 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Scenario 8:
How to add conditions while loading data and how to handle null values in text file ?
We will take above table and with same csv file and scenario is , wherever name is "venky" , that data should not be loaded.
so for this scenario, control file would be as follows with "WHEN " clause.
venky 05021989789
Venkatesh 05021991916
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\changing_data.txt'
APPEND
INTO TABLE loader_change_data
WHEN name<>'venky'
(Id "seq_id.NEXTVAL",
name POSITION (1:12) "UPPER(:name)",
DOB POSITION (13:20) "TO_DATE(:DOB,'DDMMYYYY')",
Country CONSTANT 'INDIA',
Graduation_GPA POSITION (21:23) ":Graduation_GPA/100",
Time "TO_CHAR(SYSDATE,'HH24:MI')"
)
Loading text file data using SQL Loader:
C:\Users\Venky>sqlldr phpmetreg/phpmetreg@jpac12c control=C:\Users\Venky\Desktop\Loader\loader_data_change.ctl log=C:\Users\Venky\Desktop\Loader\changing_data.LOG
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 15 17:28:03 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
Scenario 9:
how to handle null values in text file ?
we can fix this issue by using "TRAILING NULLCOLS" in control file.
CSV file content:
:venky:05021989:789:
:Venkatesh:05021991:916:
We have 6 columns in table but above csv file has only four ":" to differentiate fields instead of Five, reason is we have constant value for one column in control file so we dont need it from csv file.
you may think for id column also we are taking from sequence and dob is from sysdate so we need to reduce 2 more ':' in csv file but here actual logic is its applicable only for CONSTANT.
Control File:
LOAD DATA
INFILE 'C:\Users\Venky\Desktop\Loader\changing_data.txt'
APPEND
INTO TABLE loader_change_data
fields terminated by ':'
TRAILING NULLCOLS
(Id "seq_id.NEXTVAL",
name "UPPER(:name)",
DOB "TO_DATE(:DOB,'DDMMYYYY')",
Country CONSTANT 'INDIA',
Graduation_GPA ":Graduation_GPA/100",
Time "TO_CHAR(SYSDATE,'HH24:MI')"
)
Loading text file data using SQL Loader:
C:\Users\Venky>sqlldr phpmetreg/phpmetreg@jpac12c control=C:\Users\Venky\Desktop\Loader\loader_data_change.ctl log=C:\Users\Venky\Desktop\Loader\changing_data.LOG
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 15 17:43:10 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Scenario 10:
Whats parfile and how to use it.
This file is useful to keep all parameter which we pass in sqlldr command.
eg:
we created below "loader.par" parameter file with following content.
userid=scott/tiger@venkydb
control=C:\Users\Venky\Desktop\Loader\loader_data_change.ctl
log=C:\Users\Venky\Desktop\Loader\changing_data.LOG
We are going to use above control file and csv file and only change is while loading data as below.
Loading text file data using SQL Loader:
C:\Users\Venky>sqlldr parfile=C:\Users\Venky\Desktop\Loader\loader.par
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jun 15 18:10:33 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
No comments:
Post a Comment