I have a large stored procedure that calls a package (procedure within) that runs a number of procedures to determine whether the process can proceed or not. (It checks a student's authorization to enroll in a class.) We have a web process calling the stored procedure, and that works fine. What does not work is a call to the package. Oracle returns ORA-06508: PL?SQL: could not find program unit being called.
I have inserted debugging statements, so I know that it is the specific call to a procedure within the package that is failing with the ORA-06508. Thinking it might be something within the package, I inserted a debugging statement as the first line within called procedure in the package. (The debug statements call a procedure to inserts lines to a debug table with autonomous commit) .
When I log into the user acct with SQL Plus and run a test, it all works fine. But when the web process runs as the same user, the process fails at the call to the package procedure, and fails with ORA-06508.
It seems as if the web process is running with invoker rights, or some role is not granted.
Can anyone help me figure out what is wrong?