Re: SQL*Loader problem

From: damorgan <damorgan_at_exesolutions.com>
Date: Mon, 09 Dec 2002 17:14:29 GMT
Message-ID: <3DF4CF6E.AD0B508@exesolutions.com>

Charlie Edwards wrote:

> 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

>

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

>

> 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.

>

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

>

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

>
> CE

If it is "hundreds of records" I'd throw SQL*Loader away and just write a small procedure with UTL_FILE. There is nothing SQL*Loader can do that UTL_FILE can't do better except through-put.

Daniel Morgan Received on Mon Dec 09 2002 - 11:14:29 CST