Load data into oracle table from a text file

Load data into oracle table from a text file

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables. In this example I have used PARFILE,CONTROL, and LOG files.

The SQL*Loader PARFILE

The parfile specifies

The controlfile location
The logfile location

The SQL*Loader Control File

The SQL*Loader control file is the key to any load process. The control file provides the following information to SQL*Loader:
The name and location of the input data file
The format of the records in the input data file
The name of the table or tables to be loaded

The Log File

The log file is a record of SQL*Loader's activities during a load session. It contains information such as the following:
The names of the control file, log file, bad file, discard file, and data file
The values of several command-line parameters
A detailed breakdown of the fields and datatypes in the data file that was loaded
Error messages for records that cause errors
Messages indicating when records have been discarded
A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load

Example:

create table loader_table(first_name varcahr2(100),last_name varchar2(100),id number)
/

As a normal User:

sqlldr username@server/password parfile=loader.par

sqlldr username/password@server parfile=loader.par

As sysdba

sqlldr \'sys/sys AS SYSDBA\' PARFILE=loader.par

The parfile looks like this.

[laptop@laptop.practice.com scripts]$ cat loader.par
CONTROL=loader.ctl
LOG=loader.log

The control file

[laptop@laptop.practice.com scripts]$ cat loader.ctl
LOAD DATA
infile 'loader.dat'
INTO TABLE loader_table --if loading from sys then schema.table
FIELDS TERMINATED BY ','
(first_name,last_name,id)

The actut file the data to be loaded from

[laptop@laptop.practice.com scripts]$ cat loader.dat
Nabi,Mohammad,1234
John,Miller,1254
Christine,Mfouz,1261
Feroz,Mohammad,1268

Below is the execution

[laptop@laptop.practice.com scripts]$ sqlldr \'sys/sys AS SYSDBA\' PARFILE=loader.par
SQL*Loader: Release 11.2.0.3.0 - Production on Thu Sep 19 03:23:14 2013

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

Commit point reached - logical record count 4

SQL > select count(*) from loader_table;

  COUNT(*)
----------
         4