Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722 Invalid Number for numeric values. (Oracle 12c)
ORA-01722 Invalid Number for numeric values. [message #685795] Fri, 25 March 2022 02:36 Go to next message
Dhritman
Messages: 4
Registered: September 2005
Junior Member
I have a table:

SQL> desc tab1;
Name          Type          Nullable Default Comments 
------------- ------------- -------- ------- -------- 
EMSID         NUMBER(9)                            
EMS_VAL        VARCHAR2(255)

EMS_VAL stores some alphanumeric values and some numeric values and this depends on EMSID (1 for numeric, 2 for alphanumeric)
Table data has several rows like this:

SQL> select emsid, ems_val from TAB1;

EMSID        EMS_VAL
------------ --------
1              198
2              F
But, this query returns ORA-01722 Invalid Number :

SQL> select to_number(ems_val) from tab1 where ems_id = 1;
select to_number(ems_val) from tab1 where ems_id = 1

ORA-01722: invalid number

SQL>
Even though we are using ems_id=1 which will only lead to numeric values in ems_val, we are getting the invalid number.

The same data is in multiple DBs but strangely, I am getting ORA-01722 Invalid number in only 1 DB and not in others.

Is there a solution to this ?

Please advise.

Thanks

Report message to a moderator

Re: ORA-01722 Invalid Number for numeric values. [message #685796 is a reply to message #685795] Fri, 25 March 2022 02:51 Go to previous messageGo to next message
John Watson
Messages: 8803
Registered: January 2010
Location: Global Village
Senior Member

Your queries are inconsistent. In some, there is a column EMSID, in others EMS_ID. You are not addressing the same table.

Report message to a moderator

Re: ORA-01722 Invalid Number for numeric values. [message #685797 is a reply to message #685796] Fri, 25 March 2022 02:59 Go to previous messageGo to next message
Dhritman
Messages: 4
Registered: September 2005
Junior Member
My apologies for the confusion. Here is the updated query:

SQL> select to_number(ems_val) from tab1 where emsid = 1;
select to_number(ems_val) from tab1 where emsid = 1

ORA-01722: invalid number
Even though we are using emsid=1 which will only lead to numeric values in ems_val, we are getting the invalid number.

Please advise.

Thanks.

Report message to a moderator

Re: ORA-01722 Invalid Number for numeric values. [message #685798 is a reply to message #685797] Fri, 25 March 2022 03:12 Go to previous messageGo to next message
John Watson
Messages: 8803
Registered: January 2010
Location: Global Village
Senior Member
Can you provide the whole example, in one session? To make sure there is no mistake. Like this:
orclz> create table tab1(ems_id number(9),ems_val varchar2(255));

Table created.

orclz> insert into tab1 values(1,'198')
  2  ;

1 row created.

orclz> insert into tab1 values(2,'F');

1 row created.

orclz>
orclz>
orclz> select to_number(ems_val) from tab1 where ems_id = 1;

TO_NUMBER(EMS_VAL)
------------------
               198

orclz>

Report message to a moderator

Re: ORA-01722 Invalid Number for numeric values. [message #685799 is a reply to message #685795] Fri, 25 March 2022 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68413
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.

My first thought is that you have a non-printable character in the column you think you only have digits.
Post the result of:
select dump(ems_val,16) from tab1 where emsid = 1;

Report message to a moderator

Re: ORA-01722 Invalid Number for numeric values. [message #685800 is a reply to message #685798] Fri, 25 March 2022 03:25 Go to previous messageGo to next message
Dhritman
Messages: 4
Registered: September 2005
Junior Member
Hi,

I dropped the table and re-created it and it worked fine in my DB but the same problem is occurring in my company's client's DB.

In my DB:

Connected to Oracle Database 12c Standard Edition Release 12.1.0.2.0 
Connected as geneva_admin@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db1313cn.netcracker.com)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RD1321A1)))

SQL> create table tab1(ems_id number(9),ems_val varchar2(255));

Table created


SQL> insert into tab1 values(1,'198');

1 row inserted


SQL> insert into tab1 values(2,'F');

1 row inserted


SQL> commit;

Commit complete


SQL> select to_number(ems_val) from tab1 where ems_id = 1;

TO_NUMBER(EMS_VAL)
------------------
               198
The same table is in my company's client's DB.
I am afraid, I cannot ask my company's client to drop the table in their DB and in their DB we are seeing the invalid number issue:-

SQL> select to_number(ems_val) from tab1 where ems_id = 1;
select to_number(ems_val) from tab1 where ems_id = 1

ORA-01722: invalid number
Any suggestions/solution from anyone ?

Thanks

Report message to a moderator

Re: ORA-01722 Invalid Number for numeric values. [message #685801 is a reply to message #685800] Fri, 25 March 2022 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68413
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I gave you what you have to do and no one told or suggested you to drop the table.

[Updated on: Fri, 25 March 2022 03:52]

Report message to a moderator

Re: ORA-01722 Invalid Number for numeric values. [message #685802 is a reply to message #685801] Fri, 25 March 2022 05:20 Go to previous messageGo to next message
Dhritman
Messages: 4
Registered: September 2005
Junior Member
Yes Thanks. But, actually before your post appeared I read John's post and he asked for the whole example. So I thought it is best I drop the table , re-create it , insert data, select data and capture everything and then post everything but unfortunately this made the issue go away.
I am guessing this issue is because of Oracle optimizer where it first applies to_number before checking the ems_id .

Report message to a moderator

Re: ORA-01722 Invalid Number for numeric values. [message #685803 is a reply to message #685802] Fri, 25 March 2022 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68413
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I am guessing this issue is because of Oracle optimizer where it first applies to_number before checking the ems_id .

And your are wrong, for sure, it's a basic of RDBMS; why Oracle, or any RDBMS, would apply a function on zillion of rows when it has to do it only on few ones? Just read the execution plan.
My guess is far more likely.

[Updated on: Fri, 25 March 2022 05:28]

Report message to a moderator

Re: ORA-01722 Invalid Number for numeric values. [message #685804 is a reply to message #685802] Fri, 25 March 2022 05:28 Go to previous message
Solomon Yakobson
Messages: 3211
Registered: January 2010
Location: Connecticut, USA
Senior Member
Don't guess. Issue:

explain plan for select to_number(ems_val) from tab1 where emsid = 1;
select * from table(dbms_xplan.display);
And post results.

SY.
P.S. Use real table/column names. I doubted tab1 is real name. And please confirm tab1 is table and not a view.

[Updated on: Fri, 25 March 2022 05:34]

Report message to a moderator

Previous Topic: DB last date
Next Topic: Remove empty tags from XML
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Mar 20 04:06:29 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.