Home » SQL & PL/SQL » SQL & PL/SQL » Invalid number error when trying to retrieve numeric data from varchar2 columns. (10.2.0.3)
Invalid number error when trying to retrieve numeric data from varchar2 columns. [message #301219] Tue, 19 February 2008 12:01 Go to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

Hello, I have 2 databases with Oracle 10.2.0.3 ..and the below process works fine in 1 db and in the other database it throws me a invalid number error .. { trying to get the numeric data out of varchar2 datatype without using Quotes } ..

testtab_temp@testdb:19-feb-2008> alter table testtab add(b varchar2(10));

Table altered.

Elapsed: 00:00:01.00
testtab_temp@testdb:19-feb-2008> update testtab set b=200;

1 row updated.

Elapsed: 00:00:00.01
testtab_temp@testdb:19-feb-2008> commit;

Commit complete.

Elapsed: 00:00:00.00
testtab_temp@testdb:19-feb-2008> select * from testtab where a=100 and b=200;

A B
---------- ----------
100 200

Is there any setting that need to be taken care of .. ?
Re: Invalid number error when trying to retrieve numeric data from varchar2 columns. [message #301222 is a reply to message #301219] Tue, 19 February 2008 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NEVER relies on implicit conversion.

You query is converted to:
select * from testtab where to_number(a)=100 and to_number(b)=200;

So no index and error if one row contains something that is not a number.

Regards
Michel
Re: Invalid number error when trying to retrieve numeric data from varchar2 columns. [message #301223 is a reply to message #301222] Tue, 19 February 2008 12:30 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
After posting more than 12K you have not followed the forum guidelines. Laughing

Please take some time to read the forum guidelines. I am only kidding.

Regards

Raj
Re: Invalid number error when trying to retrieve numeric data from varchar2 columns. [message #301224 is a reply to message #301219] Tue, 19 February 2008 12:57 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
It's not the DBs that are different. It is your data. Your select will work fine as long as there are only numbers in the column in question.
SQL> CREATE TABLE t (a VARCHAR2(10));

Table created

SQL> INSERT INTO t VALUES(200);

1 row inserted

SQL> SELECT * FROM t WHERE a = 200;

A
----------
200
But once you add a non-numeric, the select will no longer work
SQL> INSERT INTO t VALUES('a');

1 row inserted

SQL> SELECT * FROM t WHERE a = 200;

SELECT * FROM t WHERE a = 200

ORA-01722: invalid number

SQL>
Re: Invalid number error when trying to retrieve numeric data from varchar2 columns. [message #301228 is a reply to message #301224] Tue, 19 February 2008 13:29 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not what I said?

Regards
Michel
Previous Topic: Urgent: DBMS_SQL.PARSE - Raising ORA-01009: missing mandatory parameter
Next Topic: How to do Implied Commit
Goto Forum:
  


Current Time: Sun Dec 04 14:40:41 CST 2016

Total time taken to generate the page: 0.11840 seconds