Sql loader in oracle

Sql loader in oracle

If you are using Oracle database, at some point you might have to deal with uploading data to the tables from a text file. Actually, Sql loader is the utility to use for high performance data loads. It is used for moving data from external files into the Oracle database.

The Control File

The SQL Loader control file, is a flat file or text file, contains information that describes how the data will be loaded.

 It contain the following information…

§  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

§  ( id, name, dept, salary ) – Lists the name of the column names in the table into which the data should be uploaded.

Options in SQL*Loader while loading the data.

      a) INSERT: Specifies that you are loading into an empty table.

(b) APPEND: If we want to load the data into a table which is already containing some rows.

(c) REPLACE: Specifies that, we want to replace the data in the table before loading. Will 'DELETE' all the existing records and replace them with new.

(d) TRUNCATE: This is same as 'REPLACE', but SQL*Loader will use the 'TRUNCATE' command instead of 'DELETE' command.

Example

Firstly, We create the table as shown below.

SQL> create table employee

(

  id integer,

  name varchar2(10),

  dept varchar2(15),

  salary integer)

Now We create the control file to upload data.

Define the SQL*Loader control file, called .ctl, that will allow us to load the data.

load data

 infile '/home/xyz/employee.txt'

 into table employee

 fields terminated by ","

 ( id, name, dept, salary )

 Now we execute the sqlldr command to upload these new record to the empty table .

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-new.ctl

Commit point reached - logical record count 5

Now verify...

SQL> select * from employee;

        ID NAME       DEPT                SALARY 

---------- ---------- --------------- ---------- -------

       100 Ram      Sales                 5000

       200 Ravi     Technology            5500

       300 Raja     Technology            7000

       400 Mohan      Marketing             9500

       500 Shyam    Technology            6000

 Data inside the Control File using BEGINDATA

We can also specify the data directly inside the control file itself using BEGINDATA keyword. i.e Anything that comes after BEGINDATA will be treated as data to be uploaded to the table .

$ Cat sqlldr-add-new-with-data.ctl

Load data

 infile *

 into table employee

 fields terminated by ","

 ( id, name, dept, salary )

begindata

100,Ram,Sales,5000

200,Ravi,Technology,5500

300,Raj,Technology,7000

400,Shyam,Marketing,9500

 Now We Execute sqlldr to upload the data from the control file.

$ sqlldr scott/tiger control=/home/xyz/sqlldr-add-new-with-data.ctl

Loading fixed length (positional) data

The control file can also specify that records are in fixed format. A file is in fixed record format when all records in a datafile are the same length.

LOAD DATA

INFILE *

INTO TABLE positional_data

(data1 POSITION(1:5),

data2 POSITION(6:15)

)

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB