When Using SQL*Loader Conventional Path Load The Error ORA-1722 Reports Wrong Column
(Doc ID 1198313.1)
Last updated on OCTOBER 08, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Goal
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
When performing SQL*Loader conventional 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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |