SQL Loader in oracle database 11g
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
|
1 2 3 |
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
|
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Also read, How to create a tablespace
Words from dbapath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.