9 Replies Latest reply on Dec 10, 2018 3:24 PM by Raviteja

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

    Raviteja

      Hi All,

                I'm on Apex 5.1 and 11.2 database. I have a requirement where I need to generate JSON file on fly when user initiates it (with a button click) from Apex application page.

      I had a report with query based on pipelined function that accepted the parameters and generated output from which users used to download CSV data,

      Now, instead of showing report output, I need to generate JSON file and provide download option for that file.

              May I know how I can achieve that?

       

      Thank you

        • 1. Re: How to generate JSON file on demand from Apex 5.1 on 11.2 database

          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

          • 2. Re: How to generate JSON file on demand from Apex 5.1 on 11.2 database
            Mint-Innit

            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.

            • 3. Re: How to generate JSON file on demand from Apex 5.1 on 11.2 database
              Pavel_p

              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

              1 person found this helpful
              • 4. Re: How to generate JSON file on demand from Apex 5.1 on 11.2 database
                Raviteja

                Thanks everyone for you posts.

                 

                Pavel_p,

                               My output is is most likely going to be over 32k. Is the 32k limit due to clob? Is there a way I can combine as one JSON for users to download?

                 

                Thank you

                 

                              

                • 5. Re: How to generate JSON file on demand from Apex 5.1 on 11.2 database
                  Pavel_p

                  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;
                  
                  1 person found this helpful
                  • 6. Re: How to generate JSON file on demand from Apex 5.1 on 11.2 database
                    Raviteja

                    Pavel_P,

                                   You are awesome. Worked like a charm and thank you so much for your time (that too, over a weekend) and help, I really, really appreciate it.

                    • 7. Re: How to generate JSON file on demand from Apex 5.1 on 11.2 database
                      Pavel_p

                      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

                      • 8. Re: How to generate JSON file on demand from Apex 5.1 on 11.2 database
                        Pavel_p

                        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

                        1 person found this helpful
                        • 9. Re: How to generate JSON file on demand from Apex 5.1 on 11.2 database
                          Raviteja

                          Thank you so much Pavel, Will do.