ORA-01722 error when running a query

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Wed, 29 Nov 2006 15:10:38 -0500
Message-ID: <77A4D80DB2ADD74EB5D7F1D31626F0C0038A7B22@usa0300ms03.na.xerox.net>

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

Received on Wed Nov 29 2006 - 14:10:38 CST