Home » SQL & PL/SQL » SQL & PL/SQL » TO_NUMBER conversion on NVARCHAR2 column throws ORA-01722: invalid number (12c, 12.1.0.1, Windows 7)
TO_NUMBER conversion on NVARCHAR2 column throws ORA-01722: invalid number [message #623855] Mon, 15 September 2014 08:34 Go to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hi Folks,

The issue is that there is column of NVARCHAR2 datatype which has numbers stored(with or without decimal part). I suggested the respective team to change the data type to number, however, it is a legacy system so they cannot change the data type as of now, unless I come up with a permanent fix.

When TO_NUMBER is applied on the NVARCHAR2 column with required number format and nls numeric characters, it throws ORA-01722: invalid number :

Test case :

SQL> SELECT banner
  2  FROM   v$version
  3  WHERE  ROWNUM = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL>
SQL> DROP TABLE t purge;

Table dropped.

SQL>
SQL> CREATE TABLE t
  2    (
  3       col NVARCHAR2(100)
  4    );

Table created.

SQL>
SQL> INSERT INTO t
  2  VALUES     ('0.5');

1 row created.

SQL>
SQL> INSERT INTO t
  2  VALUES     ('1.0');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT To_number(col, '999D99', 'NLS_NUMERIC_CHARACTERS = ,.') val
  2  FROM   t
  3  /
ERROR:
ORA-01722: invalid number



no rows selected


What I found after reserching on the data is that, it only happens when we have pure decimal number(such as 0.5) in the NVARCHAR2 column. If I filter them out, the query is executed successfully.

SQL> SELECT To_number(col, '999D99', 'NLS_NUMERIC_CHARACTERS = ,.') val
  2  FROM   t
  3  WHERE  Substr(col, 1, 2) <> '0.'
  4  /

       VAL
----------
        10

SQL>


And interesting thing is, if I modify the datatype from NVARCHAR2 to VARCHAR2, the query works perfectly.

SQL> DROP TABLE t purge;

Table dropped.

SQL>
SQL> CREATE TABLE t
  2    (
  3       col VARCHAR2(100)
  4    );

Table created.

SQL>
SQL> INSERT INTO t
  2  VALUES     ('0.5');

1 row created.

SQL>
SQL> INSERT INTO t
  2  VALUES     ('1.0');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT To_number(col, '999D99', 'NLS_NUMERIC_CHARACTERS = ,.') val
  2  FROM   t
  3  /

       VAL
----------
         5
        10


So, as a workaround, I suggested the developers to apply TO_CHAR before applying TO_NUMBER :

SQL> DROP TABLE t purge;

Table dropped.

SQL>
SQL> CREATE TABLE t
  2    (
  3       col VARCHAR2(100)
  4    );

Table created.

SQL>
SQL> INSERT INTO t
  2  VALUES     ('0.5');

1 row created.

SQL>
SQL> INSERT INTO t
  2  VALUES     ('1.0');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT To_number(To_char(col), '999D99', 'NLS_NUMERIC_CHARACTERS = ,.') val
  2  FROM   t
  3  /

       VAL
----------
         5
        10


I tried searching MOS, but got a Bug ID which talks just the opposite of my issue, Bug 13794591. Has any of you come across such an issue Or any idea what's going on? Any help/suggestion is welcome.

The create and insert statements to replicate :

DROP TABLE t purge; 

CREATE TABLE t 
  ( 
     col VARCHAR2(100) 
  ); 

INSERT INTO t 
VALUES     ('0.5'); 

INSERT INTO t 
VALUES     ('1.0'); 

COMMIT; 

SELECT To_number(To_char(col), '999D99', 'NLS_NUMERIC_CHARACTERS = ,.') val 
FROM   t 
WHERE  Substr(col, 1, 2) <> '0.';


Regards,
Lalit

Report message to a moderator

Re: TO_NUMBER conversion on NVARCHAR2 column throws ORA-01722: invalid number [message #623861 is a reply to message #623855] Mon, 15 September 2014 10:00 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
 
orclz>
orclz> SELECT To_number(col, '999D99', 'NLS_NUMERIC_CHARACTERS = ,.') val FROM   t;
ERROR:
ORA-01722: invalid number



no rows selected

orclz> SELECT To_number(col, '999D99', 'NLS_NUMERIC_CHARACTERS = .,') val FROM   t;

       VAL
----------
        .5
        10

orclz>
Any good?

Report message to a moderator

Re: TO_NUMBER conversion on NVARCHAR2 column throws ORA-01722: invalid number [message #623863 is a reply to message #623861] Mon, 15 September 2014 10:18 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks John for the reply.

I believe I tried that already before posting, however, didn't mention it, sorry. But, I might be wrong, so I would feedback tomorrow when I will access the particular database to validate.

Edit : I know you are on 12c as I see your recent posts, but just confirming if you did it on 12c. My test case is on 12c initial release(please don't get annoyed).

[Updated on: Mon, 15 September 2014 10:25]

Report message to a moderator

Re: TO_NUMBER conversion on NVARCHAR2 column throws ORA-01722: invalid number [message #623892 is a reply to message #623863] Tue, 16 September 2014 00:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I followed your suggestion, doesn't seem to work in general when I add more values(though it worked in your test case with just two values) :

SQL> INSERT INTO t
  2  VALUES     ('1.5');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL> select * from t
  2  /

COL
---------
0.5
1.0
1.5

SQL>    SELECT To_number(col, '999D99', 'NLS_NUMERIC_CHARACTERS = .,')
  2  FROM   t
  3  /
ERROR:
ORA-01722: invalid number



no rows selected


If I filter out '0.5', it executes without throwing error, but the result is incorrect since it returns 15 instead of 1.5 :

SQL> SELECT To_number(col, '999D99', 'NLS_NUMERIC_CHARACTERS = .,') val
  2  FROM   t
  3  WHERE  Substr(col, 1, 2) <> '0.'
  4  /

       VAL
----------
         1
        15


If I use VARCHAR2 instead of NVARCHAR2, this is the result :

SQL> CREATE TABLE t
  2    (
  3       col VARCHAR2(100)
  4    );

Table created.

SQL>
SQL> INSERT INTO t
  2  VALUES     ('0.5');

1 row created.

SQL>
SQL> INSERT INTO t
  2  VALUES     ('1.0');

1 row created.

SQL>
SQL> INSERT INTO t
  2  VALUES     ('1.5');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT To_number(col, '999D99', 'NLS_NUMERIC_CHARACTERS = .,') val
  2  FROM   t
  3  /

       VAL
----------
        .5
         1
       1.5


I guess, the safe workaround would be to use TO_CHAR before applying TO_NUMBER. I couldn't find anything else that would work in general.

Thank you for your time.

[Updated on: Tue, 16 September 2014 00:27]

Report message to a moderator

Re: TO_NUMBER conversion on NVARCHAR2 column throws ORA-01722: invalid number [message #624085 is a reply to message #623892] Wed, 17 September 2014 10:51 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I have asked the dev team to raise SR, let's see the output. I know it is not going to be quick turnaround. Also, as I already tried, internal service requests are not entertained by managers unless if it is really required Sad

Report message to a moderator

Previous Topic: find value in table (don't know column name yet)
Next Topic: How to find total count based on date
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 04:42:18 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.