How to Create Page Items Dynamically in Oracle APEX

Posted inAPEX, APEX Tutorials

Dynamic item creation in Oracle APEX can greatly enhance application flexibility, allowing you to programmatically add items to regions based on specific requirements. This blog explains the functionality and implementation of the create_page_item procedure, which dynamically creates APEX page items using a well-structured PL/SQL process.

Understanding the Procedure

The create_page_item procedure is designed to:

  1. Prepare a dynamic item’s label and sequence.
  2. Identify the target region and its sequence.
  3. Retrieve the appropriate display template.
  4. Use the wwv_flow_api.create_page_item (Undocumented) API to create the page item dynamically.

Full Procedure Code

Here is the complete procedure code for reference:

Copy Code
CREATE OR REPLACE PROCEDURE create_page_item(
    p_item_name IN VARCHAR2
) AS
    l_region_id NUMBER;
    l_template_id NUMBER;
    l_item_seq NUMBER;
    l_field_label VARCHAR2(500);
    l_sec_num NUMBER;
BEGIN
    -- Preparing APEX Item label and sequence
    BEGIN
        l_field_label := INITCAP(REPLACE(SUBSTR(p_field_name, INSTR(p_item_name,'_',1,2)+1),'_',' '));
        l_sec_num := TO_NUMBER(SUBSTR(p_field_name, INSTR(p_item_name,'_',1,2)-1,1));
    EXCEPTION WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Unable to prepare label and section from the Field Item: ' || SQLERRM);
    END;

    -- Determining Display Sequence
    BEGIN
        SELECT MAX(display_sequence)+10
        INTO l_item_seq
        FROM apex_application_page_items
        WHERE region_id = l_region_id;
    EXCEPTION WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20002, 'Unable to find dynamic item display sequence: ' || SQLERRM);
    END;

    -- Fetching Template ID
    BEGIN
        SELECT template_id
        INTO l_template_id
        FROM apex_application_templates
        WHERE application_id = gv_app_id
        AND template_type = 'Item Label'
        AND template_name = 'Optional - Floating';
    EXCEPTION WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20003, 'Unable to find template for dynamic item: ' || SQLERRM);
    END;

    -- Creating the APEX Page Item
    BEGIN
        wwv_flow_api.create_page_item(
            p_id => NULL,
            p_flow_id => gv_app_id,
            p_flow_step_id => gv_app_page_id,
            p_name => p_field_name,
            p_item_sequence => l_item_seq,
            p_item_plug_id => wwv_flow_api.id(l_region_id),
            p_prompt => l_field_label,
            p_display_as => 'NATIVE_TEXT_FIELD',
            p_cSize => 30,
            p_field_template => wwv_flow_api.id(l_template_id),
            p_item_template_options => '#DEFAULT#',
            p_attribute_01 => 'N',
            p_attribute_02 => 'N',
            p_attribute_04 => 'TEXT',
            p_attribute_05 => 'BOTH'
        );
    COMMIT;
    EXCEPTION WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20004, 'Error in create_page_item: ' || SQLERRM || dbms_utility.format_error_backtrace);
    END;
END create_page_item;
/

Calling the Procedure

To call this procedure, follow the steps below:

Copy Code
BEGIN
    create_page_item(p_item_name => '2_DYNAMIC_DETAIL_FIELD');
END;
/

Replace 2_DYNAMIC_DETAIL_FIELD with the appropriate field name that follows the required naming convention.

Key Components of the Code

  • Preparing Item Label and Section Number
Copy Code
l_field_label := INITCAP(REPLACE(SUBSTR(p_field_name, INSTR(p_field_name,'_',1,2)+1),'_',' '));
l_sec_num := TO_NUMBER(SUBSTR(p_field_name, INSTR(p_field_name,'_',1,2)-1,1));
  • Item Label: Extracts and formats the field name for a user-friendly label.
  • Section Number: Parses the input field name to determine the section for item placement.
  • Determining Display Sequence
Copy Code
SELECT MAX(display_sequence)+10
INTO l_item_seq
FROM apex_application_page_items
WHERE region_id = l_region_id;
  • Calculates the next display sequence for the new item to ensure proper placement.
  • Fetching the Template ID
Copy Code
SELECT template_id
INTO l_template_id
FROM apex_application_templates
WHERE application_id = gv_app_id
AND template_type = 'Item Label'
AND template_name = 'Optional - Floating';
  • Retrieves the template ID for the item label, ensuring a consistent UI.
  • Creating the APEX Page Item
Copy Code
wwv_flow_api.create_page_item(
     p_id =>NULL
    ,p_flow_id =>gv_app_id
    ,p_flow_step_id =>gv_app_page_id
    ,p_name =>p_field_name
    ,p_item_sequence =>l_item_seq
    ,p_item_plug_id =>wwv_flow_api.id(l_region_id)
    ,p_prompt =>l_field_label
    ,p_display_as =>'NATIVE_TEXT_FIELD'
    ,p_cSize =>30
    ,p_field_template =>wwv_flow_api.id(l_template_id)
    ,p_item_template_options=>'#DEFAULT#'
    ,p_attribute_01 =>'N'
    ,p_attribute_02 =>'N'
    ,p_attribute_04 =>'TEXT'
    ,p_attribute_05 =>'BOTH'
);
  • Uses the wwv_flow_api.create_page_item (Undocumented) API will define and add the item to the application dynamically.
  • Committing Changes

COMMIT;

  • Finalizes the creation process by committing the changes to the database.

Use Cases

  • Dynamic Forms: Generate form fields on the fly based on user roles or data requirements.
  • Custom Dashboards: Add new fields dynamically to display metrics or user inputs.
  • Rapid Prototyping: Quickly create and test new fields during development.

Conclusion

The create_page_item procedure exemplifies the power of PL/SQL in Oracle APEX, enabling dynamic and flexible UI development. By programmatically creating items, developers can save time, enhance maintainability, and deliver highly adaptable applications.

Tags: