Search This Blog

Monday, June 1, 2009

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

4 Comments:

Unknown said...

Hi Suresh,
My name is rajesh and I was browsing through your website which was very helpful. I could get a lot of information from the website. Thank You for such a fantastic effort. I have a few doubts about the API's in purchasing. If you can clarify them it could be very helpful for me.

I was using SQLLDR to import data from the flat file to the ora apps server.
I had to validate data.
I came to know that validations can be done in two ways
1. By checking in the table
2. By going into the form and querying whether the data is present or not

I am undergoing training in oracle applications and I did all the validations by writing a trigger on the table po_headers_interface and then running the concurrent program to import PO's. Import standard Purchase orders concurrent program to import the data to the base tables. Now my assignment is to write a concurrent program to load data from a flat file and then call the standard concurrent program in the user defined program to perform the validatons and then insert data into the interface tables. My trainer asked me to do the validations with simple SQL commands and insert the data using API's. I am confused because I have never worked with API's and I am also having trouble with the datatypes used in API's (table type etc). I f you could help me it could be very useful for me.
Thanks in advance,


Rajesh Kolagatla

nicetoknow said...

In the example above, in MFILES.CTL.
INFILE '/u01/projs/denver.dat'
INFILE '/u01/projs/orlando.dat'

Instead of hardcoding denver.dat and orlando.dat, Is there a way to dynamically read the file names from the folder and specify after the INFILE '*.csv'.
All .csv files will be in one directory.

I am calling the below line in a shell script.

sqlldr $UID control=$APP_BIN/addm_loadtemptable.ctl, bad=$LOADERBAD, log=$LOADERLOG, errors=10000, data=$SRCFILE

Can the above line be called in a loop and the each time a new file in the directory be given as a source.

Highly appreciate your help. thans

Suresh Vaishya said...

Yes there is a way to do that .. I will try to post the steps/code on how to do that. Please check on later.

Regards,
Suresh

Suresh Vaishya said...

Check out http://sureshvaishya.blogspot.com/2010/03/based-on-request-from-one-of-our-reader.html

Regards,
Suresh

Copyright (c) All rights reserved. Presented by Suresh Vaishya