Apex - General - Database Dependencies

Search For

App ID

Emp Public Synonym N

Database Object Dependencies IR

Parsing Errors IR

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):

select to_char(p.process_source) as process_source
from apex_application_page_proc p
where p.application_id = 4000
and   p.page_id        = 425 -- the Builder page that contains the report
and   p.process_name   = 'populate collection'

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:

v_ted_synonym_name varchar2(100);

Then replace the "IF C1.REFERENCED_OWNER NOT IN ('SYS','PUBLIC')" line with this block of code:

v_ted_synonym_name := null;
if c1.referenced_owner = 'PUBLIC' and c1.referenced_type = 'SYNONYM' then
  select max(s.synonym_name)
  into v_ted_synonym_name
  from dba_synonyms s
  where s.owner        = c1.referenced_owner
  and   s.synonym_name = c1.referenced_name
  and (
        s.table_owner in
          ( -- List of standard users - source: http://www.morganslibrary.org/reference/users.html
            -- For database 12c or higher, you can simply use: select username from sys.dba_users where oracle_maintained = 'Y'
             'ANONYMOUS','APPQOSSYS','AUDSYS','CTXSYS','DBSFWUSER','DBSNMP','DIP','DVF','DVSYS','GGSYS','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','LBACSYS','MDDATA'
            ,'MDSYS','OJVMSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','PDBADMIN','PM','REMOTE_SCHEDULER_AGENT','SH','SI_INFORMTN_SCHEMA'
            ,'SPATIAL_CSW_ADMIN_USR','SYS','SYS$UMF','SYSBACKUP','SYSDG','SYSKM','SYSRAC','SYSTEM','WMSYS','XDB','XS$NULL'
          )
        or s.table_owner in
          ( -- all Apex schemas
            select username
            from sys.dba_users
            where username in ('FLOWS_FILES')
            or    username like 'APEX\_%' escape '\' --'
            or    username like 'ORDS\_%' escape '\' --'
          )
      )
  ;
end if;

          IF C1.REFERENCED_OWNER NOT IN ('SYS')
          and not (     c1.referenced_owner = 'PUBLIC'
                    and c1.referenced_type  = 'SYNONYM'
                    and c1.referenced_name  = nvl(v_ted_synonym_name, '^') -- if we found the public synonym, it is "standard" so we can ignore it
                  )

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:

grant execute on APEX_220200.ted_flow_theme_manager to tedstruik;

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.

create public synonym emp_public_synonym for tedstruik.emp;

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.

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

Item

Identification
	Sequence	10
	Name	P1107_APP_ID
	Display As	Select List
User Interface
User Interface	Region	Settings
Settings
	Value Required	No
	Page Action on Selection	SUBMIT
List Of Values
	Display Extra Values	Yes
	Display Null Value	Yes
	List of values definition	select t.application_id d , t.application_id r from apex_applications t order by 1
Source
	Source Used	Only when current value in session state is null
	Source Type	Always Null
Default
	Default Value	select nvl(v('APP_ID'), min(t.application_id)) from apex_applications t
	Default Value Type	SQL_QUERY

Item

Identification
	Sequence	20
	Name	P1107_EMP_PUBLIC_SYNONYM_N
	Display As	Display Only
User Interface
User Interface	Region	Settings
Settings
	Value Required	No
	Subtype	Y
List Of Values
List Of Values	Display Null Value	No
Source
	Source Used	Only when current value in session state is null
	Source Type	SQL Query (return single value)
	Source value or expression	select count(*) from emp_public_synonym

Button

Name
	Button Name	COMPUTE_DEPENDENCIES
	Text Label / Alt	Compute Dependencies
Displayed
	Sequence	10
	Display in Region	Settings
Action When Button Clicked
	Action	Submit Page
	Execute Validations	Yes

Dynamic Action

Identification
Identification	Name	on change P1107_APP_ID
Execution Options
Execution Options	Sequence	10
When
	Event	Change
	Selection Type	Item
	Item(s)	P1107_APP_ID
Client-Side Condition
Client-Side Condition	Type	is null
Advanced
Advanced	Event Scope	Static
True Action
True Action
Identification
Identification	Action	Disable
Execution Options
	Sequence	10
	Fire On Initialization	Yes
Affected Elements
	Selection Type	BUTTON
	Button	COMPUTE_DEPENDENCIES
False Action
False Action
Identification
Identification	Action	Enable
Execution Options
	Sequence	10
	Fire On Initialization	Yes
Affected Elements
	Selection Type	BUTTON
	Button	COMPUTE_DEPENDENCIES

Region

Identification
	Sequence	21
	Title	Database Object Dependencies IR
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Region Source	with a as ( select distinct decode( c.c003 , 'NON-EXISTENT' , wwv_flow_lang.system_message('LAYOUT.T_PAGE_PUBLIC') , c.c001 ) as c001 , c.c002 , decode(c003, 'NON-EXISTENT', c.c002, c.c002) as name , initcap( decode( c.c003 , 'NON-EXISTENT' , null , c.c003 --htmldb_item.text_from_lov(c003 ,'OBJECT.TYPES') ) ) as c003 , count(*) as c , c.c012 from htmldb_collections c where c.collection_name = 'ALL_DEPENDENCIES' and c.c008 = :P1107_APP_ID and c.c002 <> 'PLITBLM' group by c.c001 , c.c002 , c.c003 , c.c012 ) select ( case when a.c001 = '#'\|\|'OWNER'\|\|'#' then app.owner else a.c001 end) as c001 -- concatenation is needed here , a.c002 , a.name , a.c003 , a.c , a.c012 from a , ( select owner from apex_applications where application_id = :P1107_APP_ID ) app -- this is used to avoid the '#OWNER#' value for c001 (which is rather annoying in the original report) order by 1 , 2 , 3

Report Column

Column Definition
	Column Name	C001
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Owner

Report Column

Column Definition
	Column Name	C002
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	C002

Report Column

Column Definition
	Column Name	NAME
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Referenced Name

Report Column

Column Definition
	Column Name	C003
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Referenced Type

Report Column

Column Definition
	Column Name	C
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Reference Count

Report Column

Column Definition
	Column Name	C012
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	C012

Region

Identification
	Sequence	31
	Title	Parsing Errors IR
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Region Source	select distinct c001, c002, c003, c004, c005, c006, c007, c008, c009, c010, c011, c012 from htmldb_collections c join ted_apex_application_pages_v p on trim(p.page_id) = c.c002 where c.collection_name = 'ALL_DEPENDENCIES_ERRORS'

Report Column

Column Definition
	Column Name	C001
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Name

Report Column

Column Definition
	Column Name	C002
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Page

Report Column

Column Definition
	Column Name	C003
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	ID

Report Column

Column Definition
	Column Name	C004
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	SQL

Report Column

Column Definition
	Column Name	C005
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Error

Report Column

Column Definition
	Column Name	C006
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Application

Report Column

Column Definition
	Column Name	C007
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Parse Method

Report Column

Column Definition
	Column Name	C008
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Dependencies

Report Column

Column Definition
	Column Name	C009
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Compiled As

Report Column

Column Definition
	Column Name	C010
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Parsed As

Report Column

Column Definition
	Column Name	C011
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Temp Object Status

Report Column

Column Definition
	Column Name	C012
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Lines

Process

Name
	Button Name	populate collection
	Type	PL/SQL anonymous block
Process Point
	Sequence	10
	Process Point	On Submit - After Computations and Validations
	Run Process	Once Per Page Visit (default)
Source
Source	Process	apex_220200.ted_flow_theme_manager.find_object_dependencies ( p_flow_id => :P1107_APP_ID , p_page_id => null ) ;

Settings

Database Object Dependencies

Database Object Dependencies IR

Parsing Errors

Parsing Errors IR

Info

Code

Item

Item

Button

Dynamic Action

Region

Report Column

Report Column

Report Column

Report Column

Report Column

Report Column

Region

Report Column

Report Column

Report Column

Report Column

Report Column

Report Column

Report Column

Report Column

Report Column

Report Column

Report Column

Report Column

Process