ORA-01722 error when running a query
- From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Nov 2006 15:10:38 -0500
Folks,
I have a standard Oracle 11i applications table (WSH_NEW_DELIVERIES)
with a column (NAME) of VARCHAR2(30) data type which stores numeric
data. When I run a query like the following against this table, it
returns the ORA-01722 error:
Select name from WSH_NEW_DELIVERIES where name=1048434;
But when I modify the statement and put single-quotes around the numeric
value then it runs fine:
Select name from WSH_NEW_DELIVERIES where name='1048434';
The statement mentioned above is a simplified test statement to
troubleshoot the issue. The predicate implicit conversion information
can be seen from DBMS_XPLAN.DISPLAY statement as shown below:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes |
Cost |
------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1 | 8 |
6349 |
|* 1 | INDEX FULL SCAN | WSH_NEW_DELIVERIES_U2 | 1 | 8 |
6349 |
------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
--------
1 - filter(TO_NUMBER("WSH_NEW_DELIVERIES"."NAME")=1048434)
Does anyone know why Oracle is returning the error? Any help will be
appreciated.
Thanks
Amir
--
//www.freelists.org/webpage/oracle-l
Other related posts:
