SQL*Loader Reports ORA-1722: "INVALID NUMBER" for Wrong Column
(Doc ID 2047931.1)
Last updated on APRIL 03, 2020
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 to 18.104.22.168 [Release 11.2]
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 Backup Service - Version N/A and later
Information in this document applies to any platform.
In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
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:
SQL script 'tables.sql' for creating the table:
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