0

Environment:

  • DB Version: Oracle Database 12cR2 EE on Exadata

  • RAC: True

  • Problem: ORA-06508 on procedure.

One of our users had a DBA role privilege, we don't want such a big privilege to be defined anymore. When we revoke the DBA role privilege from this user, all procedures, packages and triggers became invalid. This was something we expected at that moment and we accepted it as normal. When we compiled all of them, there was no problem and we reassigned the DBA role privilege to this user in order to postpone this work until later.

The problem starts from this point. We get an error when one of the procedures wants to run. But the error is not constantly getting, for example it was working correctly for the last 26 hours, but then it got the error. When we drop / create or compile, the problem is solved. However, the strange part is that when this problem occurs, the procedure and dependencies all seem to be valid. The error is as follows:

Error raised in: PROCEDURE_NAME at line xxxx - ORA-06508: PL/SQL: could not find program unit being called

Line-xxxx:

EXCEPTION
   WHEN OTHERS THEN ROLLBACK;
    R_CODE:= 400;
    R_MESSAGE:='Error raised in: '|| $$plsql_unit ||' at line ' || $$plsql_line || ' - '||sqlerrm;

I could not find a suitable solution for this scenario on Oracle Support or on dba.stackexchange and I am still researching. Anyone have a good idea?

Best Regards,

2
  • Who owns the code? Who's calling the code when it errors? Is it a Definer's Rights code? ( AUTHID DEFINER ) Is CBAC in uses? Why are you not logging your error stack before you toss it? Without the full error stack, I don't think we can help you. Check GitHub for loggers. Nov 20 at 20:01
  • @MichaelKutz, owner and caller are same users.
    – jrdba123
    Nov 20 at 21:02
0

$$plsql_line contains the line where it appears, which is useless locating the cause of an exception. You're not the first falling for this, eg. using-plsql-line. Read the solution there, eg. using DBMS_UTILITY.FORMAT_CALL_STACK in your exception handling.

-2

Reason: The called stored procedure has been modified
solution:Recompile stored procedures

1
  • That's not the main issue, I already know the solution you mentioned. The main issue here is; why this error is received and permanent solution is found. We cannot continually recompile this procedure.
    – jrdba123
    Nov 21 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.