export_query_to_blob

CREATE OR REPLACE FUNCTION export_query_to_blob(
    p_sql_query  IN VARCHAR2
) RETURN BLOB
IS
    l_context   apex_exec.t_context;
    l_export    apex_data_export.t_export;
    l_blob_data BLOB;
BEGIN
    BEGIN
        l_context := apex_exec.open_query_context(
            p_location    => apex_exec.c_location_local_db,
            p_sql_query   => p_sql_query
        );
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001, 'Failed to open query context: ' || SQLERRM);
    END;
 
    BEGIN
        l_export := apex_data_export.export (
            p_context   => l_context,
            p_format    => apex_data_export.c_format_csv
        );
    EXCEPTION
        WHEN OTHERS THEN
            apex_exec.close(l_context);
            RAISE_APPLICATION_ERROR(-20002, 'Failed to export data: ' || SQLERRM);
    END;
 
    l_blob_data := l_export.content_blob;
 
    apex_exec.close(l_context);
 
    RETURN l_blob_data;
EXCEPTION
    WHEN OTHERS THEN
        IF l_context IS NOT NULL THEN
            apex_exec.close(l_context);
        END IF;
        RAISE;
END export_query_to_blob;