Re: Oracle 8 updates from flat files

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 15 Dec 1998 16:06:05 GMT
Message-ID: <367e8657.79206002@192.86.155.100>

A copy of this was sent to Greg Drew <gdrew_at_computer.org> (if that email address didn't require changing) On Tue, 15 Dec 1998 10:12:32 -0500, you wrote:

>I have an Oracle8 database which needs to be periodically updated with
>records pulled from an ASCII flat file provided by an external source.
>I've about gone blind perusing the SQL*Loader documentation, and can
>only find ways to append new records. Unfortunately, the file contains
>both new records and updates to existing ones. I can of course write
>code to parse the data and then query the DB to find out if the record
>exists, and then insert or update as appropriate, but my intuition tells
>me that there must be a better way. Does anyone have any ideas?
>--GDD
There are no utilies that do an "update or, if no records updated -- delete" type of processing. Its pretty easy to accomplish tho.

One thing that works would be as follows:

use sqlldr to load the flat file into a table that is structurally similar to the one you want to update or insert into. for example, if your table is:

create table t ( x int primary key, y varchar2(255), z date );

you would have another table

create table t_tmp( x int primary key, y varchar2(255), z date );

run the following sql statements to process the updates:

update t

   set ( y, z ) = ( select t_tmp.y, t_tmp.z
                      from t_tmp
                     where t_tmp.x = t.x )

where t.x in ( select t_tmp.x from t_tmp ) /

insert into t
select * from t_tmp
where not exists ( select NULL from t where t.x = t_tmp.x ) /

delete from t_tmp
/

That effectively

updates existing rows (rows where t.x exist already)
inserts rows from t_tmp into t when the t_tmp.x value did not exist already
cleans up the temp table.

Alternatively, you could write a trigger on t_tmp that looked something like:

create or replace trigger t1_trigg
after insert on t_tmp
for each row
begin

insert into t values ( :new.x, :new.y, :new.z ); exception

when dup_val_on_index then

      update t set y = :new.y, z = :new.z
       where x = :new.x;

end;
/
show errors

create or replace trigger t2_trigg
after insert on t_tmp
begin

delete from t_tmp;
end;
/

That way, when sqlldr loads into t-tmp, it will try to insert that row into t and if it fails cause the row already exists, it will update that existing row. the after trigger simply keeps t_tmp 'empty' all of the time (after the insert -- there will be zero rows in t_tmp)

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Dec 15 1998 - 10:06:05 CST

Re: Oracle 8 updates from flat files - how