Thursday, 3 April 2014

Getting around large exports with RTF report templates

If you are on a slow connection like me, you may find how much of a pain it is deploying apps that house some BI publisher report templates. It only takes a few templates before your app export becomes 10MB+ in size.

Perhaps in a future release, report templates will be treated in the same way as application images, in that you can add them as a script to supporting objects so that you don't need to export the templates each and every deployment.

If you haven't seen, apex_util has a procedure to download report queries, so by using that procedure coupled with storing your files in your own maintained table, you can avoid the issue of massive application exports.

First create a table to store the templates:

create table rtf_template(
    code varchar2(25) PRIMARY KEY
  , layout BLOB);
/


Then, add your template file to the table.

We will need two application items (or 1 will do if you identify the layout with exactly the same name as the query). So I've created: REPORT_NAME and REPORT_LAYOUT

Then create an on demand process, which I've named mine as: PRINT_DOC. The code looks like this:


declare

    l_file_as_clob CLOB ;
    l_layout BLOB;
    l_asdsad CLOB;

    l_dest_offset integer := 1;
    l_src_offset integer := 1;
    l_lang_context integer := dbms_lob.default_lang_ctx;
    l_warning integer;
BEGIN


    select layout into l_layout
    from rtf_template
    where code = :REPORT_LAYOUT;
    
    dbms_lob.createtemporary(lob_loc => l_file_as_clob, cache => false);
    
    dbms_lob.converttoclob(
        dest_lob => l_file_as_clob
      , src_blob => l_layout
      , 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);


    l_file_as_clob:= replace(l_file_as_clob,'+','%2B');
    l_file_as_clob:= replace(l_file_as_clob,'/','%2F');
    l_file_as_clob:= replace(l_file_as_clob,'=','%3D');

    --signature 2
    apex_util.download_print_document (
        p_file_name => :REPORT_NAME
      , p_content_disposition => 'ATTACHMENT'
      , p_application_id => :APP_ID
      , p_report_query_name => :REPORT_NAME
      , p_report_layout => l_file_as_clob
      , p_report_layout_type => 'rtf'
      , p_document_format => 'pdf'
    );
    
END;


Now, when ever you want a button to download a layout, you can just past the request parameter of: APPLICATION_PROCESS=PRINT_DOC, then set the two (or one) page items accordingly. A little like this:



It is a little extra work, but if it saves some headaches with deployments, why not ;-)

7 comments:

  1. I have created a table and stored the template in the table then created a application process to read the template and generate the PDF. when I run this it is not generating the pdf in correct format.

    if I attach the pdf to the sql and run it all works fine.

    here is my account details
    sampacc1
    pkpanda@yahoo.com
    password1
    application 64755 and run page 2 and click on edit then click the print button.

    ReplyDelete
  2. details are in apex.oracle.com
    here is my account details
    sampacc1
    pkpanda@yahoo.com
    password1
    application 64755 and run page 2 and click on edit then click the print button.

    ReplyDelete
    Replies
    1. Hi PKP, I took a quick look and it is indeed odd that an empty file is generated. I don't have the time to properly test at the moment, but will try and reproduce in a couple of weeks. In the meantime, an alternate solution I saw pop up can be found here: http://www.laureston.ca/2016/01/29/huge_apex_application/

      Delete
  3. thank you very much I have tried similar option but my issue is bit different. Since the application is for multiple companies I have requirement to have different template for the same report. That is why I tried your option which is more appropriate solution.

    ReplyDelete
  4. Hi Trent did you get a chance to have a look this issue.

    Prasanta

    ReplyDelete
    Replies
    1. Not yet. I haven't forgotten, just haven't got there yet. Soon...

      Delete
    2. Hi, I just took a look. I made a generic layout in Word and uploaded it to see if it was the report layout causing the issue - and the generic layout worked fine.

      So there must be something in the conversion to a CLOB causing the issue. I don't know exactly what, so you might need to go step by step figuring out which component of the report layout is causing the issue, to try and find an alternate approach.

      In my process, for replacing the '+, =, /', I originally got the idea from this blog: http://marcsewtz.blogspot.com.au/2008/06/one-question-about-pdf-printing-feature.html (but that demo application no longer available for download). But I think there's something in the CLOB being sent to the server causing the rendering engine being unable to properly interpret it.

      Let me know if you find the problem or a solution.

      Delete