What is sql loader in Oracle 11g

SQL Loader loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts a little limitation on the format of the data in the data file. You can use SQL*Loader to do the following:

Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.

SQL*Loader Features

Load data from multiple datafiles during the same load session.
Load data into multiple tables during the same load session.
Specify the character set of the data.
Selectively load data (you can load records based on the records’ values).
Manipulate the data before loading it, using SQL functions.
Generate unique sequential key values in specified columns.
Use the operating system’s file system to access the datafiles.
Load data from disk, tape, or named pipe.
Generate sophisticated error reports, which greatly aid troubleshooting.
Load arbitrarily complex object-relational data.
Use secondary datafiles for loading LOBs and collections.
Use either conventional or direct path loading. While conventional path loading is very flexible, direct path loading provides superior loading performance.

sql loader control file:

SQL*Loader control file defines the columns to be loaded and instructs the loader to load the data line by line from ldr.csv into the sqlloader table. Each line in loader2.dat holds a comma-separated list of fields to be loaded.

Example : Here we will load the data present in ldr.csv into table.

[oracle@stagdb ~]$ cat ldr.csv

hyderabad,100,telangana

vijayawada,200,andhra pradesh

warangal,203,telangana

Step 1: Create a table

TEST> create table sqlloader (city varchar2(100), city_code varchar2(100), state varchar2(100));

Table created.

TEST> create table sqlloader (city varchar2(100), city_code varchar2(100), state varchar2(100));

Table created.

Step 2: Create a control file with the name ldr.ctl (any name)

LOAD DATA

INFILE ‘/home/oracle/ldr.csv’ “STR ‘\n'”

APPEND

INTO TABLE TEST.sqlloader

FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘

TRAILING NULLCOLS

(

                                                                       city char,

                                                                       city_code char,

                                                                       state char

)

Step 3: Execute Sql *Loader command

sqlldr TEST/test CONTROL=/home/oracle/ldr.ctl LOG=/home/oracle/ldr.log DATA=/home/oracle/ldr.csv DIRECT=TRUE ERRORS=1000

[oracle@abcdb~]$ sqlldr TEST/test CONTROL=/home/oracle/ldr.ctl LOG=/home/oracle/ldr.log
DATA=/home/oracle/ldr.csv DIRECT=TRUE ERRORS=1000<strong> </strong>

1 2	[oracle@abcdb~]$ sqlldr TEST/test CONTROL=/home/oracle/ldr.ctl LOG=/home/oracle/ldr.log DATA=/home/oracle/ldr.csv DIRECT=TRUE ERRORS=1000<strong> </strong>

SQL*Loader: Release 11.2.0.3.0 – Production on Wed Mar 28 16:29:02 2018

Load completed – logical record count 3.

[oracle@stagdb ~]$ cat /home/oracle/ldr.log

SQL*Loader: Release 11.2.0.3.0 – Production on Wed Mar 28 16:29:02 2018

Control File: /home/oracle/ldr.ctl

Data File: /home/oracle/ldr.csv

File processing option string: “STR ‘

‘”

Bad File: /home/oracle/ldr.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 1000

Continuation: none specified

Path used: Direct

Table TEST.SQLLOADER, loaded from every logical record.

Insert option in effect for this table: APPEND

TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype

—————————— ———- —– —- —- ———————

CITY FIRST * , O(“) CHARACTER

CITY_CODE NEXT * , O(“) CHARACTER

STATE NEXT * , O(“) CHARACTER

Table TEST.SQLLOADER:

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: 1

Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Wed Mar 28 16:29:02 2018

Run ended on Wed Mar 28 16:29:02 2018

Elapsed time was: 00:00:00.16

CPU time was: 00:00:00.02

[oracle@stagdb ~]$

Check the table with a select statement

SYS> select * from test.sqlloader;

CITY CITY_CODE STATE

——————– ——————– ——————–

hyderabad 100 telangana

vijayawada 200 andhra pradesh

warangal 203 telangana