I am trying to export data from the screen in CSV format by combining two SQL queries using a CLOB,but unable to export it using the apex_data_export.export API, as it does not accept a CLOB (l_combined_clob) directly as its p_context parameter. Is there any other approach?

Code

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;
BEGIN

  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);


  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);


  l_combined_clob := 'Page Items Section:' || CHR(10) || l_page_items_clob || CHR(10) ||
                     'Report Section:' || CHR(10) || l_report_clob;
l_combined_export := apex_data_export.export(
    p_context => l_combined_clob,
    p_format  => apex_data_export.c_format_csv,
    p_filename => 'combined_export.csv'
    -- p_as_clob => true
  );

  apex_data_export.download(
      p_export => l_combined_export
      );


END;

I am trying to export data from the screen in CSV format by combining two SQL queries using a CLOB,but unable to export it using the apex_data_export.export API, as it does not accept a CLOB (l_combined_clob) directly as its p_context parameter. Is there any other approach?

Code

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;
BEGIN

  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);


  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);


  l_combined_clob := 'Page Items Section:' || CHR(10) || l_page_items_clob || CHR(10) ||
                     'Report Section:' || CHR(10) || l_report_clob;
l_combined_export := apex_data_export.export(
    p_context => l_combined_clob,
    p_format  => apex_data_export.c_format_csv,
    p_filename => 'combined_export.csv'
    -- p_as_clob => true
  );

  apex_data_export.download(
      p_export => l_combined_export
      );


END;

• plsql
• oracle-apex

• 1 Convert your l_combined_clob to blob using APEX_UTIL.CLOB_TO_BLOB and then follow this answer to create the download page (just instead of APEX_DATA_EXPORT.DOWNLOAD) from the blob: stackoverflow/a/77299520/18667225 – Markus Commented Nov 20, 2024 at 7:49
• Thanks @Markus after converting from clob to blob able to export the report in required format. – Abneesh Kumar Commented Nov 26, 2024 at 6:25

2 Answers 2

I doubt this is supported with APEX_DATA_EXPORT, the format is no longer a CSV but a simple text file. This can be done with some pl/sql. Here is an example to put in a page process, either before render or after submit (not in a dynamic action). Note that you have to generate your own comma separated data by concatenating the columns.

owa_util.mime_header('text/plain',   FALSE);
htp.p('Content-Type: application/octet-stream');
htp.p('Content-Disposition: attachment;filename="My Text File.txt"');
owa_util.http_header_close;

htp.p('Emp Records');

FOR emp_rec IN (SELECT ename ||','||job as c FROM emp) LOOP
  htp.p(emp_rec.c);
END LOOP;

htp.p('Dept Records');

FOR dept_rec IN (SELECT dname ||','||loc as c FROM dept) LOOP
  htp.p(dept_rec.c);
END LOOP;

apex_application.stop_apex_engine;

DECLARE
l_blob              BLOB;
  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_lang_context      INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
  l_warning           INTEGER;
  l_mime              VARCHAR2(100) := 'application/octet-stream';
  p_file              VARCHAR2(255) := 'page_export.csv';
  l_dest_offset       INTEGER := 1;
  l_src_offset        INTEGER := 1;

BEGIN

  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);


  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);


  l_combined_clob := 'Page Items Section:' || CHR(10) || l_page_items_clob || CHR(10) ||
                     'Report Section:' || CHR(10) || l_report_clob;


DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
  DBMS_LOB.CONVERTTOBLOB(
    dest_lob     => l_blob,
    src_clob     => l_combined_clob,
    amount       => DBMS_LOB.GETLENGTH(l_combined_clob),
    dest_offset  => l_dest_offset,
    src_offset   => l_src_offset,
    blob_csid    => DBMS_LOB.DEFAULT_CSID,
    lang_context => l_lang_context,
    warning      => l_warning
  );


  owa_util.mime_header(l_mime, FALSE);
  htp.p('Content-Length: ' || DBMS_LOB.GETLENGTH(l_blob));
  htp.p('Content-Disposition: attachment; filename="' || p_file || '"');
  owa_util.http_header_close;

  wpg_docload.download_file(l_blob);

 
  DBMS_LOB.FREETEMPORARY(l_blob);


  apex_application.stop_apex_engine;
END;