I hereby issue a big sad sigh in response to your question.
Which means that I don’t have lots of very good news for you, but I do have a consolation prize (for me, not for you): IT’S ALL ORACLE’S FAULT (sort of).
Here’s the situation: Oracle added invoker rights (AUTHID CURRENT_USER) in Oracle8i. With invoker rights, it will use privileges assigned through roles to resolve references at runtime to objects in compiled code.
Sounds really great, but there is a limitation to this feature: it only applies to the following statements…
SELECT, INSERT, UPDATE, and DELETE data manipulation statements
The LOCK TABLE transaction control statement
OPEN and OPEN-FOR cursor control statements
EXECUTE IMMEDIATE and OPEN-FOR-USING dynamic SQL statements
SQL statements parsed using DBMS_SQL.PARSE()
In other words, invoker rights (and resolution through role-based privileges) does not apply to a static execution of a program within a compile unit of code.
In other other words, when PKG A calls SCHEMA1.PROGB, Oracle still needs directly granted privileges to resolve the reference. You can test this out for yourself by building a stored procedure in schema B that calls A’s packaged program. You should not be able to compile this procedure.
Now, the approach taken in Code Tester is that we create a test package, which calls the program being tested. A consequence of this approach is that we cannot compile the test code unless you have granted execute directly on this program to the owner of the test package.
And that is why you ran into this problem.
So…in the short term, we need to make this clear in our documentation. Mid-term, we will look into adding code to check the kind of privileges you have and inform you if they are role-based. Long-term, we could consider changing our generated test code in a way that might avoid this problem.
I am sorry I do not have better news for you…