Find the Database Objects used by your Oracle APEX Applications

Wednesday, December 6, 2023

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. 


Slika, ki vsebuje besede besedilo, posnetek zaslona, programska oprema, večpredstavnostna programska opremaOpis je samodejno ustvarjen


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:

Slika, ki vsebuje besede besedilo, posnetek zaslona, številka, pisavaOpis je samodejno ustvarjen

 

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:

Slika, ki vsebuje besede besedilo, številka, programska oprema, pisavaOpis je samodejno ustvarjen

Slika, ki vsebuje besede besedilo, posnetek zaslonaOpis je samodejno ustvarjen


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:


Slika, ki vsebuje besede besedilo, številka, posnetek zaslonaOpis je samodejno ustvarjen


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:



Slika, ki vsebuje besede besedilo, posnetek zaslona, pisava, številkaOpis je samodejno ustvarjenSlika, ki vsebuje besede besedilo, posnetek zaslona, številka, pisavaOpis je samodejno ustvarjen


 

 

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  


Slika, ki vsebuje besede besedilo, posnetek zaslona, programska oprema, zaslonOpis je samodejno ustvarjen

Slika, ki vsebuje besede besedilo, programska oprema, številka, spletna stranOpis je samodejno ustvarjen

 


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.

Picture of Aleš Kravos

Aleš Kravos

COO @ The Right Thing Solutions

APEX Project Eye Lead Developer

Comments

No comments yet, be the first one to let us know what you think of this article!