Home » SQL & PL/SQL » SQL & PL/SQL » "ORA-01722: invalid number" errors after upgrade 12c (Oracle Database 12c (12.1.0.2.0))
"ORA-01722: invalid number" errors after upgrade 12c [message #648526] Wed, 24 February 2016 11:20 Go to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
We've just gone from Oracle 10 to 12c (12.1.0.2.0) and are getting lots of "ORA-01722: invalid number" errors which didn't happen before.

Any table with a varchar2 column being compared to a number is a problem.

For example:

select to_number(a_varachar2_col) from tmp_table where a_varachar2_col = '5607xxx';

This worked ok under Oracle 10 (10.2.0.4.0) and simply returned 5607. Now it fails with the above ORA-01722.

This may well be a bug fix by Oracle or tighter use of datatypes but it's causing our code to fail now in multiple places. It's also incredibly difficult to search for as it affects all VARCHAR2 fields.

Is anyone aware of this problem and how to resolve it? Is it possible to revert back to the Ora10 functionality or turn off this feature?

Thanks for any help.
Re: "ORA-01722: invalid number" errors after upgrade 12c [message #648527 is a reply to message #648526] Wed, 24 February 2016 11:25 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
How about writing your own function which strips off any bad characters before calling to_number? Then replace every to_number call.
Re: "ORA-01722: invalid number" errors after upgrade 12c [message #648528 is a reply to message #648527] Wed, 24 February 2016 11:38 Go to previous messageGo to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
It's not just to_number function. The following example also fails (when it hits a row with alpha and numeric in the varchar field):

select * from tmp_table WHERE a_varachar2_col NOT IN (30,31,33,34);

[Updated on: Wed, 24 February 2016 11:38]

Report message to a moderator

Re: "ORA-01722: invalid number" errors after upgrade 12c [message #648529 is a reply to message #648528] Wed, 24 February 2016 11:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I am certainly glad that application was tested under 12c before completing the migration the Production DB.
Re: "ORA-01722: invalid number" errors after upgrade 12c [message #648530 is a reply to message #648526] Wed, 24 February 2016 11:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Post a test case. I seriously doubt it was working in any version:

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table tmp_table
  2    as select '5607xxx' a_varachar2_col from dual;

Table created.

SQL> select to_number(a_varachar2_col) from tmp_table where a_varachar2_col = '5607xxx';
select to_number(a_varachar2_col) from tmp_table where a_varachar2_col = '5607xxx'
       *
ERROR at line 1:
ORA-01722: invalid number


SQL> 


SY.

Re: "ORA-01722: invalid number" errors after upgrade 12c [message #648540 is a reply to message #648530] Thu, 25 February 2016 02:15 Go to previous messageGo to next message
varlamovvp
Messages: 13
Registered: February 2016
Location: Russia
Junior Member
Hi! You should check your NLS parameters. Maybe they differ.
Re: "ORA-01722: invalid number" errors after upgrade 12c [message #648574 is a reply to message #648540] Thu, 25 February 2016 12:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
I am not the OP. And anyway, I am not sure what NLS parameters you have in mind especially when OP never mentioned what result is expected. I guess we can ply a bit with NLS_NUMERIC_CHARACTERS, but it only brings us to something like:

SQL> alter session set nls_numeric_characters='xy';

Session altered.

SQL> select to_number('5607x') from dual;

TO_NUMBER('5607X')
------------------
              5607

SQL> select to_number('5607xx') from dual;
select to_number('5607xx') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number


SQL> 


SY.
Re: "ORA-01722: invalid number" errors after upgrade 12c [message #648598 is a reply to message #648574] Fri, 26 February 2016 02:41 Go to previous messageGo to next message
varlamovvp
Messages: 13
Registered: February 2016
Location: Russia
Junior Member
OK
for example
alter session set nls_numeric_characters=', ';
select to_number('560,7') from dual; -- 560,7
select to_number('560.7') from dual; -- ORA-01722: invalid number


alter session set nls_numeric_characters='. ';
select to_number('560,7') from dual; -- ORA-01722: invalid number
select to_number('560.7') from dual; -- 560.7



Re: "ORA-01722: invalid number" errors after upgrade 12c [message #648604 is a reply to message #648598] Fri, 26 February 2016 06:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And what does this have to do with OP's question?

select to_number(a_varachar2_col) from tmp_table where a_varachar2_col = '5607xxx';

This worked ok under Oracle 10 (10.2.0.4.0) and simply returned 5607.

SY.
Re: "ORA-01722: invalid number" errors after upgrade 12c [message #648708 is a reply to message #648604] Tue, 01 March 2016 08:01 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You must have had your own version of to_number that overrode the oracle function.

select to_number('5607xxx') from dual;

will fail in any version
Re: "ORA-01722: invalid number" errors after upgrade 12c [message #648715 is a reply to message #648708] Tue, 01 March 2016 11:15 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Bill,

I am not the OP.

SY.
Previous Topic: Select only One Case results
Next Topic: ORA-02292: integrity constraint error
Goto Forum:
  


Current Time: Thu Mar 28 08:03:43 CDT 2024