Sqlloader Examples

SQLLDR - Shell script to load Multiple files in table

Based on request from one of our reader, here is the post to read file from a directory and then call sqlloader command to load data file into a table.
Assumptions:
1) There has to be some way/standard to recognize the file name. In my case its a .dat file starting withsample. If you don't know data file name then create a directory specific to the load and select all data files from that directory.
2) The file format has to be same so that same .ctl file can be used to read the file and load the table.
3) Once loaded the file is then archived.

Control file code

LOAD DATA
insert into table sv_temp
fields terminated by '|' optionally enclosed by '"'
(first_col
,second_col
)

Shell Script Code

cd $CUSTOM_TOP/data
for file in sample*.dat
do
sqlldr userid=$login control=$CUSTOM_TOP/bin/sv_test.ctl data=$CUSTOM_TOP/data/$file
mv $CUSTOM_TOP/data/$file $CUSTOM_TOP/archive/$file
done

3 files sample1.dat, sample2.dat, sample3.dat was copied in CUSTOM_TOP/data directory.
Sample Output is

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 8
Commit point reached - logical record count 9

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4
Commit point reached - logical record count 5

SQL*Loader: Release 8.0.6.3.0 - Production on Fri Mar 5 13:24:18 2010

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 7

SQL Loader (SQLLDR) in Oracle Applications

One of the executable type in Oracle applications is SQL*Loader. In this post we will discuss more about how to define, create and use this type of Execution Method.

Execution Method SQL*Loader is used to load data into database table using SQL*Loader. Below are the steps that needs to completed
1) Create Control File and copy it in the TOP/bin directory. E.g. if your custom application top directory is $XX_TOP, then the file should be copied in $XX_TOP/bin directory

OPTIONS (SKIP=1)
LOAD DATA infile '%1'
APPEND INTO TABLE sv_test_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
(
   item_number     "trim(:item_number)"
 , description     "trim(:description)"
 , attribute1      "trim(:attribute1)"
 , process_flag    Constant 'UNPROCESSED'
)

2) Create an executable with execution method as SQL*Loader and use the control file Name as Execution file Name. The Application should same as where the file is copied.

3) Create a concurrent program definition and use the executable created above.
It is optional to have any parameters to the program, but in this case the data file name should be same as control file name except the extension for the data file being .dat
If you notice in the control file definition above I have used %1 as the infile name. This is how parameters are referenced in control file.
So now we can create conc. program with a file name as a parameter(Full file name with path should be entered and this is case sensitive.)

4) Assign this program to the request group and it is ready to be used.

SQL Loader limit number of rows

Based on a query from one of our reader here is an example to illustrate how to limit number of rows to be loaded in SQL Loader. This can be done using option LOAD

OPTIONS (SKIP=1, LOAD=10, ERRORS=5)
LOAD DATA infile c:/sv_test.dat
REPLACE INTO TABLE sv_test_sql_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
    item_number     "trim(:item_number)"
  , vendor_name "trim(:vendor_name)"
  , vendor_site_name "trim(:vendor_site_name)"
  , supplier_item   "trim(:supplier_item)"
  , process_flag  Constant 'UNPROCESSED'
)

In the example above the total records to be loaded is limited to 10, error records is 5 and 1 record is skipped.

These options can also be given with sqlldr command as follows

sqlldr control='sv_test.ctl' data='sv_test.dat' load=10 errors=5  skip=1

Keywords: SQL*LOADER, ERRORS, SKIP, LOAD

Load multiple user datafiles into multiple tables using SQL*LOADER

On request from one of the reader, below are the steps to load multiple tables using multiple datafiles.
Problem Description
-------------------
You have multiple datafiles to be loaded using SQL*Loader. Each of the data
files contains records that must be loaded into one of several tables. The
following is an example that uses the INFILE clause for each datafile and a
WHEN clause for each table to do this.

Records in a datafile that are to be loaded into multiple table can be loaded
based on a check for a value that distinguishes each record. This check can be
on an entire column or on a specific position within the data file.

Solution Description
--------------------
Example:
Datafiles are generated each day that contain multiple employees and the
projects those employees worked on that day. Projects can be worked on from
many locations, and an employee can work on many projects each day. Each week
all the daily files are gathered and loaded into project tables based on
location.
-----------------------Table Create Statements---------------
CREATE TABLE DENVER_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

CREATE TABLE ORLANDO_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

CREATE TABLE MISC_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

-------------------Control File - MFILES.CTL------------------
LOAD DATA
INFILE '/u01/projs/denver.dat'
INFILE '/u01/projs/orlando.dat'
APPEND

INTO TABLE DENVER_PRJ
WHEN PROJNO = '101'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )

INTO TABLE ORLANDO_PRJ
WHEN PROJNO = '202'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )

INTO TABLE MISC_PRJ
WHEN PROJNO != '101' AND PROJNO != '202'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )
--------------------Datafiles: DENVER.DAT---------------------
1011234515
1015432140
1012345620
3032345610
--------------------Datafiles: ORLANDO.DAT--------------------
2021234515
2022345610
4041234510
--------------------------------------------------------------
SQL*Loader will read all the input files together. It will parse each record
and then based on the condition(s) in a WHEN clause will evaluate whether the
record can be loaded into that table. Care should be taken as to how WHEN
conditions are constructed because each record is evaluated against every WHEN
clause and loaded into all tables that match the condition.

Reference: Metalink Note: 1023792.6

Tips and Tricks with SQL Loader

Following are some of the tips and tricks that can be used with SQL Loader

1) Load text for a column which is having more than 4000 bytes.
Use following syntax

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)
)

2) How to use oracle functions with SQL Loader

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)   --- first 40000 characters
, column1  "trim(:column1)"       --- Trims and loads
, column2  "replace(:column2,'\n',chr(10))"  --- replace \n with new line
, column3  DATE "DD-MON-YYYY"   --- defining date style
, column4  "upper(:column4)"      --- changing to upper case
, column5  "lower(:column5)"      --- changing to lower case
, column6  constant "FIXED"      --- assigning a constant value
, column7  "sv_sequence.nextval"  --- defaulting a value from sequence
)

Thats it for today .. will update with more commands later.