Probably this can be achived with Interactive Prints plugin: https://apex.world/ords/f?p=100:710:30568029236928::::P710_PLG_ID:SI.MIRMAS.REGION2XSLTREPORT

If this is interesting for you, test plugin with your report.

If Excel and Word outputs will work fine with your report, You'll need to write XSLT for transforming xml with report data to JSON. It should be simple XSLT. XML is generated by plugin.

HTH

Miro

I do something similar with XML files. The user can click a button and an xml file of the report is emailed to them (so not a download direct from the page).

I achieve this by having page processing which calls a stored procedure with appropriate parameters, the procedure then generates the XML file using PL/SQL and drops it on the database file system. It then uses our existing framework to insert a record into our email subsystem table with the file as an attachment. So, it's built on existing building blocks that were already available to me.

Another option might be to generate your JSON and store it as a file in a blob column somewhere. Then you could have an IR with download links directly to the blob in the usual way. This works well for downloading documents from a table for example.

Cheers.

Hi,

glad to help. Give the code more thorough testing though - I wrote it "on the fly" and (as you pointed out) during the weekend.

Regards,

Pavel

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