Adding Page Items to All APEX Pages Automatically Using PL/SQL

Managing large Oracle APEX applications often means repeating the same setup across many pages. One common task is adding standard page items such as hidden variables, state trackers, or session-related fields to every page in an application.

Instead of creating these items manually page by page, PL/SQL gives you a faster, safer, and fully automated solution.

The PL/SQL Script

The following block loops through all pages of Application 210 and creates three hidden items on each page: P{page_id}_HELLO, P{page_id}_GOODBYE, and P{page_id}_STATUS.

It uses wwv_flow_api.create_page_item along with the workspace security group ID:

DECLARE
    CURSOR sel_workspace IS
        SELECT workspace_id
        FROM apex_applications
        WHERE application_id = 210;

v_workspace_id NUMBER;
    CURSOR sel_pages IS        
        SELECT page_id
        FROM apex_application_pages
        WHERE application_id = 210
          AND page_id <> 0;
BEGIN
    OPEN sel_workspace;
    FETCH sel_workspace INTO v_workspace_id;
    CLOSE sel_workspace;
    FOR i IN sel_pages LOOP
        APEX_UTIL.SET_SECURITY_GROUP_ID(v_workspace_id);
        wwv_flow_api.create_page_item(
            p_flow_id         => 210,
            p_flow_step_id    => i.page_id,
            p_item_sequence   => 10,
            p_name            => 'P' || i.page_id || '_HELLO',
            p_display_as      => 'NATIVE_HIDDEN',
            p_data_type       => 'VARCHAR2'
        );
        wwv_flow_api.create_page_item(
            p_flow_id         => 210,
            p_flow_step_id    => i.page_id,
            p_item_sequence   => 10,
            p_name            => 'P' || i.page_id || '_GOODBYE',
            p_display_as      => 'NATIVE_HIDDEN',
            p_data_type       => 'VARCHAR2'
        );
        wwv_flow_api.create_page_item(
            p_flow_id         => 210,
            p_flow_step_id    => i.page_id,
            p_item_sequence   => 10,
            p_name            => 'P' || i.page_id || '_STATUS',
            p_display_as      => 'NATIVE_HIDDEN',
            p_data_type       => 'VARCHAR2'
        );
    END LOOP;
END;
/
COMMIT;

Why This Approach Is Useful

1. Massive Time Savings

Creating items manually on hundreds of pages is slow and error-prone. This method does it in seconds.

2. Guaranteed Consistency

All pages get identical item names, types, and settings — perfect for global logic.

3. Ideal for Large or Evolving Apps

As your application grows, you can re-run or adapt the script anytime.

Need custom Oracle APEX or PL/SQL architecture for your enterprise? Contact me via LinkedIn or email for consulting services. Radwan Salameh | LinkedIn :)