Analyzing App Dependencies in Oracle APEX

Oracle APEX 24.1 brings an exciting new feature that makes it easier to analyze application dependencies: the APEX_APP_OBJECT_DEPENDENCY API. This tool is a significant advancement for developers who are handling complex APEX applications or dealing with schemas that have many objects.

As APEX applications grow in complexity, keeping track of database object dependencies becomes increasingly challenging. The new APEX_APP_OBJECT_DEPENDENCY API addresses several common concerns:

• Identifying which database objects are used by specific applications

• Determining if dropping a schema object might break an application

• Verifying if all SQL and PL/SQL in every page of an application runs without errors

How It Works

Simply run the following PL/SQL code in your workspace. This will scan your entire app, analyzing references to schema objects in regions, SQL queries, PL/SQL processes, and even plugins

BEGIN
    apex_app_object_dependency.scan ( p_application_id => :app_id );
END;

The results of the scan are saved until:

• a new scan initiates

• apex_app_object_dependency.clear_cache is called

• the Oracle APEX instance is upgraded

Viewing the results

After running the scan, you can query the results using three new views:

1. APEX_USED_DB_OBJECTS: This view lists each schema object used at least once in your app:

    select
      referenced_type, referenced_owner,
      referenced_name, referenced_sub_name,
      usage_count
    from apex_used_db_objects
    where application_id = :app_id;
   

2. APEX_USED_DB_OBJECT_COMP_PROPS: This view shows each component property in your app that references at least one schema object:

    select
      page_id,
      component_type_name, component_display_name,
      property_group_name, property_name
    from apex_used_db_object_comp_props
    where application_id = :app_id;
   

3. APEX_USED_DB_OBJ_DEPENDENCIES: This view displays all DB objects referenced by each component property:

    select
      page_id,
      component_type_name, component_display_name,
      property_group_name, property_name,
      code_fragment,
      referenced_type, referenced_owner,
      referenced_name, referenced_sub_name
    from apex_used_db_obj_dependencies
    where application_id = :app_id;
   

Limitations

• Results are wiped when APEX is upgraded to a new version, requiring a re-scan of applications

• The reports do not include recursive dependencies (e.g., underlying tables of a view)

• Dependencies arising from dynamic SQL or PL/SQL (using "execute immediate") are not reported.

Improving the scan

The dependency analyzer specifically identifies connections to individual functions and procedures within packages that have been compiled with PL/Scope. Before starting the analysis, you can choose to recompile the relevant schema(s) with PL/Scope enabled to improve detection capabilities

alter session set plscope_settings='identifiers:all';
exec sys.dbms_utility.compile_schema(user, true);
alter session set plscope_settings='identifiers:none';