Wednesday, June 18, 2008

Verifying that the PDF print server works

Here's another posting on PDF printing in Application Express. Sometimes it's just a bit difficult to figure out what exactly is wrong if a report doesn't print properly. Sometimes all a customer gets in an empty / zero-length PDF file and no other indication on where it failed. A good place to look at are the log files of your OC4J server or J2EE container. Often BI Publisher or Apache FOP leave some clues there. But at other times there's just nothing helpful to find in these logs. And this could be due to your APEX instance not getting through to your print server which could be caused by not having configured the print server settings properly in Application Express.

One way to make sure your print server is up and running and configured properly is setting up a static HTML form that simulates what APEX is doing internally, i.e posting some XML data along with an XSL-FO stylesheet to a print server via HTTP and receiving back a PDF document as the response. If this works properly, you can at least be sure that your print server works. So then the next step would be to double-check the print server settings in APEX, make sure that network services are enabled when running 11g and ensure that you can reach your print server from your database server.

Here’s some HTML code that can be used to simulate or test your print server. Just replace the [host] and [port] values in the form tag with your actual values and then load the file into your browser and give it a try using e.g. the sample XML data and sample XSL-FO stylesheet below (just copy and paste in your form). This assumes you’re using Apache FOP, when using BI Publisher, also replace /fop/apex_fop.jsp with /xmlpserver/convert.

Static HTML form to test print server:
<html>
<body>
<form action="http://[host]:[port]/fop/apex_fop.jsp"  method="post" name="foptest">
<textarea name="xml" cols="80" rows="10">
</textarea><br />
<textarea name="template" cols="80" rows="10">
</textarea><br />
<input type="test" name="_xtype" value="xsl-fo" /><br />
<input type="test" name="_xf" value="pdf" /><br />
<input type="submit" />
<form>
</body>
</html>

Sample XML Data:
<?xml version="1.0" encoding="UTF-8"?>
<ROWSET>
<ROW>
   <FIRST_NAME>John</FIRST_NAME>
   <LAST_NAME>Doe</LAST_NAME>
</ROW>
<ROW>
   <FIRST_NAME>Jane</FIRST_NAME>
   <LAST_NAME>Doe</LAST_NAME>
</ROW>
</ROWSET>

Sample XSL-FO style sheet:
<?xml version = '1.0' encoding = 'utf-8'?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:ora="http://www.oracle.com/XSL/Transform/java/" xmlns:xdofo="http://xmlns.oracle.com/oxp/fo/extensions" xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.apps.xdo.template.rtf.XSLTFunctions" xmlns:xdoxliff="urn:oasis:names:tc:xliff:document:1.1" xmlns:xlink="http://www.w3.org/1999/xlink">
<xsl:template match="/">
   <fo:root>
      <fo:layout-master-set>
         <fo:simple-page-master master-name="master0" margin-left="84.6pt" margin-right="84.6pt" page-height="792.0pt" page-width="612.0pt" margin-top="36.0pt" margin-bottom="36.0pt">
            <fo:region-before region-name="region-header" extent="36.0pt"/>
            <fo:region-body region-name="region-body" margin-top="36.0pt" margin-bottom="36.0pt"/>
            <fo:region-after region-name="region-footer" extent="36.0pt" display-align="after"/>
         </fo:simple-page-master>
      </fo:layout-master-set>
      <fo:page-sequence master-reference="master0">
         <fo:title>Report</fo:title>
         <fo:static-content flow-name="region-header"/>
         <fo:static-content flow-name="region-footer"/>
         <fo:flow flow-name="region-body">
            <fo:block padding-bottom="0.25pt" padding-top="0.25pt">
               <fo:table start-indent="0.0pt" xdofo:table-summary="Template Table 1" xdofo:row-header-count="0">
                  <fo:table-column column-width="147.6pt"/>
                  <fo:table-column column-width="147.6pt"/>
                  <fo:table-header>
                     <fo:table-row keep-with-next="always">
                        <fo:table-cell padding-start="5.15pt" vertical-align="top" border-bottom="0.5pt solid #000000" border-end-color="#000000" padding-top="0.0pt" border-end-style="solid" border-start-color="#000000" padding-end="5.15pt" number-columns-spanned="1" border-top="0.5pt solid #000000" border-start-style="solid" height="0.0pt" border-end-width="0.5pt" padding-bottom="0.0pt" border-start-width="0.5pt" background-color="#ff4040">
                           <fo:block orphans="2" widows="2" linefeed-treatment="preserve" start-indent="0.0pt" text-align="start" padding-bottom="0.0pt" end-indent="0.0pt" padding-top="0.0pt">
                              <fo:inline height="12.0pt" font-family="Arial" white-space-collapse="false" font-size="12.0pt" font-weight="bold">First Name</fo:inline>
                           </fo:block>
                        </fo:table-cell>
                        <fo:table-cell padding-start="5.15pt" vertical-align="top" border-bottom="0.5pt solid #000000" border-end-color="#000000" padding-top="0.0pt" border-end-style="solid" border-start-color="#000000" padding-end="5.15pt" number-columns-spanned="1" border-top="0.5pt solid #000000" border-start-style="solid" height="0.0pt" border-end-width="0.5pt" padding-bottom="0.0pt" border-start-width="0.5pt" background-color="#ff4040">
                           <fo:block orphans="2" widows="2" linefeed-treatment="preserve" start-indent="0.0pt" text-align="start" padding-bottom="0.0pt" end-indent="0.0pt" padding-top="0.0pt">
                              <fo:inline height="12.0pt" font-family="Arial" white-space-collapse="false" font-size="12.0pt" font-weight="bold">Last Name</fo:inline>
                           </fo:block>
                        </fo:table-cell>
                     </fo:table-row>
                  </fo:table-header>
                  <fo:table-body>
                     <xsl:for-each select=".//ROW" xdofo:ctx="3">
                        <fo:table-row xdofo:repeat="R">
                           <fo:table-cell padding-start="5.15pt" vertical-align="top" border-bottom="0.5pt solid #000000" border-end-color="#000000" padding-top="0.0pt" border-end-style="solid" border-start-color="#000000" padding-end="5.15pt" number-columns-spanned="1" border-top="0.5pt solid #000000" border-start-style="solid" height="0.0pt" border-end-width="0.5pt" padding-bottom="0.0pt" border-start-width="0.5pt">
                              <fo:block xdofo:xliff-note="F , FIRST_NAME" orphans="2" widows="2" linefeed-treatment="preserve" start-indent="0.0pt" text-align="start" padding-bottom="0.0pt" end-indent="0.0pt" padding-top="0.0pt" height="0pt">
                                 <fo:inline height="12.0pt" font-family="Arial" white-space-collapse="false" font-size="12.0pt">
                                    <xsl:value-of select=".//FIRST_NAME" xdofo:field-name="FIRST_NAME"/>
                                 </fo:inline>
                              </fo:block>
                           </fo:table-cell>
                           <fo:table-cell padding-start="5.15pt" vertical-align="top" border-bottom="0.5pt solid #000000" border-end-color="#000000" padding-top="0.0pt" border-end-style="solid" border-start-color="#000000" padding-end="5.15pt" number-columns-spanned="1" border-top="0.5pt solid #000000" border-start-style="solid" height="0.0pt" border-end-width="0.5pt" padding-bottom="0.0pt" border-start-width="0.5pt">
                              <fo:block xdofo:xliff-note="LAST_NAME" orphans="2" widows="2" linefeed-treatment="preserve" start-indent="0.0pt" text-align="start" padding-bottom="0.0pt" end-indent="0.0pt" padding-top="0.0pt" height="0.0pt">
                                 <fo:inline height="12.0pt" font-family="Arial" white-space-collapse="false" font-size="12.0pt">
                                    <xsl:value-of select=".//LAST_NAME" xdofo:field-name="LAST_NAME"/>
                                 </fo:inline>
                              </fo:block>
                           </fo:table-cell>
                        </fo:table-row>
                     </xsl:for-each>
                  </fo:table-body>
               </fo:table>
            </fo:block>
         </fo:flow>
      </fo:page-sequence>
   </fo:root>
</xsl:template>
</xsl:stylesheet>

Posted by at 213 comments:

Friday, June 13, 2008

Using Dynamic Images in PDF Reports

One question about the PDF printing feature in APEX keeps popping up: how to include images in a PDF report. For static images, that's pretty straightforward. Just place them into your RTF layout using MS Word and BI Publisher Desktop. For dynamic images, that's a little more complicated, so here's how to do that:

When talking about dynamic images, what I mean are images that are stored in BLOB columns in database tables, like for example product images. To include those images in a PDF report, two things are needed: the image needs to be included in the generated XML representation of the report data and the RTF report layout needs to include instructions on what to do with the image information stored in the XML data. To get the BLOB values to be included in the XML data, they need to be base64 encoded. And in the RTF report layout an XSL-FO expression can be used to reference the image:

<fo:instream-foreign-object content-type="image/jpg">
<xsl:value-of select="IMAGE_ELEMENT"/>
</fo:instream-foreign-object>

To enter such a XSL-FO, edit your RTF layout in Word and then use the field-browser of the BI Publisher plug-in to edit the report column attributes.


I setup a sample application that illustrates how this works on apex.oracle.com, you can try it out here:

http://apex.oracle.com/pls/otn/f?p=50930:1 (logon as demo/demo123)

You can also download this application to try it out on your local instance or your own apex.oracle.com workspace. The download is available here:

Download PDF Image Demo Application

The application comes packaged with a table containing a BLOB column and a PL/SQL function that encodes the BLOB to base64. A sample RTF layout with the required XSL-FO snipped for the image transformation is also included. Once the application is installed in your own workspace, just upload a few images and try it out. One issue, which we'll address in the next version of Application Express, is the 32k limit on report columns. For this scenario, this means that only fairly small images are currently supported when using this technique with report queries or report regions. 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 as well.
Posted by at 17 comments:

Wednesday, June 11, 2008

Austrian Competence Center for Oracle APEX

Last week I was invited to be the keynote speaker at the opening event of the Austrian Competence Center for Oracle APEX in Vienna. The competence center is run by Sphinx IT Consulting and managed by Patrick Wolf, who’s well known in the APEX community and who’s the APEX Developer of the year 2007. With this effort Sphinx IT Consulting intends to promote and raise awareness for Oracle APEX and starting in August, they will be conducting APEX events and workshops on a regular basis.

Marc Sewtz, Patrick Wolf, Ingrid Kriegl and Carsten Czarski

More information about the Austrian Competence Center for Oracle APEX can be found at:

http://www.sphinx.at/it-consulting/de/home/
Posted by at 3 comments:

Thursday, May 15, 2008

Enabling end users to create their own PDF report layouts

With Oracle Application Express 3.0 we introduced advanced reporting printing using BI Publisher as the PDF rendering engine and using BI Publisher Desktop for designing sophisticated report layouts. In Oracle Application Express 3.1 we added the ability to create reports based on multiple queries. We also added a PL/SQL API that allows you to programmatically extend the built-in printing functionality. One interesting option this API offers is to dynamically link a report layout to a report query at runtime. Why is that interesting? Well, it can give your users more choices, e.g. they can pick which layout they prefer when printing reports. But more importantly, it allows your users to actually create their own report layouts, upload them into your own custom applications, and print the data in whichever format they like.

So what does that mean? How would this look like? Try out this application and see for yourself:

http://apex.oracle.com/pls/otn/f?p=49181:1 (username: demo, password: demo123)

The first page in this application shows a report, which can be filtered. And it can also be printed by clicking on "Print Report". When printing, you have a choice of which report layout you want to use. There are two report layouts pre-loaded, but you can also create your own, and upload them via manage report layouts. This takes you to a page where you can look at the report layouts already loaded, and upload you own ones. To create a report layout, you need to have BI Publisher Desktop installed (and MS Word), once that's installed, just download the XML data of your report on page 1, import this into MS Word, use the BI Publisher Desktop Word Plug-In to create your own custom layout, save as RTF and load it up into the application. Once the RTF layout is loaded, you'll find it in the select list on page 1 and can print out your report with your own personalized layout.

Want to try this at home? You can download the application, packaged up with the underlying DDL and including the two sample layouts here.
Posted by at 11 comments:

Where Parking Rules the Days, a Little Miracle

I stumbled upon this article in the New York Times today, and couldn't believe what I was reading. It's about my neighborhood, and the parking rules we have to obey here in New York. The article starts as follows:

"It is a routine so ingrained in the New Yorker's life that it seems to have always existed: And on the sixth day, God created man. And on the seventh day, while God rested, man had to go move his car because of alternate-side parking rules."

Well, looks like this summer, at least in my neighborhood, this will be suspended. And this will in fact be life-altering. I will have soo much spare time one my hands, I won't know what to do, I might even get to blog more often. So read on and enjoy:

Parking Rules the Days, a Little Miracle
Posted by at 1 comment:

Sunday, April 20, 2008

APEX Advanced Training in Munich, Germany (June 2-4)




I'm happy to announce that I've been invited to speak at the APEX Advanced Training in Munich, Germany, June 2nd to June 4th. This is a three day training class held by Denes Kubicek, Patrick Wolf and Dietmar Aust. All three of them are well know APEX developers and very active participants in the APEX community and so I was particularly pleased to accept their invitation to talk about what we're working on for our next release of Oracle Application Express and to demonstrate our newest release of Oracle SQL Developer.


I'll also be participating in the two evening Q&A sessions, helping to answer customer questions and hoping to learn a few things about the experience our customers have with APEX and hearing about their ideas and need for future versions.


Please visit the training web site Opal Consulting for more information on this event.


Posted by at No comments:

Friday, April 18, 2008

Generating CSV files and storing them in the database

This week I faced the following problem: we wanted to generate CSV files based on SQL queries, and store them in the database rather than directly downloading them from a report region. We also wanted a report, from which you could choose which CSV file to download and lastly, the rows in those CSV files could potentially exceed the 32k limit that we currently have with standard report regions.
In Application Express 3.1 we made it really easy to store files in tables with a BLOB column and to build reports on those tables with a download link that allows downloading the files to your client. So this feature takes care of storing and downloading the CSV files. No I only needed to write some logic to actually generate the CSV file and insert it into a table.

The following function does that. You supply a query, then the query is parsed and executed. And then the column headings are derived from the describe table and written to the CSV file, followed by the result set. And in the end, the file is returned back as a BLOB. This BLOB can then be written to a table, or otherwise processed.


create or replace function get_report (
p_query varchar2
) return blob is

l_cursor integer;
l_cursor_status integer;
l_col_count number;
l_desc_tbl sys.dbms_sql.desc_tab2;
l_col_val varchar2(32767);

l_report blob;
l_raw raw(32767);
begin

-- open BLOB to store CSV file
dbms_lob.createtemporary( l_report, FALSE );
dbms_lob.open( l_report, dbms_lob.lob_readwrite );

-- parse query
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor, p_query, dbms_sql.native);
dbms_sql.describe_columns2(l_cursor, l_col_count, l_desc_tbl );

-- define report columns
for i in 1 .. l_col_count loop
dbms_sql.define_column(l_cursor, i, l_col_val, 32767 );
end loop;

-- write column headings to CSV file
for i in 1 .. l_col_count loop
l_col_val := l_desc_tbl(i).col_name;
if i = l_col_count then
l_col_val := '"'||l_col_val||'"'||chr(10);
else
l_col_val := '"'||l_col_val||'",';
end if;
l_raw := utl_raw.cast_to_raw( l_col_val );
dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw );
end loop;

l_cursor_status := sys.dbms_sql.execute(l_cursor);

-- write result set to CSV file
loop
exit when dbms_sql.fetch_rows(l_cursor) <= 0;
for i in 1 .. l_col_count loop
dbms_sql.column_value(l_cursor, i, l_col_val);
if i = l_col_count then
l_col_val := '"'||l_col_val||'"'||chr(10);
else
l_col_val := '"'||l_col_val||'",';
end if;
l_raw := utl_raw.cast_to_raw( l_col_val );
dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw );
end loop;
end loop;

dbms_sql.close_cursor(l_cursor);
dbms_lob.close( l_report );

-- return CSV file
return l_report

end;


To showcase this function, I create an APEX application that has a report page for downloading the CSV files. And a form page that lets you create CSV files based on the demo tables that ship with our APEX sample application. You can try this out here:

http://apex.oracle.com/pls/otn/f?p=36083:1:

logon as demo/demo123

If you’d like to try this on your local APEX instance, review the implementation, and use this for your own application, you can download the packaged application here: CSV and BLOB Demo Application.

This file contains the application along with the supporting objects (get_report function and other required database objects). It does not include the demo tables, so you’ll have to have the APEX default sample application installed in your workspace. If you don’t have this application, click on Create Application -> Create Demonstration Application.

Some other interesting usages for this code could be to generate XML instead of CSV. You would only have to replace the delimiters, etc with XML tags using the column names. This XML file could then be used together with our new PL/SQL Print API, i.e. you could send it off to BI Publisher or Apache FOP, along with a XSL-FO stylesheet or RTF laytout, and produce PDF, Word or Excel files, and either download them to the client or store them in BLOB columns the same way as above. More on this in my next posting.
Posted by at 14 comments:
Subscribe to: Posts (Atom)