Thursday, February 23, 2012

Dynamic Images in PDF - What 32k Limit?

Looks like it's time to follow up on a Blog posting I wrote in 2008 about including dynamic images in PDF reports. After being called out on that good old 32k limit in a recent Blog posting by Roel Hartman, and reading through the recent OTN Forum posts and tweets on that topic, it would be rude not to respond ;-)

While I did state that we were looking to lift this 32k limit, this has not yet made it in, i.e. the limit is still in place. However in my posting I was also saying that if the XML data is generated by some other way, and the PDF rendering is done using the print API, then the use of larger images would be possible. And that is certainly the case, so let's take a look at how this could be done.

The key piece is our Print API (apex_util.download_print_document and apex_util.get_print_document), with this API you can generate PDF and other documents through a simple PL/SQL API call. This API is taking care of all the communication with BI Publisher or FOP for you. The apex_util.get_print_document API can be called to generate and retrieve the print document as a BLOB in the database for further processing, like storing the document in tables, etc. The apex_util.download_print_document API can be called in an APEX page process to generate and download the print document straight to your client. Both APIs have three different signatures, they allow for programmatically downloading report queries while dynamically associating stored report layouts at runtime, downloading report queries with custom templates stored in your own tables, and generating PDF based on your own custom XML using your own custom templates.

This last scenario is what we want to use for our dynamic images sample, i.e. we're going to generate the report data in XML format ourselves, thus getting around that 32k limit and store the report data in a CLOB. We're then also going to store our report layouts in our own tables, and query them up dynamically at runtime. The API for this looks as follows (for further details, reference the Oracle Application Express API Reference):

-- -----------------------------------------------------------------------------------------------
procedure download_print_document (
--
-- This procedure initiates the download of a print document using XML based report data and RTF or XSL-FO based report layout.
--
-- Arguments:
-- p_file_name Defines the filename of the print document
-- p_content_disposition: Specifies whether to download the print document or display inline ("attachment", "inline")
-- p_report_data: XML based report data
-- p_report_layout: Report layout in XSL-FO or RTF format
-- p_report_layout_type: Defines the report layout type, that is "xsl-fo" or "rtf"
-- p_document_format: Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml"
-- p_print_server: URL of of the print server. If not specified, the print server will be derived from preferences
-- example: http://myserver.mydomain.com:8888/xmlpserver/convert
--
p_file_name in varchar,
p_content_disposition in varchar default 'attachment',
p_report_data in clob,
p_report_layout in clob,
p_report_layout_type in varchar2 default 'xsl-fo',
p_document_format in varchar2 default 'pdf',
p_print_server in varchar2 default null
);


Now of course the question is, how do we get our data into XML format, if we don't have APEX take care of that for us, and where do we get the images from and how to we include them in this API call? Getting our data into XML format is the easy part, let's say you want to generate XML data for this query:

select * from emp

You could simply call dbms_xmlgen.getxml, supply the query and retrieve the XML back as a CLOB:

select dbms_xmlgen.getxml('select * from emp') xml_data from dual

Now assuming you have your images stored in a BLOB column, you would need to convert the images into base64 encoded data in order to include them in your XML. I have a blob2clobase64 function included in my sample application, which basically does just that. It should be noted though, that ultimately our API call is going to reach out to BI Publisher via utl_http, meaning you're going to do send your XML data via http to another service, which requires certain characters to be encoded, you can find more information on this here:

http://en.wikipedia.org/wiki/Base64

APEX typically takes care of this for you, in our scenario though, you generate the XML yourself, so you need to encode the base64 data on your own. I have taken care of this in blob2clobase64 function that ships with my sample app.

create or replace function blob2clobase64 (
p_blob in blob,
p_escape in char default 'N'
) return clob is
l_pos pls_integer := 1;
l_buffer varchar2 (32767);
l_res clob;
l_lob_len integer := dbms_lob.getlength (p_blob);
begin
dbms_lob.createtemporary (l_res, true);
dbms_lob.open (l_res, dbms_lob.lob_readwrite);
loop

l_buffer := utl_raw.cast_to_varchar2 (
utl_encode.base64_encode (
dbms_lob.substr (p_blob, 48, l_pos)
)
);
if (p_escape = 'Y') then
l_buffer := replace(l_buffer,'+','%2B');
l_buffer := replace(l_buffer,'/','%2F');
l_buffer := replace(l_buffer,'=','%3D');
end if;

if length (l_buffer) > 0 then
dbms_lob.writeappend (l_res, length (l_buffer), l_buffer);
end if;
l_pos := l_pos + 48;

exit when l_pos > l_lob_len;

end loop;
return l_res;

end blob2clobase64;

So in my example, using a table called eba_pdfimg_images, that includes the data and images I want to print, the XML generation would look like this:
   -- generate XML data
for c2 in (
select dbms_xmlgen.getxml('
select
id,
file_name,
mime_type,
description,
blob2clobase64(image,''Y'') image
from eba_pdfimg_images
') xml_data from dual
) loop
l_xml_data := c2.xml_data;
end loop;

Once I query up my report layout, converted that to a CLOB, and generated my XML data with images as outlined above, I can simply call our API:
    apex_util.download_print_document (
p_file_name => 'image_demo',
p_content_disposition => 'ATTACHMENT',
p_report_data => l_xml_data ,
p_report_layout => l_print_layout,
p_report_layout_type => 'rtf',
p_document_format => :P1_FORMAT
);
This will generate a file called image_demo, and based on whether you choose to generate Word or PDF, you'll get ‘.rtf' or ‘.pdf' file back.

So that's it. You can generate your RTF templates with the BI Publisher Word Plug-In as you normally would. And you can take your report query SQL and wrap it into a dbms_xmlgen.getxml call, and then you'll be able to include much larger images in your PDF dynamically. Want to give it a try? Here's my update sample app (logon as demo/demo123):

Dynamic Images in PDF Reports

I uploaded three images, and two report layouts. If you're trying out your own images or templates, I ask that you please remove when done, and report back if you encounter any issues. Also, if you want to try this out locally, you can download the app (sample_pdf_with_images.sql) along with the RTF layouts and images here:

Download Sample App

Please note, this app requires BI Publisher to be configured as your print server, and the current release of APEX 4.1.1 (my sample is an APEX 4.1.1 export, but you should be able to use the same technique on APEX 4.0 and above).

7 comments:

  1. I think here comes APEX_WEB_SERVICE.BLOB2CLOBBASE64 in pretty handy.

    ReplyDelete
  2. Hi Peter, that's certainly an option, too, you'll just have to make sure to escape your special character at a different point then.

    ReplyDelete
  3. That may be a good enhancement of that function for APEX 4.2 ?

    ReplyDelete
  4. Very nice. The best thing about this solution (apart from lifting the 32k limit), is that you even can print reports in batch!

    ReplyDelete
    Replies
    1. Hi Peter,

      I have tried to import your sample into Apex 4.0.2.00.07 and I get the following error message:

      NOT COMPATIBLE (Your export may contain calls not supported by your application version.)

      I am aware that you sample is for Apex 4.1.1. However you you be so kind as to point me in the right direction to the areas I may need to modify so that it will import into apex 4.0.

      Thank you

      Delete
  5. I know this is a little old but you may be interested in the this.

    Your example and application work great... except for one thing. If a special character such as &"<>' are used, the PDF will not open.

    Any suggestions for a fix?

    ReplyDelete
  6. Somewhat tangential to your post, but is there a way to use apex_util.get_print_document outside of apex? I'd like to run a particularly long running report as a background job, but when I try to call apex_util.get_print_document outside of apex, it returns null.

    ReplyDelete