Oracle APEX中多SQL查询数据合并导出为单个CSV报表

因为apex_data_export.export的p_context参数仅接受apex_exec.t_context类型，无法直接传入CLOB，所以可以跳过该API，直接将合并好的CSV格式CLOB转换为BLOB，再通过APEX原生工具触发下载。

实现步骤 #

1. 保留原有两个查询导出CLOB的逻辑，确保生成合法的CSV内容
2. 将合并后的CLOB转换为BLOB（APEX下载通常依赖BLOB格式）
3. 直接通过HTTP头输出触发文件下载

修改后的完整代码 #

DECLARE
  l_combined_clob CLOB;
  l_page_items_clob CLOB;
  l_report_clob CLOB;
  l_page_items_export apex_data_export.t_export;
  l_report_export apex_data_export.t_export;
  l_page_items_context apex_exec.t_context;
  l_report_context apex_exec.t_context;
  l_combined_blob BLOB;
  l_dest_offset INTEGER := 1;
  l_src_offset INTEGER := 1;
  l_lang_context INTEGER := dbms_lob.default_lang_ctx;
  l_warning INTEGER;
BEGIN
  -- 导出页面项为CLOB
  l_page_items_context := apex_exec.open_query_context(
    p_location => apex_exec.c_location_local_db,
    p_sql_query => '
      SELECT
        region AS REGION_NAME,
        item_name AS FIELD_NAME,
        APEX_UTIL.GET_SESSION_STATE(item_name) AS FIELD_VALUE
      FROM
        apex_application_page_items
      WHERE
        page_id = :APP_PAGE_ID
        AND application_id = :APP_ID
      ORDER BY region'
  );

  l_page_items_export := apex_data_export.export(
    p_context => l_page_items_context,
    p_format  => apex_data_export.c_format_csv,
    p_as_clob => true
  );
  l_page_items_clob := l_page_items_export.content_clob;
  apex_exec.close(l_page_items_context);

  -- 导出报表为CLOB
  l_report_context := apex_exec.open_query_context(
    p_location => apex_exec.c_location_local_db,
    p_sql_query => 'SELECT * FROM emp'
  );

  l_report_export := apex_data_export.export(
    p_context => l_report_context,
    p_format  => apex_data_export.c_format_csv,
    p_as_clob => true
  );
  l_report_clob := l_report_export.content_clob;
  apex_exec.close(l_report_context);

  -- 合并两个CLOB为完整的CSV内容
  l_combined_clob := 'Page Items Section:' || CHR(10) || l_page_items_clob || CHR(10) ||
                     'Report Section:' || CHR(10) || l_report_clob;

  -- 将CLOB转换为BLOB
  dbms_lob.createtemporary(l_combined_blob, true);
  dbms_lob.converttoblob(
    dest_lob     => l_combined_blob,
    src_clob     => l_combined_clob,
    amount       => dbms_lob.lobmaxsize,
    dest_offset  => l_dest_offset,
    src_offset   => l_src_offset,
    blob_csid    => nls_charset_id('AL32UTF8'),
    lang_context => l_lang_context,
    warning      => l_warning
  );

  -- 触发文件下载
  apex_application.stop_apex_engine;
  owa_util.mime_header('text/csv', false);
  htp.p('Content-Disposition: attachment; filename="combined_export.csv"');
  htp.p('Content-Length: ' || dbms_lob.getlength(l_combined_blob));
  owa_util.http_header_close;
  wpg_docload.download_file(l_combined_blob);

  -- 清理临时对象
  dbms_lob.freetemporary(l_combined_blob);
EXCEPTION
  WHEN OTHERS THEN
    IF dbms_lob.istemporary(l_combined_blob) THEN
      dbms_lob.freetemporary(l_combined_blob);
    END IF;
    RAISE;
END;
/

替代方案：临时表合并数据 #

如果希望继续使用apex_data_export.export的上下文模式，可以创建临时表统一存储两个数据集（不同结构的列补NULL），再查询导出：

-- 创建临时表（仅需执行一次）
CREATE GLOBAL TEMPORARY TABLE temp_export_data (
  section_name VARCHAR2(100),
  region_name VARCHAR2(100),
  field_name VARCHAR2(100),
  field_value VARCHAR2(4000),
  empno NUMBER,
  ename VARCHAR2(50),
  job VARCHAR2(50),
  mgr NUMBER,
  hiredate DATE,
  sal NUMBER,
  comm NUMBER,
  deptno NUMBER
) ON COMMIT DELETE ROWS;

-- 导出逻辑
DECLARE
  l_context apex_exec.t_context;
  l_export apex_data_export.t_export;
BEGIN
  -- 插入页面项数据
  INSERT INTO temp_export_data (section_name, region_name, field_name, field_value)
  SELECT 'Page Items Section', region, item_name, APEX_UTIL.GET_SESSION_STATE(item_name)
  FROM apex_application_page_items
  WHERE page_id = :APP_PAGE_ID AND application_id = :APP_ID;

  -- 插入报表数据
  INSERT INTO temp_export_data (section_name, empno, ename, job, mgr, hiredate, sal, comm, deptno)
  SELECT 'Report Section', empno, ename, job, mgr, hiredate, sal, comm, deptno
  FROM emp;

  -- 查询临时表并导出
  l_context := apex_exec.open_query_context(
    p_location => apex_exec.c_location_local_db,
    p_sql_query => 'SELECT * FROM temp_export_data'
  );

  l_export := apex_data_export.export(
    p_context => l_context,
    p_format => apex_data_export.c_format_csv,
    p_filename => 'combined_export.csv'
  );

  apex_data_export.download(p_export => l_export);
  apex_exec.close(l_context);
END;
/

注意事项 #

• 第一种方法更适配你当前的场景，无需调整数据结构，直接复用已生成的CSV内容
• 转换CLOB到BLOB时，需确保字符集与系统一致（示例用AL32UTF8，可按需修改）
• 临时表方案适合需要统一列结构导出的场景，需注意列的兼容性

内容的提问来源于stack exchange，提问作者Abneesh Kumar