Xref: alice comp.databases.oracle.server:35965
Path: alice!news-feed.fnsi.net!WCG!Supernews73!supernews.com!su-news-hub1.bbnplanet.com!su-news-feed1.bbnplanet.com!news.gtei.net!inet16.us.oracle.com!not-for-mail
From: tkyte@us.oracle.com (Thomas Kyte)
Newsgroups: comp.databases.oracle.server
Subject: Re: Oracle 8 updates from flat files - how
Date: Tue, 15 Dec 1998 16:06:05 GMT
Organization: Oracle Service Industries
Lines: 98
Message-ID: <367e8657.79206002@192.86.155.100>
References: <36767C59.5FA109A1@computer.org>
Reply-To: tkyte@us.oracle.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Newsreader: Forte Agent 1.5/32.451

A copy of this was sent to Greg Drew <gdrew@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 

1) updates existing rows (rows where t.x exist already)
2) inserts rows from t_tmp into t when the t_tmp.x value did not exist already
3) 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@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.