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
INSERTstatements 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 write 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
INSERTstatements 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:
The following picture illustrates the SQL*Loader process:
Let’s take the example of using the SQL*Load tool.
We will load email data CSV file format into the
emails table in the database.
Prepare the input files
The following is the content of the
1,email@example.com 2,firstname.lastname@example.org 3,email@example.com
The contents of the control file (
email.ctl) is as follows:
load data into table emails insert fields terminated by "," ( email_id, email )
In the control file:
load data into table emails insertinstruct the SQL*Loader to load data into the emails table using the INSERT statement.
fields terminated by "," (email_id,email)specifies that each row in the file has two columns
Here is the content of the parameter file (
userid=ot@pdborc/Abcd1234 control=email.ctl log=email.log bad=email.bad data=email.dat direct=true
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.bad file stores invalid data. And 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:
After having three files, you can place it in a directory e.g., C:\loader.
Here is the link to download the three files in a zip file format:
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 );
Second, launch the SQL*Loader program from the directory that you store the files using the
C:\loader> sqlldr parfile=dept_loader.par
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.
Third, log in to the Oracle and verify the contents of the
SELECT * FROM emails;
Here is the result set:
Finally, review the log file:
SQL*Loader: Release 184.108.40.206.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.07
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.