This content has been marked as final. Show 7 replies
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
Edited by: Alberto Faenza on Nov 21, 2012 5:39 PM
i think you should use the following syntax
sqlldr userid=<username/pwd> control=<script_name.ctl> log=<script_name>.log bad=<scriput_name>.bad data=<input_file_name.dat> discard=<script_file_name>.dsc errors=<script_name.err>
whenever the record did not match the control file requirement, it will make an entry in .bad and .dsc files.
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.
you have been clear and I have made a quick test. Unfortunately you cannot do such check in SQL*Loader as the WHEN clause in control file does not allow any OR.
Even if you add this check using a constraint in your table and specify the maximum number of errors to be 0, SQL*Loader will load the records up to that error.
Let me show you an example:
1) create the table with a constraint that for record_type 5 detail_line_number cannot be null.
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;
Let's prepare your input file:
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|
Here the control file I have used:
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
So if I check the table
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
This cannot be avoided as documented in SQL*Loader reference manual:
<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.
One alternative solution is to create an external table in Oracle and do all the checks you want before copying your external table into a database table, as BluShadow suggested.
We have a mechanism to truncate the table if the sql loader is aborted, so if it gets aborted due to the check constraint then the process of ours will allow us to truncate the table, remove the problematic record from the file and then rerun the procesS.
Also ill learn external tables and see if i can convert the existing sql loader process.