Note: Since Apex 20.2 the "Utilities -> Database Object Dependencies" report is missing from the Utilities page. That may be a bug or because it is deprecated, but it is still operational - just run page 425 manually by editing the url in the Apex Builder.
I used the "Utilities -> Database Object Dependencies" report recently for an "old code cleanup" operation, to see which objects (packages/tables/views/etc) were used in a number of Apex applications. It is a very helpful tool, but is also turned out to be incomplete...
If an object is referenced via a public synonym, this reference does not show up
in the report. This only applies to public synonyms, not private ones.
E.g. if you have table A_T in schema A, and public synonym A_S for table A_T, and you use A_S in an Apex application, then you would expect A_S to show up in the report. But alas, neither A_S nor A_T show up, so you miss that dependency altogether.
I never noticed this before, because I personally never create public (or private) synonyms for objects I create; I'm not a fan of synonyms in general and avoid them whenever possible.
Ignoring public synonyms is not completely illogical - you don't want to have every "select ... from dual" showing up for example, or all the Apex public synonyms used in an application. You do however want to find all the public synonyms for objects you created yourself.
So to fix this, we need to recreate the Apex Database Object Dependencies report, and include our own public synonyms (while still ignoring all the "standard" public synonyms).
This is not a simple report however. The Database Object Dependencies report queries a collection, which is filled by a procedure. This procedure creates temporary functions and procedures for each of the (PL/)SQL blocks in the Apex application, and uses dba_dependencies to find all the objects it references. So we have to recreate (and modify) the package that fills the collection, before we can create a new report.
You can find the procedure call that fills the collection using this (run as sys):
For Apex 18.1 and up this returns: wwv_flow_theme_manager.find_object_dependencies(p_flow_id=>:fb_flow_id,p_page_id=>null);
Older Apex versions (e.g. 4.2) use this: wwv_flow_theme_files.find_object_dependencies(p_flow_id=>:fb_flow_id,p_page_id=>null);
The package is located in the APEX_220200 schema (exact name depending on your Apex version of course).
All Apex packages are wrapped, so first we need to unwrap the wwv_flow_theme_manager (or wwv_flow_theme_files) package body. The easiest way to do this is by using a SQL Developer plugin by Philipp Salvisberg: https://www.salvis.com/blog/2015/05/17/introducing-plsql-unwrapper-for-sql-developer/
Note that the (quite illogical in my view) way to install extensions in SQL Developer is via Help - Check for Updates - Install From Local File(s) (option at the bottom). It always takes me 10 minutes to find that one, so I thought I'd mention it - to help myself in the future :-)
Next we can create a copy of the original package (in the same APEX_220200 schema), with a few changes. For this example I named this package APEX_220200.ted_flow_theme_manager.
Towards the end of the find_dependencies procedure you will find this line: "IF C1.REFERENCED_OWNER NOT IN ('SYS','PUBLIC')" - here all public objects (including synonyms) are filtered out, so we need to fix this part.
In the find_dependencies procedure declaration, include this line:
Then replace the "IF C1.REFERENCED_OWNER NOT IN ('SYS','PUBLIC')" line with this block of code:
Make sure to leave the line "AND C1.REFERENCED_NAME != 'DBMS_LOB' THEN" intact.
Also make sure the very last "end [package_name]" line matches the new package name (or just use "end").
And finally grant the execute privilege so we can execute the procedure in the Apex application:
After all that, we can finally recreate the Database Object Dependencies report using our new package. You can find out how the report on page 425 of the Builder does this, by querying the apex_% views for the elements on that page. See the report above and the code below for my implementation.
To show all this actually works, I have created a public synonym and used that in a page item on this page.
Note 1: The Parsing Errors report contains a few rows; these are not errors however, the code runs just fine. For some reason the procedure creates functions and procedures which do not compile. I'm guessing this has to do with bind variables, comments and/or linebreaks, but that would need to be investigated. The original Builder report shows the same incorrect hits by the way.
Note 2: The Database Object Dependencies report ignores:
- All PL/SQL code in Dynamic Actions. So of you want to include that information, you'll have to add that manually in the find_object_dependencies procedure, or add it later.
- References to tables/view/materialized views, when they are defined in processes like Automated Row Fetch.
For more info on how to fix that, see this page: DBA - Object Dependencies