RE: ORA-01722 error when running a query
- To: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Nov 2006 15:34:43 -0500
Thanks Tom. I understand the implicit conversation between data types
and that is why I was surprised to see the error. I am looking at the
data right now to see if it has any issues.
-----Original Message-----
From: Mercadante, Thomas F (LABOR)
[mailto:Thomas.Mercadante@xxxxxxxxxxxxxxxxx]
Sent: Wednesday, November 29, 2006 3:26 PM
To: Hameed, Amir; oracle-l@xxxxxxxxxxxxx
Subject: RE: ORA-01722 error when running a query
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:
