Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL*Loader problem

Re: SQL*Loader problem

From: damorgan <damorgan_at_exesolutions.com>
Date: Fri, 06 Dec 2002 17:13:25 GMT
Message-ID: <3DF0DAB2.50EBF1AF@exesolutions.com>

Charlie Edwards wrote:

> Hello - Long time no post!
>
> I've got a little problem with SQL*Loader.
>
> I'm trying to load records into a table (of course!). For each file I
> load, I want to create a record in the "PAYMENT_FILES" table. So what
> I did was to create an index on the "PAYMENT_RECORDS" table like this:
>
> CREATE OR REPLACE TRIGGER pr_trg_1
> BEFORE INSERT
> ON payment_records
> FOR EACH ROW
> DECLARE
> no_parent EXCEPTION;
> PRAGMA EXCEPTION_INIT(no_parent, -8002);
> BEGIN
> SELECT pf_sequence.currval
> INTO :new.pf_id
> FROM dual;
> EXCEPTION
> WHEN no_parent THEN
> INSERT INTO payment_files
> (pf_id
> file_loaded,
> status_ind)
> VALUES (pf_sequence.currval,
> SYSDATE,
> 'BL',
> 'N');
> SELECT pf_sequence.currval
> INTO :new.pf_id
> FROM dual;
> END;
> /
>
> This is to create a parent record for the first payment record loaded.
>
> Now this works just fine unless we have an Oracle error (say data too
> big for a column). Then it gets inconsistent. If the error hits
> before the first SQL*Loader commit, then SQL*Loader exits with:
>
> SQL*Loader-704: Internal error: ulnain: error occurred on good insert
> [-1]
>
> Everything gets rolled back.
>
> However, if the first error appears after the first commit, then
> SQL*Loader continues to the end, loading all valid records and copying
> the bad ones to a .bad file.
>
> Now I don't really care which one of the above happens - I just want
> it to be consistent.
>
> Anyone got any ideas???
>
> TIA
>
> CE

You wrote: "For each file I load" but you wrote a FOR EACH ROW trigger rather than a statement level trigger? The trigger you wrote is going to fire once for every row inserted. Is that what you want?.

I would suggest, in the interest of efficiency, that whatever is running SQL*Loader just do an insert into your payment_files table after it is done. That can be easily done in a number of ways depending on your environment.

Daniel Morgan Received on Fri Dec 06 2002 - 11:13:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US