Tuesday, July 27, 2010

ORA-06508 error calling package in different schema

I had a PL/SQL package (let's call it PACKAGE_A) in schema SCHEMA_A which called a procedure in PACKAGE_B in SCHEMA_B. When I added a new parameter to the procedure in PACKAGE_B, I got some very strange errors showing up in PACKAGE_A. After trying all combinations of compiling PACKAGE_A and PACKAGE_B and REVOKEing and GRANTing EXECUTE rights to PACKAGE_B, I was consistently coming up with this Oracle error when I ran PACKAGE_A:

ORA-06508: PL/SQL: could not find program unit being called

Note that PACKAGE_A would compile fine, it just wouldn't run. I finally managed to get rid of this error by logging off my PL/SQL session in SCHEMA_A and then logging back in. After that, my PACKAGE_A call worked fine. "When in doubt, reboot!" A pretty old-school solution but it was the answer in this case!

NB. Oracle Version 10g Enterprise Edition