Re: SQL*Loader problem

damorgan <damorgan_at_exesolutions.com> wrote in message news:<3DF0DAB2.50EBF1AF_at_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

[Quoted] Well, efficiency isn't an issue here, there will be only hundreds of records loaded overnight.

[Quoted] I wrote it like this because 95% of the files we load have header records containing critical information. These are fixed record length. It's just the minority of files that are csv with no header records.

[Quoted] I want to guarantee that each record is linked to the correct header record, which is why I've chosen the above method.

[Quoted] I was thinking over the weekend - is there no way to tell SQL*Loader not to commit till the end????

CE Received on Mon Dec 09 2002 - 15:26:43 CET