My Oracle Support Banner

ORA-1722 SQL*Loader Does Not Handle Negative Values When Using TO_NUMBER Function (Doc ID 815516.1)

Last updated on MARCH 07, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud 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
Information in this document applies to any platform.

NOTE: 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.

Symptoms

SQL*Loader (sqlldr) appears to handle negative values incorrectly when using TO_NUMBER function.

Example: "to_number( :c2, '9999D99MI' )"

When the number to be import doesn't contain a minus character at the end of character string such as "99.00 " the row will not be loaded and ORA-1722 is reported in logfile.

Character strings which contain a minus character, such as "1100.00-" will be exported correctly.

BEGINDATA
a 1100.00-
b 99.00 <== Note: the string is ended by blank character (0x20).

The TO_NUMBER Format Model

TO_NUMBER function is expected to translate a value of CHAR or VARCHAR2 datatype to NUMBER datatype as shown by the example:

SQL> select to_number('99.00 ','9999D99MI') from dual;

TO_NUMBER('99.00','9999D99MI')
------------------------------
99

It appears that the the blank space (0x20) is filtered by sqldr during data load which then would result in ORA-1722.

SQL> select to_number('99.00','9999D99MI') from dual;
select to_number('99.00','9999D99MI') from dual
*
ERROR at line 1:
ORA-01722: invalid number

SQLLOADER TEST CASE

TABLE DEFINITION
----------------
create table tc.test_mi_num_format (
   c1 char(1)
  ,c2 numeric( 6,2 ));

SQL> desc tc.test_mi_num_format
Name                 Null? Type
----------------- -------- ------------
C1                         CHAR(1)
C2                         NUMBER(6,2)

CONTROL FILE
------------
LOAD DATA characterset WE8MSWIN1252
INFILE *
INTO TABLE test_mi_num_format
TRUNCATE
--FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
 c1 position( 1:1 )
,c2 position( 2:10) "to_number( :c2, '9999D99MI' )"
)
BEGINDATA
a 1100.00-
b 99.00 <== Note: the string is ended by blank character (0x20).

Note:
-----
Hex dump of controlfile (ldr.ctl) clearly confirms character string is terminated by blank value (0x20)

00000000h: 4C 4F 41 44 20 44 41 54 41 20 63 68 61 72 61 63 ; LOAD DATA charac
00000010h: 74 65 72 73 65 74 20 57 45 38 4D 53 57 49 4E 31 ; terset WE8MSWIN1
00000020h: 32 35 32 0D 0A 49 4E 46 49 4C 45 20 2A 0D 0A 49 ; 252..INFILE *..I
00000030h: 4E 54 4F 20 54 41 42 4C 45 20 74 65 73 74 5F 6D ; NTO TABLE test_m
00000040h: 69 5F 6E 75 6D 5F 66 6F 72 6D 61 74 0D 0A 54 52 ; i_num_format..TR
00000050h: 55 4E 43 41 54 45 0D 0A 2D 2D 46 49 45 4C 44 53 ; UNCATE..--FIELDS
00000060h: 20 54 45 52 4D 49 4E 41 54 45 44 20 42 59 20 27 ; TERMINATED BY '
00000070h: 3B 27 20 4F 50 54 49 4F 4E 41 4C 4C 59 20 45 4E ; ;' OPTIONALLY EN
00000080h: 43 4C 4F 53 45 44 20 42 59 20 27 22 27 0D 0A 54 ; CLOSED BY '"'..T
00000090h: 52 41 49 4C 49 4E 47 20 4E 55 4C 4C 43 4F 4C 53 ; RAILING NULLCOLS
000000a0h: 20 28 0D 0A 20 20 63 31 20 20 20 20 20 20 20 20 ; (.. c1
000000b0h: 20 20 70 6F 73 69 74 69 6F 6E 28 20 31 3A 31 20 ; position( 1:1
000000c0h: 29 0D 0A 2C 63 32 20 20 20 20 20 20 20 20 20 70 ; )..,c2 p
000000d0h: 6F 73 69 74 69 6F 6E 28 20 32 3A 31 30 29 20 22 ; osition( 2:10) "
000000e0h: 74 6F 5F 6E 75 6D 62 65 72 28 20 3A 63 32 2C 20 ; to_number( :c2,
000000f0h: 27 39 39 39 39 44 39 39 4D 49 27 20 29 22 0D 0A ; '9999D99MI' )"..
00000100h: 29 0D 0A 42 45 47 49 4E 44 41 54 41 0D 0A 61 20 ; )..BEGINDATA..a
00000110h: 31 31 30 30 2E 30 30 2D 0D 0A 62 20 20 20 39 39 ; 1100.00-..b 99
00000120h: 2E 30 30 20                                     ; .00 
                    --
                    Blank character.

LOGFILE - ldr.ctl
------------------
SQL*Loader: Release 10.2.0.4.0 - Production on Fri Apr 24 06:26:10 2009

... <lines truncated> ...

Table TEST_MI_NUM_FORMAT, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

Column Name                     Position   Len  Term Encl  Datatype
------------------------------ ---------- ----- ---- ---- -----------
C1                                    1:1     1             CHARACTER
C2                                   2:10     9             CHARACTER

SQL string for column : "to_number( :c2, '9999D99MI' )"

Record 2: Rejected - Error on table TEST_MI_NUM_FORMAT, column C2.
ORA-01722: invalid number

... <lines truncated> ...

Changes

 

Cause

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
Symptoms
 SQLLOADER TEST CASE
Changes
Cause
Solution
References


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