SQL*Loader problem

From: Charlie Edwards <charlie3101_at_hotmail.com>
Date: 6 Dec 2002 06:37:40 -0800
Message-ID: <217ac5a8.0212060637.747e597@posting.google.com>

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 Received on Fri Dec 06 2002 - 08:37:40 CST