PL/SQL & ora-6502 bug?

I'm trying to find a good answer to a large annoyance -- I'm working on a PL/SQL project that hunts for orphaned data in the database. I've tested each part of the project separately as I was developing it, and the thing works fine!

HOWEVER! When I run the whole package at once, 98% of it works -- a few bomb with:

ORA-06502: PL/SQL: numeric or value error

I know there's nothing wrong with the procedures -- I can run one, and it'll bomb, and after enough retries, it'll finally work! I've even tried rearranging the order the procedures are called:

Normal order:

exec proc (key1, table1)
exec proc (key2, table2)
exec proc (key3, table3) <-- BOMBS
exec proc (key4, table4)

Rearranged order:

exec proc (key1, table1)
exec proc (key2, table2)
exec proc (key4, table4)
exec proc (key3, table3) <-- WORKS

...this isn't the case all the time. If I remove an offending entry, then the next one down may bomb. Maybe not. (D'oh!)

I've looked all over the place in technet.oracle.com and support.oracle.com and can't find a working solution. One recommendation was to do an ALTER SYSTEM FLUSH SHARED_POOL -- and even after several of these, the problem still exists.

I'm running Oracle 7.3.2.3 on Solaris 2.5.1 -- upgrading to the latest version isn't an option at this time.

Any ideas? Other than a shotgun? Received on Wed Jan 27 1999 - 09:37:33 CST