Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Query started getting 1722 error - interesting explanation why
I got a call from a developer the other week complaining that a query in QA
had started giving an ora-1722. It had worked fine for several months and
the identical query was working fine in production.
Select count(1)
from prod.job_profile jp
where step_id < 9999
and not exists (select 'x' from prod.job_log jl
where jl.job_id = jp.job_id and ( jl.status in ('C', 'EI') ) and jl.batch_id in (select to_number(text_value) from prod.batch_parameter where name = 'BATCH_ID'));
Investigating confirmed this, along with the fact that the query was comparing a string (jl.batch_id) with a number (to_number(text_value)). But why did it suddenly stop working and why did it still work in production?
Comparing explain plans between QA and production I saw that they were different (statistics had changed during QA testing). The query that failed was using an index on jl.batch_id.
Apparently Oracle automatically took care of converting the string to a number *unless* the number was being used to access an index on the varchar2 column. Interesting behavior. I can see why it might work that way but it's certainly not intuitive.
Of course explaining this to the developer took quite a while. "It worked before, why shouldn't it work now? What's wrong with Oracle?"
Oracle 9.2.0.6
Jay Miller
Sr. Oracle DBA
.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 09 2005 - 13:18:56 CDT