Home » RDBMS Server » Server Utilities » SQL*Loader: Mutiple input files into multiple tables
SQL*Loader: Mutiple input files into multiple tables [message #219515] Wed, 14 February 2007 13:10 Go to next message
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!
Re: SQL*Loader: Mutiple input files into multiple tables [message #219516 is a reply to message #219515] Wed, 14 February 2007 13:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: SQL*Loader: Mutiple input files into multiple tables [message #220471 is a reply to message #219516] Tue, 20 February 2007 15:43 Go to previous messageGo to next message
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!
Re: SQL*Loader: Mutiple input files into multiple tables [message #220477 is a reply to message #220471] Tue, 20 February 2007 16:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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).

Re: SQL*Loader: Mutiple input files into multiple tables [message #221228 is a reply to message #220477] Sun, 25 February 2007 19:56 Go to previous messageGo to next message
syang
Messages: 30
Registered: February 2007
Member
Does anyone know how to create control files on fly?
Re: SQL*Loader: Mutiple input files into multiple tables [message #221230 is a reply to message #219515] Sun, 25 February 2007 20:11 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
by using the scripting language of choice such as PERL.
Re: SQL*Loader: Mutiple input files into multiple tables [message #221840 is a reply to message #219515] Wed, 28 February 2007 11:11 Go to previous message
Ari00
Messages: 3
Registered: February 2007
Junior Member
I don't know if this will work for you, but I use an sh file when I have some control files to load. So I just need to group them into a for clause.
Previous Topic: Database Language
Next Topic: EXP-00003: no storage definition found which oracle client version should I use for exporting ....
Goto Forum:
  


Current Time: Fri Dec 09 04:09:07 CST 2016

Total time taken to generate the page: 0.18748 seconds