Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> sqlldr is ignoring ERRORS option

sqlldr is ignoring ERRORS option

From: Naren <narendra.sharma_at_gmail.com>
Date: 27 Feb 2006 07:36:41 -0800
Message-ID: <1141054601.444875.269000@z34g2000cwc.googlegroups.com> 





Hi,



I am using sqlldr to load one data file into multiple tables. The
oracle version is 9i, sqlldr version is 9.2.0.1.0. My data file has 379
records. The control file looks like following:




LOAD DATA



APPEND



INTO TABLE table1


WHEN recordtype = 'V'


FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(

	recordtype  FILLER  position(1),
	f1,
	f2,
	f3,
	f4,
	f5,
	f6,
	f7,
	f8 "to_date(:f8, 'MM/DD/YYYY')"




)


INTO TABLE table2


WHEN recordtype = 'H'


FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(

	recordtype  FILLER position(1),
	f1,
	f2,
	f3,
	f4,
	f5 LOBFILE (f4) TERMINATED BY EOF,
	f6 "to_date(:f6, 'MM/DD/YYYY')"




)


INTO TABLE table3


WHEN recordtype = 'C'


FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(

	recordtype  FILLER position(1),
	f1,
	f2,
	f3,
	f4,
	f5,
	f6,
	f7,
	f8,
	f9,
	f10 "to_date(:f10, 'MM/DD/YYYY')"




)


INTO TABLE table4


WHEN recordtype = 'CV'


FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(

	recordtype  FILLER position(1),
	f1,
	f2,
	f3,
	f4,
	f5,
	f6,
	f7,
	f8 "to_date(:f8, 'MM/DD/YYYY')",
	f9 "to_date(:f9, 'MM/DD/YYYY')"




)




The command I use to upload the data is




sqlldr user/pass control=file.ctl data=file.dat bad=file.bad
discard=file.dis ROWS=500 BINDSIZE=4644000 READSIZE=4644000
log=file.log errors=1




The data file contains records that violate referential integrity
constraints. What I expect is that the load should abort as soon as
first error is encountered, and because all the rows in the data file
can fit in the BindArray, none of the rows should get committed.
Instead, the load doesn't abort and it loads the data in one of the
tables and also report the errors in the log file. Following is the log
file:





SQL*Loader: Release 9.2.0.1.0 - Production on Mon Feb 27 17:54:31 2006



Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Control File:   file.ctl
Data File:      file.dat
  Bad File:     file.bad
  Discard File: file.dis




 (Allow all discards)


Number to load: ALL
Number to skip: 0
Errors allowed: 2
Bind array:     500 rows, maximum of 4644000 bytes
Continuation:    none specified
Path used:      Conventional





Table table1, loaded when RECORDTYPE = 0X56(character 'V')
Insert option in effect for this table: APPEND


   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
RECORDTYPE                              1     *   ,  O(") CHARACTER

  (FILLER FIELD)
f1                             NEXT     *   ,  O(") CHARACTER

f2                       NEXT     *   ,  O(") CHARACTER
f3                     NEXT     *   ,  O(") CHARACTER
f4                      NEXT     *   ,  O(") CHARACTER
f5                           NEXT     *   ,  O(") CHARACTER
f6                NEXT     *   ,  O(") CHARACTER
f7                      NEXT     *   ,  O(") CHARACTER
f8                           NEXT     *   ,  O(") CHARACTER




    SQL string for column : "to_date(:f8, 'MM/DD/YYYY')"



Table table2, loaded when RECORDTYPE = 0X48(character 'H')
Insert option in effect for this table: APPEND


   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
RECORDTYPE                              1     *   ,  O(") CHARACTER

  (FILLER FIELD)
f1                            NEXT     *   ,  O(") CHARACTER

f2                                NEXT     *   ,  O(") CHARACTER

f3                           NEXT     *   ,  O(") CHARACTER
f4                            NEXT     *   ,  O(") CHARACTER

f5                        DERIVED     *  EOF      CHARACTER
    Dynamic LOBFILE.  Filename in field f4
f6                           NEXT     *   ,  O(") CHARACTER




    SQL string for column : "to_date(:f6, 'MM/DD/YYYY')"



Table table3, loaded when RECORDTYPE = 0X43(character 'C')
Insert option in effect for this table: APPEND


   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
RECORDTYPE                              1     *   ,  O(") CHARACTER

  (FILLER FIELD)
f1                           NEXT     *   ,  O(") CHARACTER
f2                         NEXT     *   ,  O(") CHARACTER
f3                         NEXT     *   ,  O(") CHARACTER
f4                        NEXT     *   ,  O(") CHARACTER
f5                     NEXT     *   ,  O(") CHARACTER
f6                   NEXT     *   ,  O(") CHARACTER
f7                         NEXT     *   ,  O(") CHARACTER
f8                        NEXT     *   ,  O(") CHARACTER
f9                          NEXT     *   ,  O(") CHARACTER
f10                           NEXT     *   ,  O(") CHARACTER





    SQL string for column : "to_date(:f10, 'MM/DD/YYYY')"



Table table4, loaded when RECORDTYPE = 0X4356(character 'CV')
Insert option in effect for this table: APPEND


   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
RECORDTYPE                              1     *   ,  O(") CHARACTER

  (FILLER FIELD)
f1                           NEXT     *   ,  O(") CHARACTER
f2                             NEXT     *   ,  O(") CHARACTER

f3                    NEXT     *   ,  O(") CHARACTER
f4                      NEXT     *   ,  O(") CHARACTER
f5                            NEXT     *   ,  O(") CHARACTER

f6                               NEXT     *   ,  O(") CHARACTER

f7                      NEXT     *   ,  O(") CHARACTER
f8                         NEXT     *   ,  O(") CHARACTER
    SQL string for column : "to_date(:f8, 'MM/DD/YYYY')"
f9                           NEXT     *   ,  O(") CHARACTER




    SQL string for column : "to_date(:f9, 'MM/DD/YYYY')"



Record 1: Rejected - Error on table table1.
ORA-00001: unique constraint (user.PK1) violated



Record 2: Rejected - Error on table table2.
ORA-00001: unique constraint (user.PK2) violated



/*......



other errors


......*/



MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.



Table table1:


  0 Rows successfully loaded.


  1 Row not loaded due to data errors.


  378 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.




Table table2:


  0 Rows successfully loaded.


  0 Rows not loaded due to data errors.


  379 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.




Table table3:


  0 Rows successfully loaded.


  189 Rows not loaded due to data errors.
  190 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.




Table table4:


  189 Rows successfully loaded.


  0 Rows not loaded due to data errors.


  190 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.



Space allocated for bind array:                4644000 bytes(500 rows)


Read   buffer bytes: 4644000


Total logical records skipped:          0
Total logical records read:           379
Total logical records rejected:       190
Total logical records discarded:        0





Run began on Mon Feb 27 17:54:31 2006


Run ended on Mon Feb 27 17:54:32 2006


Elapsed time was:     00:00:00.66
CPU time was:         00:00:00.40


==============================================================






As can be seen from the log, 189 rows were successfully loaded in
table4. The records in the data file are interleaved i.e. in following
form:

"V", "13123", ............
"C", "234234",.....
"CV", "234324"......
"C", "234234".....
"CV", "6867867"..





Any help on this topic will be highly appreicated.



TIA,



Naren
Received on Mon Feb 27 2006 - 09:36:41 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US