You Asked

Hi Tom,

We have a package which was running fine from quite a sometime in production.
All of the sudden, the report associated to this package got error out. The reason being an and condition in the procedure inside the package i.e.
and ct.tran_code = 60.Here the tran_code is an VARCHAR2(2 BYTE) data type.After replacing the code with ct.tran_code = '60,the package is
working fine. How come a package suddenly started having issues which wasn't there earlier. The same code with same data is working
fine in test environment. The column has both numerical and non numerical data like YD,73,A3,99,07

and Connor said...

Because the execution plan changed.

ct.tran_code = 60

is silently changed to

to_number(ct.tran_code) = 60

so if there is data that cannot be converted to a number, then it might crash. Now you might be wondering, "How did it ever work?". Well, it worked before by *good luck*, ie, the query managed to filter out the bad data with other predicates or joins before it hit the bad data.

When the execution plan changed, your luck ran out :-)

Here's a video showing a similar example

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

tuning

Laurent Schneider, June 16, 2017 - 1:58 pm UTC

It's indeed better to use tran_code='60' if you've an index.

If you sql-tune tran_code=60, the optimizer sometimes suggests create index on to_number(tran_code), which will fails or prevent future non-numeric insert.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

tuning

More to Explore

PL/SQL demos

PL/SQL docs

Numeric data not working which used to work earlier

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

tuning

More to Explore

PL/SQL demos

PL/SQL docs