RE: Query started getting 1722 error - interesting explanation wh y
- From: JayMiller@xxxxxxxxxxxxxxxx
- To: DGoulet@xxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 9 Sep 2005 14:29:16 -0400
Hmm, I think I checked that at the time but a week and a half later I can't
say for certain. Currently that is not the case but currently it's back to
the old explain plan as well so that doesn't say anything.
Thanks,
Jay Miller
Sr. Oracle DBA
x68355
-----Original Message-----
From: Goulet, Dick [mailto:DGoulet@xxxxxxxx]
Sent: Friday, September 09, 2005 2:23 PM
To: Miller, Jay; oracle-l@xxxxxxxxxxxxx
Subject: RE: Query started getting 1722 error - interesting explanation why
Jay,
Did that varchar2 have numbers only in it before it stopped working and
then picked up one with an alpha character? I've seen that one more than I
care to remember.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of JayMiller@xxxxxxxxxxxxxxxx
Sent: Friday, September 09, 2005 2:16 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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
.
_____
This message is confidential and sent by TD Waterhouse solely for use
by the intended recipient. If you are not the intended recipient, you
are hereby notified that any use, distribution or copying of this
communication is strictly prohibited. This should not be deemed as an
offer or solicitation, to buy or sell any product. Any 3rd party
information contained herein was prepared by sources deemed reliable,
but is not guaranteed. TD Waterhouse does not accept electronic
instructions that would require an original signature. Information
received by or sent from TD Waterhouse is stored, subject to review,
and may be produced to regulatory authorities or others with a legal
right to such.
Other related posts:
- » RE: Query started getting 1722 error - interesting explanation wh y
