Skip to Main Content
  • Questions

  • Big PL/SQL block passes wrong parameters for function call

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, vmatyi.

Asked: September 04, 2024 - 5:42 pm UTC

Last updated: September 17, 2024 - 5:08 am UTC

Version: different versions, 19.17.0 - 23c

Viewed 10K+ times! This question is

You Asked

During executing a big PL/SQL blocks, some of the numeric parameters get lost/replaced with an earlier value.
There is no error message, the PL/SQL block executes, but some of the function calls get the wrong parameter values.

See the LiveSQL link: it defines a function which compares its input parameters, and calls it some 11 thousand times, and at the 10932th call, it gets mismatched values.

The problem seemingly occurs above a certain program size, but it seemingly didn't reach the diana nodes limit, there is no ORA-00123, the program executes, but stores the wrong data in the database.

Is there a size limit I bump into or can it possibly be an Oracle bug (that reproduces on multiple instances and different Oracle versions?)

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/b88zdplq1r4m273hx1y5r5dom

and Connor said...

A little more playing shows that it is when you get to 32767 parameters (ie, 10923 calls to the proc) that it blows up.

That seems to relate to several of the listed limits in the docs about PLSQL - 32767 is often a threshold.

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-program-limits.html

But we should be just silently allowing bad parameters to be floating around.

I'll log a bug.

Rating

  (1 rating)

Comments

Not just the parameter count

vmatyi, September 05, 2024 - 7:27 am UTC

Thnak you for the answer!

I've played quite some with it before, and it's gets more complicated: adding a fourth, varchar2 parameter to the procedure doesn't change the outcome, and the loop executes without any problems on the range of 24000 to 35000 (vs. the posted 34000 to 45000), seemingly only "counting" numeric literals where the value itself is greater than 32768.

(I know, bugs can have a thousand faces, it's just seems to be an unusually peculiar one)
Thanks!
Connor McDonald
September 17, 2024 - 5:08 am UTC

It was well spotted by you... although I do need to question why you have a plsql block that is making thousands of calls :-)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.