OraFAQ Forum: Server Utilities » SQL*Loader: Mutiple input files into multiple tables

Home » RDBMS Server » Server Utilities » SQL*Loader: Mutiple input files into multiple tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Loader: Mutiple input files into multiple tables [message #219515]

Wed, 14 February 2007 13:10

syang
Messages: 30
Registered: February 2007

Member

Hi All,

Is there a way with SQL*Loader that allows us to load multiple infiles into multiple tables in the same sqlloader session, using one control file? We have 20 tables that need to have data populated; each of the 20 tables has its own input file.

Questions:

1. Can we setup one single control file to accomplish these loads of 20 tables? If we can, what would the control file look like?

2. Do we have to setup control file (20 of them) for each of the table to be loaded.

Below are some examples:

Table 1:

SQL> desc accrediting_organization
Name Null? Type
------------------- -------- ---------------
ACRDTN_ORG_NAME NOT NULL VARCHAR2(35)
LAST_CHG_USER_ID VARCHAR2(30)
LAST_CHG_DT DATE

Table 2

SQL>desc accreditation_status

Name Null? Type
------------------ -------- ------------
ACRDTN_STUS_CD NOT NULL CHAR(1)
ACRDTN_STUS_DESC VARCHAR2(20)
LAST_CHG_USER_ID VARCHAR2(30)
LAST_CHG_DT DATE

Sample data for table 1:

Healthcare,xxxx,11/28/06
Rehabilitation,yyyy,12/23/06
Accreditation,zzzz,01/12/07

Sample data for table 2:

1 ,Incomplete,xxxx,10/25/06
2 ,Certified,yyyy,09/02/06
3 ,Complete,zzzz,08/11/05

Your input is greatly appreciated!

Thanks!

Report message to a moderator

Re: SQL*Loader: Mutiple input files into multiple tables [message #219516 is a reply to message #219515]

Wed, 14 February 2007 13:15

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#sthref871
Yes.
Just use multiple input clauses.
Already accounted in documentation.

Report message to a moderator

Re: SQL*Loader: Mutiple input files into multiple tables [message #220471 is a reply to message #219516]

Tue, 20 February 2007 15:43

syang
Messages: 30
Registered: February 2007

Member

Mahesh,

Thank you for your response.

However, I am still not quite clear about how I am supposed to setup the control file and the data file (infile).

For example, I have 2 tables needed to be loaded with sqlldr. In my control file, I have this:

load data
infile multiple_tab.dat
insert
into table table_1
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
BIDDER_NUM,
CMPTV_BID_CYC_NUM,
CMPTV_BID_AREA_NUM,
PROD_CTGRY_ID,
BID_EXPNSN_TYPE_CD,
BID_EXPNSN_TYPE_OTHR_DESC,
CRNT_EXPNSN_TXT,
PLAN_EXPNSN_TXT,
LAST_CHG_USER_ID,
LAST_CHG_DT date "mm/dd/yyyy HH:MI:SS AM"
)
into table table_2
(
NSC_NUM,
NSC_ACTV_IND,
AUTHRZG_OFCL_BIRTH_DT,
AUTHRZG_OFCL_5_PSTN_SSN,
AUTHRZG_OFCL_1ST_NAME,
AUTHRZG_OFCL_LAST_NAME,
LAST_CHG_USER_ID,
LAST_CHG_DT
)

Question here:

How to setup the multiple_tab.dat file so that sqlldr recognizes which data goes to which table?

Any input will be greatly appreciated.

Thanks!

Report message to a moderator

Re: SQL*Loader: Mutiple input files into multiple tables [message #220477 is a reply to message #220471]

Tue, 20 February 2007 16:38

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Apologies. I misread (thought the table structures are same and can use a when clause).
I am not aware of any direct method with sqlldr. May be others should enlighten me.
>>2. Do we have to setup control file (20 of them) for each of the table to be loaded.
If you are going to do this frequently and if those input filenames can be sequenced/identified along with a target tablename, you can write a simple script that will read the file and load into the appropriate table(by creating the controlfile on the fly).

Report message to a moderator