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 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.
The exact same data is in multiple DBs but strangely I am getting invalid number exception only in 1 of the DBs and not in the rest.
Is there a solution to this ?
Please advise.
Thanks