Re: dynamic error value in sql loader

Munish Gupta wrote:

> My problem is something like this. I deal with uploading of lots of
> txt files into the database via sql loader. My requirement is that if
> the error rate reaches certain % of the total number of rows of a
> file, sql loader should stop processing the file. The error option can
> be specified at 2 places.
> 1.) at the command where sql loader is run. But as i am running this
> command thru a batch file.. i am unable to pass it a dynamic value..
> 2.) in the control file within the OPTIONS parameter
>
> OPTIONS (ERRORS=80)
> LOAD FILE
> .......
>
> But its not allowing me to call a function in place of 80 and i can't
> declare a variable in the control file. ( if its possible to declare a
> variable in ctrl file place tell me )
>
> Please help.
> Thanks in advance

You can purchase some custom third-party party product apparently ... but why not do one of the following:

1. Blow 100% of all records into a staging table and then use a stored procedure to parcel them out to the final tables and mark each record as good or bad and why. The staging table should contain no constraints and oversize fields, all VARCHAR2.

    Not recommended for very large amounts of data.

2. If in 9i ... look at external tables.

    Also not recommended for very large amounts of data but otherwise     very functional.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)