OraFAQ Forum: Server Utilities » conditionally insert data using Oracle SQL* Loader into multile Oracle tables using the same control

Home » RDBMS Server » Server Utilities » conditionally insert data using Oracle SQL* Loader into multile Oracle tables using the same control

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

conditionally insert data using Oracle SQL* Loader into multile Oracle tables using the same control [message #185725]

Thu, 03 August 2006 05:22

sachin sharma
Messages: 9
Registered: February 2004

Junior Member

Hi Friends,
I have tried a few examples with oracle sql* loader.

I am trying to insert data into different oracle tables with the same control file.

The details are as follows:

I have a CSV data file "DATA.csv" as below:
----------------------------------------------------

sachin,28,1098,1
visu,28,10666,2
mahesh,26,109978,4
cliffy,25,1054,2
nadeem,29,1098,1
alpesh,22,10432,3

the control file is:
-----------------------

load data
infile 'D:\OCA\SQL LOADER\SAMPLE4\DATA.csv'
append
into table d_tbl1
fields terminated by ","
when ":city_id=1"
(cand_name,
cand_age,
cand_passport_no,
city_id)

into table d_tbl2
fields terminated by ","
when ":city_id=2"
(cand_name,
cand_age,
cand_passport_no,
city_id)

Querries
-----------
1) Is it possible to insert data into different oracle tables using same control file (as I am trying to do above) ?
2) Can I declare a variable in sql* loader, similar to local variables in a pl/sql block, & then use its value to create logic for inserting data conditionally ?

Thanks.
Sachin.

Report message to a moderator

Re: conditionally insert data using Oracle SQL* Loader into multile Oracle tables using the same con [message #185737 is a reply to message #185725]

Thu, 03 August 2006 06:25

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

Senior Member
Account Moderator

1)
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96652/ch10.htm#1007219
2)
yes. You need a wrapper script (shell/perl/whatever). Have a standard template control file.
every time you load, create a new control file from the existing one ( just change the input table_name, passed as a variable).
Idea is to create a control file dynamicallly for every load, with a new table_name. Search the forum, there are many examples.

Report message to a moderator

Previous Topic:	downgrade oracle9i to oracle8i
Next Topic:	export tables in text files

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 30 06:55:07 CDT 2024