EBS 12.2 Thousands of Invalid Objects from one Grant? Yes…

Remember that editioning support introduced the concept of stub objects – those that point to the “real” version of the object when multiple editions are present (which is pretty much all the time in 12.2).

This is why the synonym for an object like APPS.FND_USER points to a current editioned object named APPLSYS.FND_USER#, which may exist in different forms in multiple editions.

The trouble is when you perform a simple grant DDL:

SQL> grant SELECT on APPS.FND_USER to XXCUSTOM;

Even if you have the whole middle-tier services down, or follow the instructions in Granting Privileges On An Object May Cause Invalidations (Doc ID 1987947.1) if you then check your usual source for invalid objects (such as using $AD_TOP/sql/adutlrcmp.sql) you’ll be blessed with hundreds and perhaps thousands of invalid stubs, all of which need to be re-compiled lest you start receiving the dreaded ORA-04062: signature of package “APPS.FND_GLOBAL” has been changed (meaning it was automatically re-compiled while in-use by another process), or ORA-07445: exception encountered: core dump (something was valid, but a bunch of dependent stub objects no longer are valid.)

What I’ve discovered in 12.2.6, with an 11.2.0.4 DB, even if you issue the DDL commands in a proper patching cycle, you still get thousands of invalid stubs.  So, really there’s only one solution – use the new API (introduced in patch 19674458:R12.AD.C) and present generally after AD.Delta.6

--As APPS:
SQL> exec AD_ZD.revoke_privs('SELECT','FND_RESPONSIBILITY','MSC');

Syntax:
————
ad_zd.grant_privs(
X_PERMISSIONS in varchar2, /* comma-seperated permission list */
X_OBJECT_NAME in varchar2, /* name of APPS object or APPS synonym */
X_GRANTEE in varchar2, /* grantee schema or role */
X_OPTIONS in varchar2 default NULL); /* grant options, example: ‘WITH
GRANT OPTION’ */

exec AD_ZD.grant_privs(‘SELECT’, ‘FND_RESPONSIBILITY’, ‘MSC’,X_GRANT_TO_TABLE=>TRUE)
PL/SQL procedure successfully completed.

Instead of:

SQL> grant SELECT on FND_RESPONSIBILITY to MSC;

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.