SQL Developer extension: add a report for viewing synonyms compiled with/without PL/Scope

[rvo-cs]

Synonyms compiled with PL/Scope can be found in sys.plscope_identifier$, with the following identifying properties:

• type# = 37
• symrep = name of the synonym
• obj# = object_id of the synonym in dba_objects

While such internal implementation details are not publicly disclosed by Oracle, hence there's obviously no commitment that they may not change in the future, in the meantime they make it possible to create a report for showing synonyms compiled with/without PL/Scope, along with relevant information (e.g. status of the synonym object, existence and type of the target object, creation date and last_ddl_time of the synonym object).

Because that report would query sys.plscope_identifier$, only users having the DBA role, or the SELECT ANY DICTIONARY system privilege, or an ad-hoc grant of SELECT / READ on sys.plscope_identifier$, would be able to use the new report. This privilege requirement should be made as obvious as possible, otherwise insufficiently-privileged users could be frustrated by trying the report, and receiving the ORA-00942 ("table or view does not exist") exception in response.

As of v1.0.0, existing reports only use ALL_xxx views, hence in principle they can be run by unprivileged users without raising exceptions—though the actual results will vary greatly depending on user privileges. The new report would be different in terms of privilege requirements and exception behaviour, therefore, in order to make this clearer for users I'd suggest to put it clearly aside, perhaps by using sub-folders under the main "plscope-utils Reports" folder:

• Main reports: for reports using ALL_xxx views; all existing reports (as of v1.0.0) would be put in that sub-folder
• Other reports: for reports of niche interest, or having high privilege requirements; the new report would be put in this sub-folder.

[PhilippSalvisberg]

Good idea to provide a report that shows which objects are compiled with/without PL/Scope.

There is an easier way to find out if a synonym is compiled with PL/Scope or not. No need to access sys.plscope_identifier$. Here's an example:

select s.owner,
       s.synonym_name,
       nvl2(i.owner, 'Yes', 'No') as compiled_with_plscope
  from all_synonyms         s
  left join all_identifiers i
    on i.owner = s.owner
   and i.object_name = s.synonym_name
   and i.type = 'SYNONYM'
 order by s.owner, s.synonym_name;

To cover all objects the query needs some minor amendments. Something like that:

select o.owner,
       o.object_type,
       o.object_name,
       nvl2(i.owner, 'Yes', 'No') as compiled_with_plscope
  from all_objects          o
  left join all_identifiers i
    on i.owner = o.owner
   and i.object_name = o.object_name
   and i.type = o.object_type
   and i.usage = 'DECLARATION'
 where o.object_type in (
          'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER',
          'TYPE', 'TYPE BODY', 'SYNONYM', 'TABLE', 'VIEW', 'SEQUENCE'
       )
 order by o.owner, o.object_type, o.object_name;

As you see this works with all_ views. No need to use dba_ views. No need to introduce a dedicated folder.

[rvo-cs]

No need to access sys.plscope_identifier$

That's right! Or at least it should be, say, 99.99% of the times.

The reason why I self-persuaded that this was not the case, and that querying sys.plscope_identifier$ was necessary in the first place, is because after I read your blog (here), I attempted to reproduce example 3b on a 19c test PDB. And, after issuing the alter public synonym dbms_output compile statement (either as SYSDBA, or as a regular PDB DBA account), the public synonym would not show up in DBA_IDENTIFIERS; I have just tested it again, and sure enough, a row gets created in sys.plscope_identifier$, but there should be a matching row in sys.plscope_action$ for the same obj# (with action = 1 as it is the declaration), and that row is missing... From there I wrongly inferred that all synonym declarations would be missing, without double-checking that.

I should certainly have started by compiling a public synonym for an object in an ordinary schema, not one belonging to SYS... And at present I do know that enabling PL/Scope in the SYS schema cannot be done naïvely just like that, unless one is begging for trouble.

So yes, I think it's safe to assume that declarations will be in DBA_IDENTIFIERS or ALL_IDENTIFIERS for synonyms compiled with PL/Scope, and that querying sys.plscope_identifier$ directly is unnecessary, unless perhaps in case of bugs, or if in a self-inflicted mess.