My Oracle Support Banner

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 later
Oracle 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

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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.