Skip to Main Content

Settings

14

Database Object Dependencies

Info

New API in Apex 24.1: apex_app_object_dependency
This new API was introduced in Apex 24.1, and it seems to solve the issue outlined on this page.

So I have disabled the Compute Dependencies button - the code is still there, but there is not much point in running it on 24.1 anymore.
Jeff Kemp wrote a nice article about this here, or check out the documentation.

Note: on my VM running Oracle 23ai Free, I got an error the first time I ran apex_app_object_dependency.scan: "ORA-04031: out of shared memory in shared pool".
Which is a bit ironic since the hacked solution outlined below still works just fine :-)
I suspect the 23ai Free database is a bit too limited (RAM-wise), because after a reboot of the VM the scan procedure ran fine.



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 while in the Apex Builder (depending on the Apex version, either change the page number to 425 or the page name to database-object-dependencies).

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_240100 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_240100 schema), with a few changes. For this example I named this package APEX_240100.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. Sometimes the procedure creates functions and procedures which do not compile.
One reason are comments (using --) at the end of a line. These are not interpreted correctly - to avoid issues, use /* */ instead.
Other reasons may be bind variables or linebreaks, but that would need to be investigated. The original Builder report shows the same incorrect hits by the way.
This report is showing more and more errors as time goes by, so it really is deprecrated it seems. As of Apex 24.1 (or maybe earlier) it has become quite useless.

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
Advanced
Warn on Unsaved Changes
Source
Source Used
Source Type
Default
Default Value
Default Value Type

Item

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

Button

Name
Button Name
Text Label / Alt
Displayed
Sequence
Display in Region
Behavior
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
Conditions
When Button Pressed
Condition Type