Skip to Main Content

APEX

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

How to export/download clob on button click in Oracle APEX without creating file on the server?

Kinjan BhavsarMay 23 2020 — edited May 25 2020

Hi,

I have a CLOB column in my table which stores a large JSON content in it. I want to convert this clob to file and download it on button click in Oracle APEX. I have tried the following approach to convert it into BLOB and then download the file but it shows processing icon and doesn't download the file.

Steps to follow

  1. Create a new page.
  2. Add a new region and a button on it.
  3. Create a process that runs when button is clicked.
  4. The process has the following logic

DECLARE

  l_blob         BLOB;

  l_dest_offset  PLS_INTEGER := 1;

  l_src_offset   PLS_INTEGER := 1;

  l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;

  l_warning      PLS_INTEGER := DBMS_LOB.warn_inconvertible_char;

  l_clob_content clob;

  v_file_name varchar2(4000) := 'Input.json';

BEGIN

    select file_clob into l_clob_content

    from test_table

    where id = 23;

   

    DBMS_LOB.createtemporary(lob_loc => l_blob,

                             cache => TRUE);

    DBMS_LOB.converttoblob(dest_lob => l_blob,

                           src_clob => l_clob_content,

                           amount => DBMS_LOB.lobmaxsize,

                           dest_offset => l_dest_offset,

                           src_offset => l_src_offset,

                           blob_csid => DBMS_LOB.default_csid,

                           lang_context => l_lang_context,

                           warning => l_warning);

    sys.htp.init;

    sys.owa_util.mime_header('application/json',false);

    sys.htp.p('Content-length:'||sys.dbms_lob.getlength(l_blob));

    sys.htp.p('Content-Disposition: attachment; filename="' || V_FILE_NAME ||'"');

    sys.htp.p('Cache-Control: max-age=3600');

    sys.owa_util.http_header_close;

    sys.wpg_docload.download_file(l_blob);

    apex_application.stop_apex_engine;

END;

Can someone suggest what am I doing it wrong?

This post has been answered by fac586 on May 24 2020
Jump to Answer
Comments
Post Details
Added on May 23 2020
16 comments
5,159 views