SQL*Loader Reports ORA-1722: "INVALID NUMBER" For Wrong Column

(Doc ID 2047931.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

Symptoms

This document will show that it is possible for SQL*LOADER to report an error ORA-1722 on a simple data set that contains an invalid number. The error message as such is correct but we will show that it is possible to see the error being reported on the wrong column.
The following steps should only be done on a TEST database if wished to see first hand as it requires change of a database parameter and we also need to stop/start the database a number of times.

The data, tables and SQL*LOADER controlfile are defined as follows:

File 'data.txt' containing the data to be loaded:

20150721 11.11.11|0.00|YY


SQL script 'tables.sql' for creating the table:

 

Cause

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