Home » RDBMS Server » Server Utilities » conditionally insert data using Oracle SQL* Loader into multile Oracle tables using the same control
conditionally insert data using Oracle SQL* Loader into multile Oracle tables using the same control [message #185725] Thu, 03 August 2006 05:22 Go to next message
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.
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 Go to previous message
Mahesh Rajendran
Messages: 10672
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.

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


Current Time: Tue Dec 06 15:50:48 CST 2016

Total time taken to generate the page: 0.12367 seconds