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 ;-)