SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the data file. You can use SQL*Loader to do the following:
- Load data across a network if your data files are on a different system than the database.
- Load data from multiple data files during the same load session.
- Load data into multiple tables during the same load session.
- Specify the character set of the data.
- Selectively load data (you can load records based on the records’ values).
- Manipulate the data before loading it, using SQL functions.
- Generate unique sequential key values in specified columns.
- Use the operating system’s file system to access the data files.
- Load data from disk, tape, or named pipe.
- Generate sophisticated error reports, which greatly aid troubleshooting.
- Load arbitrarily complex object-relational data.
- Use secondary data files for loading LOBs and collections.
- Use conventional, direct path, or external table loads.
- Input Datafile contains file containing the data to be loaded.The record format can be specified in the control file with the INFILE parameter.
cat /home/oracle/employee.txt 100,Hari,MCA,5000 200,Karthi,Technology,5500 300,Sunil,Technology,7000
- Control file contains the instructions to the sqlldr utility. This tells sqlldr the location of the input file, the format of the input file, and other optional meta data information required by the sqlldr to upload the data into oracle tables.
load data infile '/home/oracle/employee.txt' into table employee fields terminated by "," ( emp_id,emp_name,dept,salary )
The above control file indicates the following:
- infile – Indicates the location of the input data file
- into table – Indicates the table name where this data should be inserted
- fields terminated by – Indicates the delimiter that is used in the input file to separate the fields
- ( emp_id, emp_name, dept, salary ) – Lists the name of the column names in the table into which the data should be uploaded
3. Discard file contains rejected rows those were discarded because they were filtered due to a statement in SQL*Loader control file. Data written to any database table is not written to the discard file.
4. Bad File contains the records which are rejected either by SQL Loader or by the database because of the bad formatting or data type mismatch.It will have the same name as the data file, with a .bad extension.
5. Log File contains a detailed summary of the load, including a description of any errors that occurred during the load.
Basics execution of sqlloader :-
Create the table structure:-
SQL> create table employee(emp_id integer,emp_name varchar2(10),dept varchar2(15),salary integer,Join_date date); Table created.
Input Data :-(Datafile)
cat /home/oracle/employee.txt 100,Hari,MCA,5000 200,Karthi,Technology,5500 300,Sunil,Technology,7000
INSERT : Default value for loading data using SQL loader.
Sqlldr control file :-
[oracle@test] cat > sqlldr-add-records.ctl load data infile '/home/oracle/employee.txt' into table employee fields terminated by "," ( emp_id,emp_name,dept,salary )
Run the sqlloader utility:-
[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-add-records.ctl SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 13 02:40:28 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 3 Table EMPLOYEE: 3 Rows successfully loaded. Check the log file: sqlldr-add-records.log for more information about the load.
Check the loaded data in table
SQL> select * from hari.employee; EMP_ID EMP_NAME DEPT SALARY JOIN_DATE ---------- ---------- --------------- ------- --------- 100 Hari MCA 5000 200 Karthi Technology 5500 300 Sunil Technology 7000
APPEND :
Input data into existing table employee :-
cat /home/oracle/newemployee.txt 400,Ram,DBA,5500 500,Siva,Developer,7000
[oracle@test] cat > sqlldr-append-records.ctl load data infile '/home/oracle/newemployee.txt' append into table employee fields terminated by "," ( emp_id,emp_name,dept,salary )
[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-append-records.ctl SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 13 02:47:18 2018Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 2 Table EMPLOYEE: 2 Rows successfully loaded. Check the log file: sqlldr-append-records.log for more information about the load.
SQL> select * from hari.employee; EMP_ID EMP_NAME DEPT SALARY JOIN_DATE ---------- ---------- --------------- ---------- --------- 100 Hari MCA 5000 200 Karthi Technology 5500 300 Sunil Technology 7000 400 Ram DBA 5500 500 Siva Developer 7000 5 rows selected.
TRUNCATE :
Table Structure :
SQL> select * from hari.employee; EMP_ID EMP_NAME DEPT SALARY JOIN_DATE ---------- ---------- --------------- ---------- --------- 100 Hari MCA 5000 200 Karthi Technology 5500 300 Sunil Technology 7000 400 Ram DBA 5500 500 Siva Developer 7000 5 rows selected.
Input Data file :-
cat /home/oracle/newemployee.txt 400,Sam,DBA,5500 500,Scott,Developer,7000
Sqlloader control file :
cat > sqlldr-truncate-records.ctl load data infile '/home/oracle/newemployee.txt' truncate into table employee fields terminated by "," ( emp_id,emp_name,dept,salary )
Sqlloader execution :-
[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-truncate-records.ctl SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 15:22:16 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 2 Table EMPLOYEE: 2 Rows successfully loaded. Check the log file: sqlldr-truncate-records.log for more information about the load.
Check the table data,
SQL> select * from hari.employee; EMP_ID EMP_NAME DEPT SALARY JOIN_DATE ---------- ---------- --------------- ---------- --------- 400 Ram DBA 5500 500 Siva Developer 7000
REPLACE :
When REPLACE is specified, the entire table is replaced, not just individual rows.It uses an implicit truncate of the table to replace existing data. It does not look at specific rows but rather removes all rows and inserts new ones, even if the new data is the same as the original data.
Table Structure :-
SQL> select * from hari.employee; EMP_ID EMP_NAME DEPT SALARY JOIN_DATE ---------- ---------- ----------- ------- --------- 4000 Steven ITlead 50000 5000 Brad SystemAdmin 10000
Input data :-
[oracle@orcl:~ orcldemo] cat /home/oracle/newemployee.txt 4000,Steven,ITlead,50000 5000,Brad,SystemAdmin,10000
Control file :-
[oracle@orcl:~ orcldemo] cat sqlldr-replace-records.ctl load data infile '/home/oracle/newemployee.txt' replace into table employee fields terminated by "," ( emp_id,emp_name,dept,salary )
Sqlloader execution :-
[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-replace-records.ctl SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 17:08:22 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 2 Table EMPLOYEE: 2 Rows successfully loaded. Check the log file: sqlldr-replace-records.log for more information about the load.
Check Table data,
SQL> select * from hari.employee; EMP_ID EMP_NAME DEPT SALARY JOIN_DATE ---------- ---------- ----------- ------- --------- 4000 Steven ITlead 50000 5000 Brad SystemAdmin 10000
Bad and Discard file Scenario :-
Table Structure :-
SQL> create table dept (deptno number,dname varchar(20),location varchar(20)); Table created.
Input data:-
cat sqlloader.dat
10,ACCOUNTING,PAK
30,SALES,RUSSIA
4D,OPERATIONS,USA
50,HUMAN RESOURCE,USA
60,IT,USA
70,PRODUCTION,ENG
80,QUALITY,USA
Control file :
cat sqlload.ctl load data infile '/home/oracle/sqlloader.dat' badfile '/home/oracle/badrecords.bad' discardfile '/home/oracle/dicardload.dsc' into table dept WHEN LOCATION!='USA' fields terminated by "," (DEPTNO,DNAME,LOCATION)
Run the sqlloader utility :-
[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlload.ctl SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 14:43:30 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 7 Table DEPT: 3 Rows successfully loaded. Check the log file: sqlload.log for more information about the load.
Discard file record:-
[oracle@test:~ orcldemo] cat dicardload.dsc 4D,OPERATIONS,USA 50,HUMAN RESOURCE,USA 60,IT,USA 80,QUALITY,USA
Bad file record:-
[oracle@test:~ orcldemo] cat badrecords.bad 10,ACCOUNTING,PAK 30,SALES,RUSSIA 70,PRODUCTION,ENG
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
