Re: How to load ascii file in oracle table

From: Richard Kuhler <noone_at_nowhere.com>
Date: Thu, 01 Nov 2001 16:37:31 GMT
Message-ID: <fTeE7.13922$D5.3555306@typhoon.san.rr.com>

There's an easier way to achieve this if you're using 8i or above. Here's the trick I use:

1. create a view on the table you want to 'merge' into:

create view customer_merge as select * from customer;

2. create an 'instead of insert' trigger on that view to do the merge:

create trigger trg_customer_merge_ioi
instead of insert on customer_merge
begin

  update customer set
    set name = :new.name
    ...
  where cust_id = :new.cust_id;

  if sql%rowcount = 0 then
    insert into customer (

      name


      ...

    ) values (
      :new.name


      ...

    );
  end if;

end;

3. Now just use sql*loader to load into the view and it should work just like you want. This way is much more efficient and the trigger is simple to write. In fact, I just wrote a simple sql script to automatically build a trigger given a table name.

Note: if you have a primary key on cust_id and you expect to do mostly inserts instead of updates, you might want to reverse the update and insert in the trigger. You'll have to catch the dup_val_on_index exception then do the insert. In addition, you still need to check that the update actually updated something and throw an error if it didn't. That way you'll get see the problem in your sql*loader log. This should really only happen if you have other unique indexes that might cause an exception without the customer actually being there.

Richard Received on Thu Nov 01 2001 - 10:37:31 CST