Due to the high interest in the topic of Advanced Printing with Oracle Application Express, I signed up to present this topic at some additional upcoming conferences. The actual presentations will have different focus areas, but they all will cover the basics of configuring printing in Application Express, using BI Publisher and Apache FOP and showcase some of the advanced features such as Web Services based integration with BI Publisher, the APEX print API, report rescheduling and emailing, etc. Please see below for the current conference schedule, and please check the conference websites for updated information.
New York Oracle Users Group - General Meeting
Date: December 10, 2008
Location:
St. John's University
101 Murray Street
New York, NY 10007
http://nyoug.org/upcoming_events.htm#General_Meeting
My presentation is scheduled for Session 6 in the Developer Track from 4:00-5:00pm EST, please check the NYOUG website for updated information.
Oracle BIWA Summit 2008
Date: December 2-3, 2008
Location:
Oracle World Headquarters
Conference Center
350 Oracle Parkway
Redwood Shores, CA 94065
http://ioug.itconvergence.com/pls/htmldb/f?p=219:25
My presentation is scheduled for Tuesday, December 2, 2008, 11:00am PST, Room 106, please check the BIWA Summit website for updated information.
Desktop Conference - Fall 2008 virtual conference
Date: November 11-13, 2008
Location: your desk
http://desktopconference.org/
My presentation is scheduled for Thursday, November 13, 2008, 11am EST, Session 32, please check the DesktopConference for updated information.
Friday, November 7, 2008
Saturday, November 1, 2008
Advanced Printing in Application Express
For everyone using the printing features of Application Express, or whoever is struggling with the BI Publisher or Apache FOP configuration, you might be interested in the BIWA SIG Summit 08, taking place at Oracle’s World HQ December 2nd and 3rd. There are a number of presentations scheduled covering BI Publisher, and I’ll be doing a presentation there on advanced printing in Application Express. I’m going to cover the integration of APEX with BI Publisher, and also the configuration of APEX using Apache FOP for printing. Furthermore I’ll demonstrate how to use Web Services in APEX to call out to BI Publisher directly, to initiate and schedule the generation of reports. I’ll also go through some actual samples on how to use the PL/SQL print API to store reports inside the database and how to email them out to lists of recipients.
Visit the BIWA SIG's website for the full schedule and registration information:
BIWA Summit 2008 - December 2-3, 2008 - Oracle World HQ, Redwood Shores, California
Visit the BIWA SIG's website for the full schedule and registration information:
BIWA Summit 2008 - December 2-3, 2008 - Oracle World HQ, Redwood Shores, California
Friday, August 29, 2008
Oracle APEX Forms Converter at NYOUG
As stated in out Statement of Direction, Oracle Application Express 3.2 will introduce an Oracle Forms Converter tool. This tool will capture the design of existing Oracle Forms applications and automatically translate many of the main components. Other components, such as complex triggers, will need to be manually converted from client-side PL/SQL to server-side PL/SQL.
On September 10th, I'll give a live preview of this tool at the 6th Annual New York City Metro Area Oracle Users Group Day. Registration is open to members and non-members, so please stop by if you’re in the area:
http://www.oracle.com/dm/09q1field/18983_ev_metro_day_new_york_sep08.html
On September 10th, I'll give a live preview of this tool at the 6th Annual New York City Metro Area Oracle Users Group Day. Registration is open to members and non-members, so please stop by if you’re in the area:
http://www.oracle.com/dm/09q1field/18983_ev_metro_day_new_york_sep08.html
APEX 3.1.2 Patch Released
Thursday, August 14, 2008
Oracle Application Express with Oracle eBusiness Suite
In case you haven't read about this already on other APEX Blog sites, we're currently conducting a short survey to determine how customers integrate Oracle Application Express with the Oracle eBusiness Suite. As Joel Kallman was explaining on his blog, the purpose of this is to gather evidence with the eventual goal of formally legitimizing the use of Oracle Application Express with the Oracle eBusiness Suite. So if you are using Oracle Application Express to integrate with the Oracle eBusiness Suite, please visit David's blog here, and take this one page survey.
Wednesday, July 16, 2008
WebCast: What’s next for Oracle Application Express?
Want to learn more about what features we're working on for Oracle Application Express 4.0 and get a live demonstration of some tip & tricks for APEX 3.1.1? Then be sure to join our German-language WebCast next Friday, July 25th at 8am EDT / 14:00 CEST. Please visit the German APEX Community page for additional information and dial-in numbers:
http://www.oracle.com/global/de/community/index.html
Please note that this is a German-language WebCast.
http://www.oracle.com/global/de/community/index.html
Please note that this is a German-language WebCast.
Friday, June 20, 2008
Integrating Google Maps
Following up on a discussion on the APEX OTN forum today, here's some additional information on integrating Google Maps. There are different techniques for integrating Google maps, and we actually have a Whitepaper available online, that explains working with Google maps in much detail:
http://www.oracle.com/technology/products/database/application_express/html/doc.html
This Whitepaper explains how to do the geo-coding, i.e. converting an address (Street/City/State/ZIP/Country) into coordinates that can be used to position a Google map. In this example, the PL/SQL package utl_http is used to connect to Google's geocoding service.
An alternative to this approach is to connect to that service from the client. Both techniques have their advantages and disadvantages. One of the advantages of doing it on the client is that the server doesn't need to be able to connect to Google, i.e. there's no need to define any proxies on the server, and as a result, this approach would work on apex.oracle.com, where external network connections are currently not supported.
The first step for any kind of Google map integration is to register for a Google maps key, which can be done here:
http://code.google.com/apis/maps/
After that, it's just a matter of adding some Java Script to your page that communicates with Google and refreshes the area on your APEX page that you have allocated for the map. Here's a link to my sample page:
http://apex.oracle.com/pls/otn/f?p=52790
You can download this application here:
http://www.sewtz.com/GoogleMaps.zip
I added an address region, where you can enter a city or a full address and then click go and have the map move to the address specified. Here's the JavaScript I added to my page. It's pointing the map to my office on first load, and then re-positions the map based on the address you enter:
http://www.oracle.com/technology/products/database/application_express/html/doc.html
This Whitepaper explains how to do the geo-coding, i.e. converting an address (Street/City/State/ZIP/Country) into coordinates that can be used to position a Google map. In this example, the PL/SQL package utl_http is used to connect to Google's geocoding service.
An alternative to this approach is to connect to that service from the client. Both techniques have their advantages and disadvantages. One of the advantages of doing it on the client is that the server doesn't need to be able to connect to Google, i.e. there's no need to define any proxies on the server, and as a result, this approach would work on apex.oracle.com, where external network connections are currently not supported.
The first step for any kind of Google map integration is to register for a Google maps key, which can be done here:
http://code.google.com/apis/maps/
After that, it's just a matter of adding some Java Script to your page that communicates with Google and refreshes the area on your APEX page that you have allocated for the map. Here's a link to my sample page:
http://apex.oracle.com/pls/otn/f?p=52790
You can download this application here:
http://www.sewtz.com/GoogleMaps.zip
I added an address region, where you can enter a city or a full address and then click go and have the map move to the address specified. Here's the JavaScript I added to my page. It's pointing the map to my office on first load, and then re-positions the map based on the address you enter:
var geocoder;
var map;
var bounds = new GLatLngBounds();
var myStreet = "540 Madison Ave";
var myCity = "New York";
var myState = "NY";
var myZIP = "10022";
var myCountry = "USA";
var address = myStreet + "," + myCity + "," + myState + "," + myZIP + "," + myCountry;
var addressMarker = myStreet + "<br />" + myCity + ", " + myState + ", " + myZIP + "<br />" + myCountry;
// On page load, call this function
function load()
{
// Create new map object
map = new GMap2(document.getElementById("map"));
// Create new geocoding object
geocoder = new GClientGeocoder();
// Retrieve location information, pass it to addToMap()
geocoder.getLocations(address, addToMap);
}
// This function adds the point to the map
function addToMap(response)
{
// Retrieve the object
place = response.Placemark[0];
// Retrieve the latitude and longitude
point = new GLatLng(place.Point.coordinates[1],
place.Point.coordinates[0]);
map.addControl(new GSmallMapControl());
map.addControl(new GMapTypeControl());
// Center the map on this point
bounds.extend(point);
map.setCenter(point, 13);
map.setZoom(14);
// Create our "tiny" marker icon
var oIcon = new GIcon(G_DEFAULT_ICON);
oIcon.image = "#WORKSPACE_IMAGES#opin.png";
oIcon.iconSize = new GSize(32, 40);
oIcon.shadowSize = new GSize(38, 46);
// Set up our GMarkerOptions object
markerOptions = { icon:oIcon };
marker = new GMarker(point, markerOptions);
// Add the marker to map
map.addOverlay(marker);
// Add address information to marker
marker.openInfoWindowHtml(addressMarker);
}
function updateMap() {
myStreet = $v('P1_STREET');
myCity = $v('P1_CITY');
myState = $v('P1_STATE');
myZIP = $v('P1_ZIP');
myCountry = $v('P1_COUNTRY');
address = myStreet + "," + myCity + "," + myState + "," + myZIP + "," + myCountry;
addressMarker = myStreet + "<br />" + myCity + ", " + myState + ", " + myZIP + "<br />" + myCountry;
load();
}
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:
Sample XML Data:
Sample XSL-FO style sheet:
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>
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.
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.
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.
http://www.sphinx.at/it-consulting/de/home/
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/
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.
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.
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
"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
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.
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.
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.
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.
Monday, March 3, 2008
Oracle Application Express 3.1 available now!
Oracle Application Express 3.1 was released a few days ago, you can download the latest release here:
http://www.oracle.com/technology/products/database/application_express/download.html
Many of you have probably already heard of or even tried out the new interactive report regions. If you haven’t heard about this new region type yet, I’d recommend starting with the brief introduction which can be found here:
http://www.oracle.com/technology/products/database/application_express/html/irrs.html
In addition to interactive report regions, there have been numerous other enhancements, and one of my focus areas were the enhancements to report printing. The two main enhancements in this area are support for multiple SQL source statements for report queries, allowing to combine several reports or combinations of reports and charts in a single print document, and additionally, the introduction of a PL/SQL print API. I will cover these enhancements and some of the other changes on my Blog in the coming weeks. For now, I’d like to point out the 3.1 new features application, which showcases some of the printing enhancements along with interactive reports and other 3.1 new features:
Subscribe to:
Posts (Atom)