When Using SQL*Loader Convention Path Load The Error ORA-1722 Reports Wrong Column (Doc ID 1198313.1)

Last updated on NOVEMBER 08, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information 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

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