How to generate JSON file on demand from Apex 5.1 on 11.2 database

Hi,

generate the JSON object using APEX_JSON package https://docs.oracle.com/database/apex-5.1/AEAPI/INITIALIZE_CLOB_OUTPUT-Procedure.htm#AEAPI30243 and then serve it as an attachement as described here https://docs.oracle.com/cd/E14373_01/appdev.32/e13363/up_dn_files.htm#HTMAD008 in Create a Procedure to Download Documents section. Just instead of the command

-- download the BLOB
wpg_docload.download_file( Lob_loc );

write the JSON to http buffer using

htp.p( apex_json.get_clob_output );

If you expect larger files than 32k, you'll probably have to split the output to <=32k chunks.

Regards,

Pavel

Hi,

no, it's because of htp.p(rn) takes varchar2 as an input parameter, thus that 32k limit (32767 as the max varchar2 size, to be precise), however you can "serve" the clob chunk by chunk. Please, try the following code:

declare
  l_json_clob clob;

  k_amt constant integer := 4000;
  l_pos        integer := 1;
  l_json_chunk long; -- = varchar2(32000)
  l_json_length number;
begin

    apex_json.initialize_clob_output;
    apex_json.open_object;
    apex_json.open_array('array');
    for i in 1 .. 1500 loop --generate json larger than 32k
      apex_json.open_object;
      apex_json.write('hello_' || to_char(i), 'world_' || to_char(i));
   
      apex_json.close_object;
    end loop;

    apex_json.close_array;
    apex_json.close_object;

    l_json_clob := apex_json.get_clob_output();
   
    l_json_length :=  dbms_lob.getlength(l_json_clob);

    apex_debug.message('DEBUGMSG: Generated JSON length %s bytes.', l_json_length );
   
    htp.init;
    owa_util.mime_header( 'text/plain', FALSE,'UTF-8' );
    htp.p('Content-length: ' || l_json_length);
    htp.p('Content-Disposition: attachment; filename="my_json.txt"' );
    owa_util.http_header_close;
   
   

    loop
        l_json_chunk := dbms_lob.substr(l_json_clob, k_amt, l_pos);
     
        exit when l_json_chunk is null;
        htp.prn(l_json_chunk);
        l_pos := l_pos + k_amt;
    end loop;

  --htp.p(l_json_clob); -- if the output would be up to 32k, this could be used

  apex_json.free_output;
  apex_application.stop_apex_engine;
end;

Hello again,

I've just had one of the (rare) lightbulb moments and I think that even better approach would be to convert the CLOB to BLOB and then serve it using wpg_docload.download_file(); at once like this:

declare
  l_json_clob    clob;
  k_amt          constant integer := 4000;
  --l_pos          pls_integer := 1;
  l_json_length  pls_integer;


  --blob conversion variables
  l_blob          blob;
  l_dest_offset  pls_integer := 1;
  l_src_offset    pls_integer := 1;
  l_lang          pls_integer;
  l_warning      pls_integer := dbms_lob.no_warning;
  l_context      pls_integer := dbms_lob.default_lang_ctx;
begin
  apex_json.initialize_clob_output;
  apex_json.open_object;
  apex_json.open_array('array');
  for i in 1..1500 loop --generate json larger than 32k
    apex_json.open_object;
    apex_json.write('hello_' || to_char(i),'world_' || to_char(i) );

    apex_json.close_object;
  end loop;

  apex_json.close_array;
  apex_json.close_object;
  l_json_clob := apex_json.get_clob_output ();
  l_json_length := dbms_lob.getlength(l_json_clob);
  apex_debug.message('DEBUGMSG: Generated JSON length %s bytes.',l_json_length);
  apex_json.free_output;

--or even better convert clob to blob and serve it using wpg_docload
  dbms_lob.createtemporary(lob_loc => l_blob,cache => false);
  dbms_lob.converttoblob(dest_lob => l_blob,src_clob => l_json_clob,amount => dbms_lob.getlength(l_json_clob),dest_offset => l_dest_offset
,src_offset => l_src_offset,blob_csid => dbms_lob.default_csid,lang_context => l_context,warning => l_warning);

  --and download the blob
  htp.init;
  owa_util.mime_header('text/plain',false,'UTF-8');
  htp.p('Content-length: ' || l_json_length);
  htp.p('Content-Disposition: attachment; filename="my_json.txt"');
  owa_util.http_header_close;
  wpg_docload.download_file(l_blob);
  apex_application.stop_apex_engine;
end;

Regards,

Pavel