Skip to Main Content

Settings

14

Database Object Dependencies

Database Object Dependencies IR

Info

I used the 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 usedin 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 through 20.1 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_200100 schema (exact name depending on 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/.
Next we can create a copy of the original package (in the same APEX_200100 schema), with a few changes. For this example I named this package APEX_200100.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.

And 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

Code

Item

Identification
Sequence
Name
Display As
User Interface
Region
Settings
Value Required
Page Action on Selection
List Of Values
Display Extra Values
Display Null Value
List of values definition
Source
Source Used
Source Type

Item

Identification
Sequence
Name
Display As
User Interface
Region
Settings
Value Required
Subtype
List Of Values
Display Null Value
Source
Source Used
Source Type
Source value or expression

Button

Name
Button Name
Text Label / Alt
Displayed
Sequence
Display in Region
Action When Button Clicked
Action
Execute Validations

Dynamic Action

Identification
Name
Execution Options
Sequence
When
Event
Selection Type
Item(s)
Client-Side Condition
Type
Advanced
Event Scope
True Action
Identification
Action
Execution Options
Sequence
Fire On Initialization
Affected Elements
Selection Type
Button
False Action
Identification
Action
Execution Options
Sequence
Fire On Initialization
Affected Elements
Selection Type
Button

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Report Column

Column Definition
Column Name
Display Type
Column Heading

Process

Name
Button Name
Type
Process Point
Sequence
Process Point
Run Process
Source
Process