940838 wrote:You can check the possibility to use errors=0 if line number NULL is considered as an error by sqlldr
Unfortuntely this is all set since a decade., I've a new requirment so finding the solutions.
Any data inserted up that point, however, is committed.
sqlldr userid=user/pass control=yourfile.ctl errors=0
I think i am not clear in my requirement...
The question was how to abort the loader if the detail line number is not present in record type 5. It is however normal that detail line num is not mandatory for other record types. any insights.
In this table you will not be able to load rows having record_type=5 and detail_line_num NULL as this will be considered as an error.
CREATE TABLE test ( record_type INTEGER , batch_number VARCHAR2 (10) , batch_desc VARCHAR2 (10) , batch_date DATE , detail_line_num INTEGER , other VARCHAR2 (10) ); ALTER TABLE test ADD CONSTRAINT check_rec_5 CHECK ( record_type = 5 AND detail_line_num IS NOT NULL OR record_type != 5) ENABLE;
As you can see the input file has the fourth line with record_type = 5 and detail_line_num NULL. This will be an error for the constraint.
1|XXX123|XXX|20121121|| 4|XXX123|XXX||| 5|XXX123|XXX||1| 5|XXX123|XXX||| 5|XXX123|XXX||| 9|XXX123|XXX||| 1|XXX123|XXX|20121121|| 4|XXX123|XXX||| 5|XXX123|XXX||1| 5|XXX123|XXX||2| 5|XXX123|XXX||| 9|XXX123|XXX|||1|XXX123|XXX|20121121|| 4|XXX123|XXX||| 5|XXX123|XXX||1| 5|XXX123|XXX||2| 5|XXX123|XXX||| 9|XXX123|XXX|||1|XXX123|XXX|20121121|| 4|XXX123|XXX||| 5|XXX123|XXX||1| 5|XXX123|XXX||2|
If I try to execute the SQL*Loader and ask to stop at first error in this way:
--test.ctl load data INFILE 'test.dat' APPEND INTO TABLE test FIELDS TERMINATED BY '|' TRAILING NULLCOLS ( record_type , batch_number , batch_desc , batch_date Date 'YYYYMMDD', detail_line_num , other )
SQL*Loader will load only 3 records because it encounters an error at line 4 and having specified errors=0 will not continue to load. Actually the process will continue until it reach the commit point (100 rows in this case) but it will not load any record after the error nor continue to read the file.
sqlldr userid=yourname/yourpass@yourdb control=test.ctl errors=0 rows=100
You will see only records until you have reached the error.
SELECT * FROM test; RECORD_TYPE BATCH_NUMBER BATCH_DESC BATCH_DATE DETAIL_LINE_NUM OTHER ----------- ------------ ---------- --------------------- --------------- ---------- 1 XXX123 XXX 21-11-2012 00:00:00 4 XXX123 XXX 5 XXX123 XXX 1
<h3>Load Discontinued Because Maximum Number of Errors Exceeded</h3>As you can see SQL*Loader abort the processing but it will anyway commit the records until that error.
If the maximum number of errors is exceeded, SQL*Loader stops loading records into any table and the work done to that point is committed.