APEX applications are always built on top of database objects. References to tables or views, SQL queries, and PL/SQL code can be placed in numerous APEX component properties. It can often become a challenge to have an overview of how your database objects are utilized in your application. So how can a developer understand the impact of changing the definition of a database object or understand what objects are utilized by an APEX page?

The native option is to use the Embedded Code utility, which was introduced in Oracle APEX 20.2. Using faceted search filters, developers can inspect all SQL, PL/SQL, and JS code in their applications. Developers can search for a specific database object and identify on which pages it is being utilized if it is used in the SQL or PL/SQL code. 

However, the results do not include declarative references to tables and views, often used for Region Sources and other APEX components. It’s also possible to utilize the powerful native search functionality that covers such references. Still, the results are not displayed in a structured format, so it’s hard to have an overview of which pages or components the database object is used on.

The alternative is to use the Object Dependency calculation functionality inside APEX Project Eye, which scans the metadata and stores all references in its internal tables, allowing developers to have a structured view of how APEX applications and pages reference various database objects. 

How does it work?

APEX Project Eye has its own list of where database objects can be referenced, which is maintained with each Oracle APEX release. 

The list includes:

• All types of region sources
• Application and Page Processes
• Server-side dynamic actions
• Application and Page Computations
• Item sources
• Server-side conditions on all components
• Read-only conditions
• List of values
• Authorization schemes
• Authentication schemes
• Search configurations
• Error handling function
• Application initialization and clean-up code
• Dynamic lists
• Chart series
• Supporting objects
• Interactive grid source expressions
• Plug-ins
• Task definition actions
• Automations

Since APEX 23.2, APE has also scanned various workflow components like activities, variables, and participants.

Based on the code or table/view references, a temporary procedure is created based on which the USER_DEPENDENCIES database dictionary view is queried, which lists all dependent database objects. The results are stored together with all the related metadata inside APE’s internal tables, ready to be used by APE or other tools.

The calculation can be executed manually, but preferably executed by APEX Project Eye through a scheduler job execution. This can be done by including APEX applications in APE Projects.

What information is available?

The following information is stored in the APE_DEPENDENCY table for each referenced database object:

• Object owner
• Object name
• Object type
• APEX Application ID
• APEX Page ID
• APEX Component name (i.e., region name)
• APEX Component type
• The source itself
• Used Bind variables (i.e., page items)
• Source type (i.e., SQL Query)

If a source cannot be parsed, it is stored in the APE_DEPENDENCY_ERRORS table. 

The information can be queried through an interactive report region inside APE which also displays database objects that have a dependency on the identified database object:

There is an alternative tree view display that can help us browse through the database objects. It is possible to browse through the objects of the entire application or only objects referenced by a single page:

Where else can we benefit from this information inside APE?

Besides the above-mentioned browsing options, there are two additional functionalities inside APEX Project Eye that use this information or the underlying calculation engine.

First is the generation of the technical documentation report. In the wizard where users can configure what content should be included in the technical documentation, it is possible to limit the list of database objects only to those that are referenced by the application or set of pages for which the documentation is being generated:

The report itself includes information about where database objects are utilized and also the other way around -  which database objects are utilized on APEX pages:

The second place where APEX Project Eye uses the calculation engine is Quality Assurance. It is possible to assess all SQL and PL/SQL code inside APEX applications by creating a QA rule that is based on a query using the following function: APE_QA_API.f_get_source_for_check.

APEX Project Eye comes with the following preloaded QA rules, with good examples of how to assess your code:

• UC - Valid SQL or PL/SQL code   
• UC - Execute immediate in APEX SQL or PLSQL code       
• UC - Misuse of bind variables in PLSQL  

Conclusion

There are numerous cases where it is necessary to understand the relationship between your APEX applications and database objects. Oracle APEX offers native utilities to help you with that, while APEX Project Eye offers advanced options to bring your application development to another level.