APEX

I had an idea rejected to add a Download in Background option to Interactive Grids. https://apexapps.oracle.com/pls/apex/r/apex_pm/ideas/details?idea=FR-4678

I need to create a background process that stores a blob in a table, but the download needs to reflect the UI state of the interactive grid (if anyone applied manual filters, or hid columns). I am being told by the APEX team this is simple, so the idea can be cancelled.

When I try, I see 18 rows in my Interactive Grid with a manual filter applied, but the blob when downloaded has 3,000 rows in it.

How do I write a background process which can store a BLOB in a table to ensure the BLOB reflects any manually applied filters, column hiding etc. Best I can do is below, but when my IG is filtered and showing 18 rows, the download includes all 3,000 rows in it.

DECLARE
    l_export       apex_data_export.t_export;
    l_region_id    NUMBER;
    l_report_id    NUMBER;
    l_current_exec apex_background_process.t_execution;
    l_execution_id NUMBER;
    l_file_name    VARCHAR2(255) := 'pvcompliance_icsr_intake_' || REPLACE(LOWER(:P14_INTAKE_ROUTE), ' ', '_') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.csv';
    l_error_msg    VARCHAR2(4000);
BEGIN
    -- Get the current background execution ID 
    l_current_exec := apex_background_process.get_current_execution;
    l_execution_id := l_current_exec.id;

    -- Insert initial row with RUNNING status (ensures row exists for updates)
    INSERT INTO apex_user_exports (execution_id, status, created_by, filename, num_rows)
    VALUES (l_execution_id, 'RUNNING', :APP_USER, l_file_name, :P14_NUM_ROWS);

    COMMIT;

    -- Get region ID using binds (ensure :APP_PAGE_ID is fully spelled)
    SELECT region_id
    INTO l_region_id
    FROM apex_application_page_regions
    WHERE application_id = :APP_ID
    AND page_id = :APP_PAGE_ID  -- Fixed: Full bind variable
    AND static_id = 'intake_region';

    -- Get the *current* report ID (captures last viewed saved report with its state/filters)
    l_report_id := APEX_IG.GET_LAST_VIEWED_REPORT_ID(
        p_page_id   => :APP_PAGE_ID,
        p_region_id => l_region_id  -- Fixed: Use NUMBER region_id, not static_id
    );

    -- Fallback to primary/default if no last viewed report
    IF l_report_id IS NULL THEN
        SELECT report_id
        INTO l_report_id
        FROM apex_appl_page_ig_rpts  -- Note: View name is case-sensitive in some envs
        WHERE application_id = :APP_ID
        AND page_id = :APP_PAGE_ID
        AND type = 'PRIMARY';  -- Fixed: Correct column for default report
    END IF;

    -- Export using current report ID (applies saved filters/sorts/etc.)
    l_export := apex_region.export_data(
        p_format      => apex_data_export.c_format_csv,
        p_page_id     => :APP_PAGE_ID,  -- Page ID (not app ID)
        p_component_id => l_report_id,  -- Now uses current/primary report
        p_as_clob     => FALSE,         -- For BLOB output
        p_region_id   => l_region_id
    );

    -- Update with BLOB and set to COMPLETE
    UPDATE apex_user_exports
    SET export_blob = l_export.content_blob,  -- Matches BLOB
        status = 'COMPLETE'
    WHERE execution_id = l_execution_id;

    COMMIT;  -- Commit the update

EXCEPTION
    WHEN OTHERS THEN
        l_error_msg := SQLERRM;
        
        -- Log error (row should exist from initial INSERT)
        UPDATE apex_user_exports
        SET status = 'ERROR', 
            error_msg = l_error_msg
        WHERE execution_id = l_execution_id;

        COMMIT;  -- Commit error update
        
        RAISE;
END;