Oracle SQL*Loader

Summary: in this tutorial, you will learn how to use the Oracle SQL*Loader tool to load from a flat-file into a table in the database.

Introduction to SQL*Loader tool

SQL*Loader allows you to load data from an external file into a table in the database. It can parse many delimited file formats such as CSV, tab-delimited, and pipe-delimited.

SQL*Loader provides the following methods to load data:

• Conventional path loads – construct INSERT statements from the contents of the input datafile based on the predefined specification and execute the inserts.
• Direct path loads – creates data blocks in Oracle database block format from the datafile and directly writes the data block to the database. This way is much faster than the conventional path but subject to some restrictions.
• External table loads – create an external table for the data stored in the datafile and execute INSERT statements to insert the data from the datafile into the target table. The external table loads support parallel loading if datafile is big enough.

To execute the SQL*Load tool, you need at least three files:

• The input data file stores delimited or raw data
• The parameter file stores the location of the input/output files
• The control file contains the specification on how data is loaded.

After that, you execute the command sqlldr from the command line on Windows or Terminal on GNU/Linux:

>sqlldr parfile=parameter_file.par
Code language: SQL (Structured Query Language) (sql)

The following picture illustrates the SQL*Loader process:

Let’s take the example of using the SQL*Load tool.

SQL*Loader example

We will load email data in CSV file format into the emails table in the database.

Prepare the input files

The following is the content of the email.dat file:

1,john.doe@example.com
2,[email protected]
3,[email protected]Code language: CSS (css)

The contents of the control file (email.ctl) is as follows:

load data into table emails
insert
fields terminated by ","
(
email_id,
email
)Code language: JavaScript (javascript)

In the control file:

• The load data into table emails insert instruct the SQL*Loader to load data into the emails table using the INSERT statement.
• The fields terminated by "," (email_id,email) specifies that each row in the file has two columns email_id and email separated by a comma (,).

Here is the content of the parameter file (email.par):

userid=ot@pdborc/Abcd1234
control=email.ctl
log=email.log
bad=email.bad
data=email.dat
direct=trueCode language: JavaScript (javascript)

In this parameter file, we specify the user (userid) that will connect to the Oracle database, the control file (email.ctl), log file (email.log), and data file (email.dat).

The email.bad file stores invalid data. The last line direct=true instructs the SQL*Loader to use the direct path load method.

Note that there is no space between the parameter and value, for example:

control=email.ctrlCode language: SQL (Structured Query Language) (sql)

After having three files, you can place them in a directory e.g., C:\loader.

Here is the link to download the three files in a zip file format:

Download the data, control and parameter files

Load data from a flat file into the table

First, create a new table to store the email data from the input datafile:

CREATE TABLE emails(
    email_id NUMBER PRIMARY KEY,
    email VARCHAR2(150) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Second, launch the SQL*Loader program from the directory in which you store the files using the sqlldr command:

C:\loader> sqlldr parfile=dept_loader.parCode language: SQL (Structured Query Language) (sql)

Here is the output:

Path used:      Direct

Load completed - logical record count 3.

Table EMAILS:
3 Rows successfully loaded.

Check the log file:
email.log
for more information about the load.Code language: CSS (css)

Third, log in to the Oracle and verify the contents of the emails table:

SELECT * FROM emails;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

Finally, review the log file:

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Jul 23 08:04:46 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Control File:   email.ctl
Data File:      email.dat
  Bad File:     email.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table EMAILS, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMAIL_ID                            FIRST     *   ,       CHARACTER            
EMAIL                                NEXT     *   ,       CHARACTER            

The following index(es) on table EMAILS were processed:
index OT.SYS_C0010446 loaded successfully with 3 keys

Table EMAILS:
  3 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.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        2
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Tue Jul 23 08:04:46 2019
Run ended on Tue Jul 23 08:04:48 2019

Elapsed time was:     00:00:01.47
CPU time was:         00:00:00.07Code language: JavaScript (javascript)

In this tutorial, you have learned how to use the SQL*Loader to load data from a flat file into a table in the database.