Skip to Main Content
Feature Request FR-4067
Product Area APIs
Status CLOSED

3 Voters

PLSQL Based - SQL/PLSQL validation function

mcgeemp Public · Oct 17 2024

Idea Summary
Provide a PLSQL based function which you can pass an APP_ID and optional PAGE_ID.  The function will query all configured SQL/PLSQL for regions, sources, conditions, and the like, and invoke the same validation the button does in the code editor for the developer.  Ideally for pages, it could also query dependent shared components and validate them as well.

Use Case
With automation-based installs of pages and applications, I would like to be able to hit a PLSQL API, passing in the APP_ID/PAGE_ID, and ensuring that the newly installed page passes validation.  Ideally the function would return a JSON object of page, region/page object name, invalid code, ORA error incurred.

Preferred Solution (Optional)
Via PLSQL API.

This is a great idea! You can already achieve this in APEX today with a slightly different approach.

Comments

Comments

  • david.bliss APEX Team OP 5 weeks ago (edited 4 weeks ago)

    The APEX_APP_OBJECT_DEPENDENCY api is available to scan an application or page for database object depdencies (tables, packages, etc). Using the p_options parameter will allow you to scan for errors only.

    See the following example:

    declare
    l_application_id number := 100;
    l_page_id        number := 5;
begin                                                                                                                   
    apex_util.set_workspace (                                                                                   
        p_workspace  => 'DBLISS' );                                                                       
                                                                                                                        
    apex_app_object_dependency.scan (                                                                                   
        p_application_id     => l_application_id,                                                                                    
        p_page_id            => l_page_id,                                                                                   
        p_options            => apex_app_object_dependency.c_option_errors );                                           
                                                                                                                        
    commit;                                                                                                             
end;

    After that you can query the views in the documentation, but something like the following will get you started:

    select property_name, error_message
  from apex_used_db_object_comp_props
 where application_id = 100
   and page_id = 5
   and error_message is not null