4 Replies Latest reply on Feb 20, 2019 10:59 PM by Mike Kutz

    [APEX5.1]Download CLOB

    Arthur R.


      I'm trying to make a procedure that makes a request to a REST API, puts that API answer (JSON) in a CLOB then proposes to the user to download a file named 'Text.json' which contains the CLOB content. I managed to get my JSON answer in the CLOB but I can't get the download part right.

      Here is my code:

      create or replace PROCEDURE DOWNLOAD_REST_CLOB (v_user_name IN VARCHAR2) AS
        l_clob    CLOB;
        v_mime  VARCHAR2(48):='application/json';
        apex_web_service.g_request_headers(1).name:= 'accept';
        apex_web_service.g_request_headers(1).value:= 'application/json';
        apex_web_service.g_request_headers(2).name:= 'content-type';
        apex_web_service.g_request_headers(2).value:= 'application/json';
        -- Get the XML response from the web service.
        l_clob := APEX_WEB_SERVICE.make_rest_request( [...] );
        -- set up HTTP header
              -- use an NVL around the mime type and 
              -- if it is a null set it to application/octect
              -- application/octect may launch a download window from windows
              owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE);
          -- set the size so the browser knows how much to download
          htp.p('Content-length: ' || DBMS_LOB.GETLENGTH(l_clob));
          -- the filename will be used by the browser if the users does a save as
          htp.p('Content-Disposition:  attachment; filename="Text.json"');
          -- close the headers            
          -- download the BLOB
          wpg_docload.download_file( l_clob );
      end ;


      This code gives me Error ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

      Thanks for reading.