Re: How to load ascii file in oracle table

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<fTeE7.13922$D5.3555306_at_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

Thank you Richard

Unfortunatly I am under Oracle 7.3 :( Received on Thu Nov 08 2001 - 16:27:39 CST