How to detect broken PL/SQL in Apex Application?-CodePudding

We have several Apex 19.2 applications.

Inside each app there is PL/SQL everywhere: Application Processes, Pages Processes, Conditions, Item Sources, etc.

Sometimes when we change something in the database, let's say renaming a table, we would like to make sure no PL/SQL code is broken inside any of the applications.

Currently, whenever we rename an object, we search inside the apps using the following queries, for this object and make sure the pieces of code are working (or use the workspace search feature):

    select * from apex_application_computations where application_id = 1200;
    select * from APEX_APPLICATION_PAGE_COMP where application_id = 1200;
    select * from APEX_APPLICATION_PAGE_PROC where application_id = 1200; 
    select * from APEX_APPLICATION_PAGE_VAL where application_id = 1200;
    select * from APEX_APPLICATION_PAGE_BRANCHES where application_id = 1200;
    select * from apex_application_page_items where application_id = 1200;
    select * from APEX_APPLICATION_PAGE_REGIONS where application_id = 1200;

This method works but has it's limitations. For example, in case we miss another table we have to look into, then the code will be broken.

Another limitation of this method is that sometimes the change has more complex consequences. For example, let's say we renamed a table, the same table itself is not used directly in any application, but used by a view which is used in the applications. In this case, this method wont work and we will end up with broken code.

For database objects we use the following query which works very well:

SELECT object_type, object_name FROM user_objects WHERE status = 'INVALID'

So we were wondering if there is something equivalent that works for code inside the applications.

Does anyone know how can we spot immediately which piece of code in the applications is broken following a change in the database?

CodePudding user response：

The answer is given by @Doomi : "Your App"--> Utilities --> Advisor and check for Valid/Invalid SQL or PL/SQL Code

Thanks.