Somehow the maximum errors allowed in sql loader is 50 (default). How do I increase this to unlimited.
If you see the sample log file below it has allow all discards set. The loading stopts once it reaches 50 records in the bad file. I want it to continue and keep logging as many error in the bad file.
SQL*Loader: Release 188.8.131.52.0 - Production on Tue May 29 17:30:28 2001
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Control File: loaddata.ctl
Data File: ofasweb.txt
Bad File: ofasweb.bad
Discard File: ofasweb.dcs
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional
Use ERRORS=N in your sqlldr command line or parameter file, where N is an arbitrary large number. I don't know if there is any upper limit for this parameter. As you've allready found ot, the default is 50.
I agree with jmordic.
I have set the ERRORS as high as 25,000.
i did a lot in data conversion from legacy systems into oracle apps. the resone why i'm telling you this is that when i read your thred i think: why do you need so many errors? i think that you should look at your data and clean it befor you start to load. i think that too many errors in the sqlloader stage is a sign of bad data. better take care of it befor you start inserting data useing sqlloader, lok at the logic of the data creation. and, if i'm wrong in my argument, why not use the power of the sqlloader to clean the data first and then end up with errors that are importent and help you learn about potential problems in that data.
Jmodic is rite...When u issue ur sqlldr command also give this ERROR=n at the command line for specifiying ur required number.
I too did a lot of data transfer from Unysis MF datasets to oracle tables after importing them from Unysis. There were instances like loading in millions of records where some corrupt records tend to get in between and if we have to sort them, then we have to do the entire process rite from the begining viz, import it from dataset, check them manually to confirm the absence of corrupt records and therby the data feed in has to b stopped for time being....
Well..in this case, we can alone get to a stand where we will b aware what r all the records that has got corrupt data and we can minimise our effort while doing the reqd operattion there by the work around won't b tampered as well...
Thanks a lot for the help.
shawish_sababa, the reason I need to allow high errors is beuase it will be a batch process every week which will load 6-7 million records. Manually cleaning those huge files every week is virtually impossible, instead cleaning of the rejected records and feeding them is a better idea.