ORA-01722: invalid number when loading number fields trough external tables. (Doc ID 268906.1)

Last updated on DECEMBER 05, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.2.0.3 - Release: 9.0.1 to 11.2
Information in this document applies to any platform.

Symptoms

Receiving the error:

ORA-01722: invalid number when loading number fields trough external tables

Example:
--------
You try to load a file trough a external table.  The file contains number fields that contain data with comma as the decimal separator (like 1.234,99).
The test.txt contains:
name1    234,7    4356
name2    500,     32
(tab delimited file)
This is a sample CREATE TABLE statement:
  CREATE TABLE exttable
        (
        field1                     VARCHAR2(255),
        field2                     NUMBER,  -- Field with comma format number
        field3                     NUMBER )
      ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
       DEFAULT DIRECTORY TESTDIR
        ACCESS PARAMETERS(
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY '    ' -- this is a TAB
        MISSING FIELD VALUES ARE NULL
       )
       LOCATION ('test.txt')
     )
    /
       
but you always run into "ORA-01722: invalid number" for the numbers with a comma.

 

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