Last updated on NOVEMBER 08, 2011
Applies to:
Oracle Server - Enterprise Edition - Version: 11.2.0.1Information in this document applies to any platform.
Goal
When performing SQL*Loader convention path loads the error ORA-1722 reports wrong column.The issue can occur with database 11.2.0.1 and SQL*Loader clients releases 10.2.0.4 and 11.2.0.1:
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Aug 25 14:57:41 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: KAE_TX.CTL
Data File: KAE_TX.EXT
Bad File: KAE_TX.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 100000
Bind array: 50000 rows, maximum of 9999999 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK
Table KAE_TX, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
---------------------- ---------- ----- ---- ---- ---------------------
TX_ID FIRST * WHT CHARACTER
LINE NEXT * WHT CHARACTER
CM_PHY_OWNER_ID NEXT * WHT CHARACTER
CM_LOG_OWNER_ID NEXT * WHT CHARACTER
MTCH_TX_HX_DT NEXT * WHT DATE MM/DD/YYYY
MTCH_TX_HX_ID NEXT * WHT CHARACTER
MTCH_TX_HX_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_INS_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_PAT_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_COMMENT NEXT 370 WHT CHARACTER
MTCH_TX_HX_UN_DT NEXT * WHT DATE MM/DD/YYYY
MTCH_TX_HX_D_CVG_ID NEXT * WHT CHARACTER
MTCH_TX_HX_DSUSR_ID NEXT * WHT CHARACTER
MTCH_TX_HX_DSSRC_C NEXT * WHT CHARACTER
MTCH_TX_HX_UDUSR_ID NEXT * WHT CHARACTER
MTCH_TX_HX_UDSRC_C NEXT * WHT CHARACTER
MTCH_TX_HX_INV_NUM NEXT * WHT CHARACTER
MTCH_TX_HX_BAT_NUM NEXT * WHT CHARACTER
MTCH_TX_HX_DIST_SEQ NEXT * WHT CHARACTER
MTCH_TX_HX_UND_SEQ NEXT * WHT CHARACTER
MTCH_TX_HX_UND_BAT NEXT * WHT CHARACTER
MTCH_TX_HX_REFUND NEXT 500 WHT CHARACTER
MTCH_TX_HX_UN_COM NEXT * WHT CHARACTER
MTCH_TX_HX_UN_CV_ID NEXT * WHT CHARACTER
MTCH_TX_HX_EX_M_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_EX_U_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_BD_M_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_BD_U_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_LINE NEXT * WHT CHARACTER
value used for ROWS parameter changed from 50000 to 1275
Record 1: Rejected - Error on table KAE_TX, column MTCH_TX_HX_DSSRC_C.
ORA-01722: invalid number
Record 2: Rejected - Error on table KAE_TX, column MTCH_TX_HX_DSSRC_C.
ORA-01722: invalid number
.....
Record 51: Rejected - Error on table KAE_TX, column MTCH_TX_HX_DSSRC_C.
ORA-01722: invalid number
Table KAE_TX:
0 Rows successfully loaded.
51 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 9996000 bytes(1275 rows)
Read buffer bytes: 9999999
Total logical records skipped: 0
Total logical records read: 51
Total logical records rejected: 51
Total logical records discarded: 0
Run began on Wed Aug 25 14:57:41 2010
Run ended on Wed Aug 25 14:57:43 2010
Elapsed time was: 00:00:01.93
CPU time was: 00:00:00.05
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: KAE_TX.CTL
Data File: KAE_TX.EXT
Bad File: KAE_TX.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 100000
Bind array: 50000 rows, maximum of 9999999 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK
Table KAE_TX, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
---------------------- ---------- ----- ---- ---- ---------------------
TX_ID FIRST * WHT CHARACTER
LINE NEXT * WHT CHARACTER
CM_PHY_OWNER_ID NEXT * WHT CHARACTER
CM_LOG_OWNER_ID NEXT * WHT CHARACTER
MTCH_TX_HX_DT NEXT * WHT DATE MM/DD/YYYY
MTCH_TX_HX_ID NEXT * WHT CHARACTER
MTCH_TX_HX_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_INS_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_PAT_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_COMMENT NEXT 370 WHT CHARACTER
MTCH_TX_HX_UN_DT NEXT * WHT DATE MM/DD/YYYY
MTCH_TX_HX_D_CVG_ID NEXT * WHT CHARACTER
MTCH_TX_HX_DSUSR_ID NEXT * WHT CHARACTER
MTCH_TX_HX_DSSRC_C NEXT * WHT CHARACTER
MTCH_TX_HX_UDUSR_ID NEXT * WHT CHARACTER
MTCH_TX_HX_UDSRC_C NEXT * WHT CHARACTER
MTCH_TX_HX_INV_NUM NEXT * WHT CHARACTER
MTCH_TX_HX_BAT_NUM NEXT * WHT CHARACTER
MTCH_TX_HX_DIST_SEQ NEXT * WHT CHARACTER
MTCH_TX_HX_UND_SEQ NEXT * WHT CHARACTER
MTCH_TX_HX_UND_BAT NEXT * WHT CHARACTER
MTCH_TX_HX_REFUND NEXT 500 WHT CHARACTER
MTCH_TX_HX_UN_COM NEXT * WHT CHARACTER
MTCH_TX_HX_UN_CV_ID NEXT * WHT CHARACTER
MTCH_TX_HX_EX_M_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_EX_U_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_BD_M_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_BD_U_AMT NEXT * WHT CHARACTER
MTCH_TX_HX_LINE NEXT * WHT CHARACTER
value used for ROWS parameter changed from 50000 to 1275
Record 1: Rejected - Error on table KAE_TX, column MTCH_TX_HX_DSSRC_C.
ORA-01722: invalid number
Record 2: Rejected - Error on table KAE_TX, column MTCH_TX_HX_DSSRC_C.
ORA-01722: invalid number
.....
Record 51: Rejected - Error on table KAE_TX, column MTCH_TX_HX_DSSRC_C.
ORA-01722: invalid number
Table KAE_TX:
0 Rows successfully loaded.
51 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 9996000 bytes(1275 rows)
Read buffer bytes: 9999999
Total logical records skipped: 0
Total logical records read: 51
Total logical records rejected: 51
Total logical records discarded: 0
Run began on Wed Aug 25 14:57:41 2010
Run ended on Wed Aug 25 14:57:43 2010
Elapsed time was: 00:00:01.93
CPU time was: 00:00:00.05
Solution
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms