Re: ORA-01722

This is a mixture of bad luck and bad design. You have a character column pretending to be a numeric column, and although your query has some code in it to try and ensure that the numeric tests are only made against strings that look like numbers, you have not managed to guarantee that this happens.

One reason why rebuilding the table 'makes it work', is that the rebuild may change the statistics of the table sufficiently that a different optimizer path gets used, which just happens to bypass the problem.

As a general rule it is probably always possible to make things break in such a case, but you may be able to combine the /*+ ordered_predicates */ hint with a carefully designed predicate to avoid the problem.

e.g.

select /*+ ordered_predicates */

    {columns}
from op_service_info_osi
where

       translate(OSI_SRV_CODE_FROM, '.0123456789','.') is null and ...

The translate() predicate is a crude check that the string consists entirely of numeric characters, so you might want to refine it somewhat.

The key point is that by putting the ORDERED_PREDICATES hint in with the critical filtering conditions as the first (non-join) predicates for the table, you may be able to force the optimizer to eliminate any rows that would return an ORA-0 1722.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 21 April 2002 19:59

|Hi List,
|I have a strange problem for ONLY one table in entire database, when
run the
|following query got ORA - 01722 and when I drop the table and rebuild
it
|it's running OK,
|Any Idea?
|this is the query which I run:
|
|
|select count(*)
| from (select osi_srv_code_from, osi_srv_code_to
| from op_service_info_osi where OSI_TASK_ID = ? and
| substr(OSI_SRV_CODE_FROM, 1, 1) = '9' AND
|substr(OSI_SRV_CODE_TO, 1,1)='9' ) a
| where
| (to_number(a.OSI_SRV_CODE_FROM) = 97001 and
|to_number(a.OSI_SRV_CODE_TO) = 97001)
| OR (to_number(a.OSI_SRV_CODE_FROM) >= 97002 and
|to_number(a.OSI_SRV_CODE_TO) &lt;= 97530)
| OR (to_number(a.OSI_SRV_CODE_FROM) >= 97531 and
|to_number(a.OSI_SRV_CODE_TO) &lt;= 97750)
| OR (to_number(a.OSI_SRV_CODE_FROM) = 97003 and
|to_number(a.OSI_SRV_CODE_TO) = 97003)
| OR (to_number(a.OSI_SRV_CODE_FROM) >= 97004 and
|to_number(a.OSI_SRV_CODE_TO) &lt;= 97530)
| OR (to_number(a.OSI_SRV_CODE_FROM) = 97535 and
|to_number(a.OSI_SRV_CODE_TO) = 97535)
| OR (to_number(a.OSI_SRV_CODE_FROM) = 92056 and
|to_number(a.OSI_SRV_CODE_TO) = 92056)
| OR (to_number(a.OSI_SRV_CODE_FROM) >= 92057 and
|to_number(a.OSI_SRV_CODE_TO) &lt;= 92526)&quot;, exception:
ORA-01722:
|invalid number
|
|oracle 8.1.7 sun OS5.7, I have the same problem on 8.1.6 version TOO.
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).