SQL*Loader - Step by Step Guide How to Load a Datafile Into a Table (2024)

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.parCode 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 theemail.datfile:

1,john.doe@example.com2,[emailprotected]3,[emailprotected]Code language: CSS (css)

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

load data into table emailsinsertfields 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/Abcd1234control=email.ctllog=email.logbad=email.baddata=email.datdirect=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: DirectLoad completed - logical record count 3.Table EMAILS:3 Rows successfully loaded.Check the log file:email.logfor 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 2019Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.Control File: email.ctlData File: email.dat Bad File: email.bad Discard File: none specified (Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Continuation: none specifiedPath used: DirectTable 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 keysTable 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 : 5000Stream buffer bytes: 256000Read buffer bytes: 1048576Total logical records skipped: 0Total logical records read: 3Total logical records rejected: 0Total logical records discarded: 0Total stream buffers loaded by SQL*Loader main thread: 2Total stream buffers loaded by SQL*Loader load thread: 0Run began on Tue Jul 23 08:04:46 2019Run ended on Tue Jul 23 08:04:48 2019Elapsed time was: 00:00:01.47CPU 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.