RE: ORA-01722 error when running a query

From: <oracle-l-bounce_at_freelists.org>
Date: Wed, 29 Nov 2006 15:34:43 -0500
Message-ID: <77A4D80DB2ADD74EB5D7F1D31626F0C0038A7B23@usa0300ms03.na.xerox.net>

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_at_labor.state.ny.us] Sent: Wednesday, November 29, 2006 3:26 PM To: Hameed, Amir; oracle-l_at_freelists.org 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hameed, Amir Sent: Wednesday, November 29, 2006 3:11 PM To: oracle-l_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 29 2006 - 14:34:43 CST