Home » RDBMS Server » Server Administration » update data coming in flat file
update data coming in flat file [message #58049] Mon, 28 July 2003 15:04 Go to next message
sidd
Messages: 130
Registered: May 2003
Senior Member
I need to update the table with the data coming in flat file.. how do i do this.. they send the updates in flat files.. i don't think i can do using sqlloader.. is there a way? please
Re: update data coming in flat file [message #58052 is a reply to message #58049] Mon, 28 July 2003 17:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
you cannot update with sql*loader.
you can load the data into a similiar staging table using sql*loader.
and apply your update logic from the staging table to the target table.
drop the staging table.

Re: update data coming in flat file [message #58055 is a reply to message #58052] Tue, 29 July 2003 01:55 Go to previous messageGo to next message
psmyth
Messages: 81
Registered: October 2002
Member
i think you both need to have a look at the oracle docs on sql*loader... you can do all those things and more with sql*loader ;-)

Whether you would want to load the data directly into the main tables is another matter - it often makes sense to follow the procedure Mahesh posted - load the data into 'staging' tables and then run a transformation on that data. It really depends on what the data is and how the application works.

But read the Oracle docs... they rock ;-)
Re: update data coming in flat file [message #58063 is a reply to message #58055] Tue, 29 July 2003 06:28 Go to previous messageGo to next message
sidd
Messages: 130
Registered: May 2003
Senior Member
How about using UTL_FILE? i know i can use UTL_FILE, but do you guys have any example of it?
Re: update data coming in flat file [message #58065 is a reply to message #58063] Tue, 29 July 2003 06:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
you CANNOT UPDATE using sql*loader.
UTL_FILE is meant for other purposes.
to implement a logic using UTL_FILE would be tedious.
in your case,i tried this. it worked fine.

1. TABLE1 is target table.
2. TABLE2 is a staging table.
3. load into staging table using sql*loader.
4. Before loading,
   create an after insert trigger on staging table.
   in the trigger, construct your logic for updating
   the target table using the records inserted in staging table.

Re: update data coming in flat file [message #58078 is a reply to message #58065] Tue, 29 July 2003 21:58 Go to previous messageGo to next message
Srinivas
Messages: 138
Registered: June 2000
Senior Member
Great Mahesh....
Srinivas
Use external table and update directly [message #58099 is a reply to message #58078] Wed, 30 July 2003 10:57 Go to previous message
Mike
Messages: 417
Registered: September 1998
Senior Member
Hi,

If you are using Oracle9i you may use the new external table function. It's more or less the same as sql*loader but you can directly use it in your sql session and so directly query against it without first loading the data in a temp table. For more details have a look at the Oracle doc.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/tables.htm#15827

HTH
Mike
Previous Topic: Oracle Inventory/Financials Install
Next Topic: URGENT, Quick Help!!!!!!!!! DB link issue
Goto Forum:
  


Current Time: Fri Sep 20 06:47:17 CDT 2024