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

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

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

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