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

Monday, February 6, 2012

Mobile APEX Apps – Next Steps

It's been a few weeks since my last Blog posting on mobile development, or any topic for that matter. I've been busy doing Cloud development and talking about it at UKOUG and NYOUG. Then there's the ongoing work on APEX 4.1.1, which actually won't ship with jQuery Mobile bundled in, mainly due to jQuery Mobile's minimum requirement of jQuery 1.6.4 while APEX 4.1 includes jQuery 1.6.2, and a patch release won't be the right time to move that up. But as my previous mobile samples and especially the mobile version of the Oracle Learning Library show, it's certainly possible to use jQuery Mobile in APEX 4.1 today. So the full integration of jQuery Mobile with APEX, incl. declarative / wizard-driven support for building mobile web applications is currently planned for APEX 4.2, which actually was the subject of some recent meetings in Vienna, Austria.

While we wait for (and work on) APEX 4.2, I'm planning to continue blogging about what can be done with jQuery Mobile and APEX today, publish some ideas and gather feedback from our users on what they'd like to see in terms of mobile support in APEX 4.2. And one area that I found increasingly cumbersome in my mobile development efforts was to quickly try out my latest mobile creations on my iPhone and iPad. I kept sending emails to myself with the URL to my mobile APEX apps, bookmarked those URLs, but then ended up constantly creating new apps, requiring new boomarks. I also tried to just cloud-sync my bookmarks between my MacBook and mobile devices, but that wasn't ideal either.

Then I thought if I have these difficulties while developing, end users might have similar issues. For example, some of our new Cloud-based productivity applications will have a link that takes the user to the mobile version of a page or app. And that's great if they're already on a mobile device, but what if for example, a users sits at his desks, runs some analysis, and then needs to run to a meeting and he wants to take the results with him on his mobile device? Well, one possibility would be to just display a QR code on the desktop version of his report, and then allow him to scan that QR Code with his mobile device, which would then take him straight to the mobile version of that report.

Sounds like a good idea? Here's how this could look like, just open the following page on your desktop or laptop:


It's a very simple Interactive Report on the EMP table, and right next to it, in the sidebar, I show a QR code, which can be scanned with one of the many bar-code readers available for smartphones, like RedLaser.

Once scanned, you will typically be prompted by your smartphone whether you want to go to the URL, and if you proceed, your mobile browser opens and takes you to this page:


To generate the QR Code I wrote a very simple QR Code Generator APEX Region Plug-In that takes in the text your want to encode, and the width and height of the QR Code image and then calls out to Google to have the actual image generated.

If you would like to take a look at the app, see how it's done and use some ideas for your own apps, you can download the application here: