Re: Is there a way to load records rejected by SQL Loader in a table ?

> | I've done exactly that before, seems it was fairly easy between the
> | input file and the error file to get it all together. Each failed
> | input record is identified in the error file. It's just more
> | processing.
> |
> |
> | What have you tried so far??
> | ed.
>
> except the OP wants the error messages
>
> what's really needed is a staging table that the rejects can be loaded into
> and examined (manually or programmatically, perhaps by attempting to
> enabling constraints after the load and saving the errors)-- the assumption
> would be that none of the reject reasons would prevent the record from being
> loaded to the staging table
>
> a similar approach would be to not use SQL*Loader, but use an external table
> as the staging table -- although it could not have constraints so the
> 'attempt to enable constraints and save errors' could not be used)
>
> ++ mcs

Thanks a lot for all the responses.

But I think I should have elaborated my problem a bit more.

I have a comma separated data file and 6 oracle tables. What I am doing is a case of loading data from one data file into multiple tables using WHEN clause. The tables do not have any constraints and are just used to get the records from datafile. So I am not looking for any constraint errors. I want to catch only the field validation error (like value too large for column)

After loading the data I get the discard file, bad file and the log file.
I get all the information that I want in teh log file, like

1. The Oracle error code,
2. The Oracle error message,
3. The table and the column on which the error occured
4. The position of the record in the datafile

Now, I want to load all that inforamtion in a table. One way I can think of is to parse the log file. Is there any other way I can do it ?
Is there any SQL Loader syntax that will help me catch the exception in control file and let me write some code for the exception ? If not, then is there any work around like calling an oracle procedure when the insert fails ?

I tried to get the info from documentation, but did not find anything suitable.

Thanks for your time,
Gauri Received on Tue Oct 19 2004 - 11:16:37 CEST