RE: ORA-01722 error when running a query
- From: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <Amir.Hameed@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Nov 2006 15:26:24 -0500
Amir,
When you run the statement:
Select name from WSH_NEW_DELIVERIES where name=1048434;
Oracle tries to convert all values of the "name" column to a number to
match the literal value in your "where" clause. You obviously have some
records where the value is not all numbers and this is causing the
ORA-1722.
When you use the quotes around the value, all is well. Also note that
if you ran an explain plan against the original query, you would see a
full table scan rather than using the index. Again, this happens
because Oracle is converting the column to a literal and thus cannot use
the index.
Hope this helps.
Tom
--------------------------------------------------------
This transmission may contain confidential, proprietary, or privileged
information which is intended solely for use by the individual or entity to
whom it is addressed. If you are not the intended recipient, you are hereby
notified that any disclosure, dissemination, copying or distribution of this
transmission or its attachments is strictly prohibited. In addition,
unauthorized access to this transmission may violate federal or State law,
including the Electronic Communications Privacy Act of 1985. If you have
received this transmission in error, please notify the sender immediately by
return e-mail and delete the transmission and its attachments.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hameed, Amir
Sent: Wednesday, November 29, 2006 3:11 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: ORA-01722 error when running a query
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
