Marc Sewtz
![Subscribe to Marc Sewtz feed](http://www.orafaq.com/misc/feed.png)
Oracle Application Express and Oracle's Database Cloud ServiceMarc Sewtzhttp://www.blogger.com/profile/07323603133640801038noreply@blogger.comBlogger62125
Updated: 11 hours 19 min ago
Oracle Application Express 5.1 Early Adopter 1 now available!
We are very excited to announce the beginning of the Oracle Application Express 5.1 Early Adopter program, at https://apexea.oracle.com. The Early Adopter is an open-to-the-public hosted beta program. And we’re inviting everyone interested in Oracle Application Express - existing customers and new customers alike - to sign up for a free workspace and take our forthcoming release for a spin.
To get started, click the "Request a Workspace" button from the https://apexea.oracle.com home page, sign-in with your Oracle account (i.e. the same account you use for other Oracle sites, like OTN) and complete the sign-up wizard.
There are numerous new features to explore, including the Interactive Grid and Oracle JET charts. As this is an Early Adopters release, not everything is complete just yet, you can review a list of known issues here:
https://apex.oracle.com/pls/apex/f?p=510051:1
We’re looking for your feedback and input, which you can submit via the feedback button (see App Builder nav bar in the upper right-hand corner).
Thank you for your interest and your support!
Oracle Application Express 5.0 now available
The result of a two and half year engineering effort, Oracle Application Express 5.0 represents the greatest advancement of Oracle Application Express in its 10-year history. Oracle Application Express 5.0 enables customers to develop, design and deploy beautiful, responsive, database-driven desktop and mobile applications using only a browser.
Now that APEX 5.0 is finally available, it’s time to spread the word and to get everyone up to speed on what’s new in 5.0, how to make best use of the beautiful, new Universal Theme and how to get the most out of the incredibly powerful new Page Designer.
So here are some of the events coming up in the near future, where you can learn all about APEX 5.0 and meet many of the APEX development team members in person:
APEXposed 2015
Montreal, Canada - May 6, 2015
- Keynote: Oracle Application Express 5.0 (Marc Sewtz - 8:30 - 9:30am)
- Oracle Application Express 5.0 Plug-In Enhancements (Patrick Maniraho - 1:00 - 2:00pm)
- Transitioning to Oracle Application Express 5.0 (Marc Sewtz - 2:15 - 3:15pm)
Solihull, UK - May 14, 2015
- Oracle Application Express 5.0 (Anthony Rayner - 2:25-3:35pm)
Düsseldorf, Germany - June 9 - 10, 2015
- Keynote: Oracle Application Express 5.0 (Marc Sewtz - Jun-09 - 9:30 - 10:30am)
- Beautiful UI with APEX 5 (Shakeeb Rahman - Jun-09 11:00 - 11:45am)
- Der Oracle Application Express Entwicklungsprozess (Marc Sewtz - Jun-10 - 11:00 - 11:45am)
Plovdiv, Bulgaria - June 12 - 14, 2015
- Oracle Application Express 5.0 New Features (Marc Sewtz)
- Transitioning to Oracle Application Express 5.0 (Marc Sewtz)
Hollywood, FL, USA - June 21 - 25, 2015
June 21
- APEX Episode 5: A New Frontier (Joel Kallman, 8:30 - 9:30am)
- Need for Speed: Page Designer (Patrick Wolf, 9:30 - 10:30am)
- Interstellar: The Universal Theme (Shakeeb Rahman, 11:00am - 12:00pm)
- The Fifth Element: HMTL5 + APEX5 Mobile (Marc Sewtz, 1:00 - 2:00pm)
- The Matrix Reloaded: Interactive Report (Anthony Rayner, 2:00 - 3:00pm)
- The Prestige: Converting to APEX 5.0 Universal Theme (David Peake, 3:15 – 4:15pm)
- Transitioning to Oracle Application Express 5.0 (David Peake, 08:30am - 09:30pm)
- Introduction to Oracle Application Express (David Peake, 09:45 -10:45am)
- You Don't Lack APEX Skills; You Lack Oracle Skills (Joel Kallman, 2:00 - 3:00pm)
- Self-Service Application Deployment in a Runtime Instance: This Is How We (Oracle) Do It... (Jason Straub, 4:45 - 5:45pm)
- Oracle Application Express 5.0 New Features (Hilary Farrell, 9:45 - 10:45am)
- Application Express 5.0: Features Nobody Else Will Tell You About (David Peake, 1:45 - 2:45pm)
... and be sure to join one of the numerous APEX Meetup groups world wide - and if there isn't one close to where you live, then consider starting your own local APEX Meetup group - don't know how to do that? Check out the APEX Meetup site for helpful information:
APEX Tabular Forms Deep Dive at #ORCLAPEX NYC Meetup
Ever heard of APEX$ROW_NUM, APEX$ROW_SELECTOR and APEX$ROW_STATUS? Did you know you can reference tabular form columns using bind variable syntax in your page processes? Are you familiar with the execution scope of tabular form processes for modified vs submitted rows? Ever struggled using apex_application.g_fxx arrays with checkboxes? And do you really need to use the apex_item APIs? In APEX 4.0, 4.1 and 4.2 we've introduced lots of new tabular form features, including declarative tabular form validations and page processes, yet there are still PL/SQL processes being written that loop through the apex_application.g_fxx arrays or tabular form regions that mix apex_item API calls with using built-in form elements.
So when planning our next #ORCLAPEX NYC Meetup, we've figured this would be a great topic to cover. And since we don't want to talk you to death during a single meetup - we decided that we'll do a three part series, starting slow and covering the basics during our next meetup on 11/06, then dig a little deeper in part two and then conclude this series with a grand finale during the third meetup in this series.
Join us on Thurday, November 6th at the Oracle office in Manhattan, at 120 Park Ave. We're starting at 6pm and we'll serve pizza this time - thanks to everyone's generous donations last time.
RSVP today: #ORCLAPEX NYC Meetup
So when planning our next #ORCLAPEX NYC Meetup, we've figured this would be a great topic to cover. And since we don't want to talk you to death during a single meetup - we decided that we'll do a three part series, starting slow and covering the basics during our next meetup on 11/06, then dig a little deeper in part two and then conclude this series with a grand finale during the third meetup in this series.
Join us on Thurday, November 6th at the Oracle office in Manhattan, at 120 Park Ave. We're starting at 6pm and we'll serve pizza this time - thanks to everyone's generous donations last time.
RSVP today: #ORCLAPEX NYC Meetup
Oracle APEX 5.0 UI techniques at NYOUG on June 3rd
Are you ready to give your apps a fresh new look & feel? APEX 5.0 will bring you the Universal Theme and Template Options – a modern, responsive HTML5 theme that takes the guesswork out of choosing the right HTML templates and CSS classes. Thanks to easy-to-use declarative template options, you no longer have to be a HTML/CSS guru in order to create modern and beautiful web applications.
Want to learn more? Then join us for an APEX 5.0 preview this Friday, May 23 at our first ORCLAPEX NYC Meetup:
And on Tuesday, June 3rd , head over to the NYOUG Summer Meeting at the BMCC on Chambers Street and get an in-depth look at the new UI techniques that will be introduced with APEX 5.0:
First ORCLAPEX New York City Meetup
We’re excited to announce the first ever Oracle Application Express Meetup in New York City. Join us on May 23rd at the Oracle office at 120 Park Ave – right across from Grand Central. Meet other APEX developers working in the area and see what’s coming in APEX 5.0. New to APEX? Don’t worry, we’ll get you up to speed and show you what this product is all about.
As a special guest speaker, we’ll have Peter Raganitsch, from Click Click IT Solution in Vienna, Austria - known in the community for the APEXlib framework and the FOEX plug-in - show us how to use friendly URLs with your APEX applications
Developing Mobile Web Applications with Oracle Application Express 4.2
If you’re interested in developing Mobile Web Apps on the Oracle Database – and if you’re following my Blog, chances are that you are – then here’s a great opportunity to get up to speed on the latest trends and learn how you can quickly and easily develop your own mobile web apps and extend your existing APEX apps for mobile use: attend my upcoming free ODTUG Webinar on February 20th:
Developing Mobile Web Applications with Oracle Application Express 4.2
Thursday, February 20th at 3:00pm - 04:00pm EST
Thursday, February 20th at 3:00pm - 04:00pm EST
Here’s what I will be covering:
Smartphones and tablets are now commonly used throughout the enterprise. Thanks to easy to use mobile apps, ready access to the Internet, and widespread use of web- and cloud-based applications, mobile devices are a logical choice for many employees. Businesses need to respond to these trends by modernizing their public facing websites for mobile use and providing employees with access to in-house applications that support a variety of different devices and screen sizes. Solely building for desktop use and standardizing on IE only won’t cut it anymore.
Oracle Application Express (APEX) provides you with powerful tools to quickly and efficiently build mobile web apps and to easily extend your existing desktop applications for mobile use. Regardless of whether these applications were built with APEX originally or use other technologies—as long as they’re running on top of an Oracle Database, APEX will enable you to quickly build mobile apps that provide access to your data and interact with your business processes.
During this session, we’ll discuss mobile development trends and responsive web design, walk you through the basics of the jQuery Mobile framework, and provide you with an overview of the mobile development capabilities built into the current release of Oracle Application Express 4.2. We will include several live demos to get you started, so have your phone or tablet ready to try out our sample apps live while they are being built.
Additional information on ODTUG Webinars and a list of other upcoming Webinars can be found here:
See APEX at the Bulgarian Oracle User Group Conference this November
Are you interested in developing Mobile web applications with Oracle Application Express? Do you want to see some of the features that are going to be introduced with Oracle Application Express 5.0? Then join us at the Bulgarian Oracle User Group Conference, taking place in Plovdiv, Bulgaria from November 22nd to November 24th. Roel Hartman and I will be doing four sessions on Oracle Application Express, covering everything from what’s new in APEX 5.0, starting mobile development with APEX, taking a sneak peek at the new mobile development capabilities coming in 5.0 to building hybrid apps with APEX and PhoneGap / Cordova. Additionally I’ll also cover some SQL Developer tips and tricks and best practices:
- 11/22 14:15 - Top 10 Oracle SQL Developer Tips and Tricks (Marc Sewtz)
- 11/23 10:00 - Oracle Application Express 5.0 (Marc Sewtz)
- 11/23 11:15 - My First Mobi : Starting Mobile Development with APEX (Roel Hartman)
- 11/23 12:30 - Mobile Development with Oracle Application Express 5.0 (Marc Sewtz)
- 11/23 14:30 - The best of both worlds : Going hybrid with Oracle Application Express (Roel Hartman)
http://www.bgoug.org/en/events/details/90.html
White Paper on Custom PDF Reports in APEX
As I’ve previously blogged about and outlined in my video tutorials, it is now possible to create custom PDF reports with APEX 4.2.2 using the APEX Listener 2.0.2 and third-party tools like Altova Stylevision or Stylus Studio. To help our customers getting started with this, we’ve just released a white paper that outlines the system requirements, explains the configuration steps and options and then walks you through the creation of PDF reports using custom layouts step by step. The white paper is available on out Oracle Application Express OTN page:
PDF Reports with APEX at NYOUG
The New York Oracle User Group Summer Meeting takes place on June 5th at St. John's University, right next to the World Trade Center in Downtown Manhattan. This looks like it’s going to be a very interesting meeting for Oracle DBAs and Oracle Developers. There are two session scheduled covering the Oracle Database 12c and also two APEX sessions. There’s going to be a session on building mobile apps with APEX, which looks to be very hands-on. And I’ll be showing how to build custom PDF reports with APEX. I’ll walk you step by step through creating the report in APEX, downloading the XML, creating a report layout with tools like Stylevision, loading the layout back into APEX and associating the layout with your report. If you have a laptop running APEX 4.2.2 and the APEX Listener 2.0.2, you’re welcome to bring it and follow along – you will need at least a trial version of either Altova Stylevision or Stylus Studio.
Details about this event can be found on the NYOUG website:
APEX-Community meets Development - Munich, May 16th
Mark your calendars for May 16th. Following the Cloud World events in London and Munich on May 14th and 15th, we’re organizing an APEX Community day at the Oracle office in Munich, Germany on Thursday, May 16th. I’ll be showing the just-release APEX 4.2.2, talk about what’s new, what to consider when upgrading and we'll do a demonstration of some of the PDF printing enhancements in this release. Then we’re going to take a look at our upgraded packaged applications and sample applications, including a demonstration of our newly added Survey Builder. And with APEX 5.0 development on the way, we’re also going to take a look at some of the features we’re currently working on and of course I’d be happy to discuss your questions, suggestions and feature requests during our meeting.
A detailed agenda and information on how to sign up for this free event can be found here:
Oracle OTN Developer Day in New York on May 7th
Are you interested in developing and running your Oracle Application Express applications on the Cloud? And do you want to learn more about Oracle’s Database Cloud Service? Then join us on May 7 at the Oracle OTN Developer Day in New York. At this event, you will be able to network with peers and Oracle experts while learning about Oracle Cloud's Platform as a Service (PaaS) offerings and the speed, flexibility, and productivity they offer.
I’ll be doing an Oracle Cloud overview presentation in the morning and then we'll do a Database Cloud Hands-On lab with you in the afternoon. You’ll get hands-on experience with the Oracle Database Cloud and learn about our suite of packaged productivity applications. You’ll learn how to load bulk data to the Oracle Database Cloud, build applications on this data, add reports and charts, setup RESTful webservices and build and deploy mobile web apps – all as part of this free workshop.
PDF Printing with Oracle Application Express 4.2.2
Oracle Application Express 4.2.2 was released today, you can download the full release from the Download page on OTN. As always, there have been numerous improvements and bug fixes. We’ve updated many of our included packaged applications and we’ve introduced a new packaged application: the Survey Builder, which you can use to easily create and run surveys.
With APEX 4.2.2 and the APEX Listener 2.0 we’re also introducing a number of report printing enhancements. Using the APEX Listener for PDF printing has been added as a third option in addition to using Apache FOP or Oracle BI Publisher as external print rendering engine. The main difference in using the APEX Listener for PDF printing is that the FOP libraries are actually built into the Lister, thus no complicated configuration of an external engine is required in this setup. All that’s required is to log into the APEX Instance Administration, select the APEX Listener as the Print Server, and then enable PDF printing for your Classic Reports, Interactive Reports or Report Queries.
And unless you’re calling the APEX Print APIs directly in your custom PL/SQL code, you also won’t have to enable networking services in the Oracle database anymore, because the APEX Listener based printing option loads your reports straight to the APEX Listener, which then renders them in PDF format and downloads them to your browser.
To help you get started with using the APEX Listener for PDF Printing I’ve created a short video that walks you through the configuration steps in the APEX Instance Administration and then shows you how to create a simple report with PDF export enabled:
We’ve also improved support for custom XSL-FO layouts in APEX 4.2.2. This means, you can now more easily customize your PDF reports to your requirements using third-party tools like Altova Stylevision or Stylus Studio that allow for drag and drop layout of XSLT stylesheets. So if you have a Report Query or Classic Report Region, you can now simply download your report data in XML format and use this XML data as the data source for any of these tools. Once loaded into the tool, all components of your report data, including information about your application, page, report region, user name as well as session state can be easily included in the layout via drag and drop. Once you completed your layout, you can safe it as a XSLT 1.0 file and upload this file into APEX as a report layout (under Shared Components). Now you can update your report query or classic report region to use this custom layout when exporting your report in PDF format.
I’ve created a second video, which walks your through all the details. Starting with the sample app created in the first video, I’m showing you how to export your report in XML format, load this XML file into Altova Stylevision or Stylus Studio, then I'm demonstrating how to use these tools to create custom layouts and finally how to upload your completed layouts and use them for your reports in APEX:
Oracle Cloud World - London May 14th & Munich May 15th
I’ll be presenting Oracle Application Express as part of the Oracle Database Cloud Service at the Cloud World events in London and Munich next month.
At Oracle Cloud World you’ll hear from Oracle executives, thought leaders, customers, and partners as they share ideas on how to transform business and stay ahead of your competition through technologies like cloud, social, and mobile.
In addition to keynote sessions highlighting Oracle’s strategy and roadmap for cloud and social, Oracle CloudWorld offers tracks specifically for:
• Line-of-business leaders in sales and marketing, customer service and support, HR and talent management, and finance and operations
• IT Professionals that support the above lines of business
• Oracle Database Cloud Services and Oracle Java Cloud Services
Here’s the information on my session:
Develop With the Oracle Database You Know and Love, Now in the Cloud
Developing applications to run on the world's leading database? Why not do it in the cloud? See the process for signing up for and provisioning Oracle Database Cloud Services, loading data, developing applications with Oracle Application Express, and creating RESTful Web services. This end-to-end presentation introduces the major components of Oracle Database Cloud Services and explores its use.
Register today for these free events:
London: Tuesday, 14 May 2013, 08:15 – 18:30, ExCel Center, ICC Capital Suite, Agenda and Registration
Munich: Wednesday, 15 May 2013, 9:00 – 19:00, The Westin Grand München, Agenda and Registration
APEX at the NYOUG Special Winter Meeting
Just back from the DOAG conference (German Oracle User Group) in Nuremberg, it’s now time to get ready for an event closer to home: the annual NYOUG Special Winter Meeting at The New Yorker Hotel on December 12. If you’re interested in APEX and the Oracle Database, please stop by - you’ll have a busy day.
Willie Hardie, Vice President for Oracle Database Management will kick things off at 9:30am with his keynote on “What’s next for the Oracle Database". Right after that - at 10:30 - I’ll be talking about what’s new in Oracle Application Express 4.2.
Later – at 2pm – you can hear about "Responsive Web Design in Oracle Application Express with HTML5 and CSS3" from Shakeeb Rahman and then finish the afternoon with Josh Millinger’s "The Maturity of Tabular Forms".
Looking forward to seeing you at the NYOUG Special Winter Meeting. You can find the full agenda, including abstracts on all sessions here:
http://nyoug.org/upcoming_events.htm#General_Meeting1
APEX at DOAG 2012
Only one week to DOAG 2012 - the most important conference for Oracle users in Germany. The event takes place in Nuremberg, Nov 20th – 22nd. This year there will be 26 presentation on Oracle Application Express alone!
Make sure to attend the sessions presented by Patrick Wolf, Christian Neumüller, Carsten Czarski, Peter Raganitsch, Dietmar Aust, Niels de Bruijn and other members of the APEX community.
I'll be presenting Oracle Application Express 4.2 during my keynote on Tuesday:
Keynote Development: Oracle Application Express (APEX) 4.2 New Features
Tue 20. November
10:00am - 10:45am
Room 12-Istanbul
Then - if you want to dig a little bit deeper and learn all about developing mobile application with APEX - stop by my session on Wednesday:
Building Mobile Web Applications with Oracle Application Express
Wed 21. November
10:00am - 10:45am
Room 12-Istanbul
And if you have any question about APEX, new features, technical details, stuff you've been struggling with, APEX's future, features planed for the next release and beyond – or if you just want to stop by to let us know how much you love APEX, here's you chance: Patrick Wolf, Carsten Czarski, Peter Raganitsch and I will be doing our best answering all you question during the expert panel on Tuesday:
APEX Expert-Panel
Tue 20. November 16:00 - 16:45 Uhr
Room 17-Hongkong
… of course for your more difficult questions, we'd appreciate it if you could post your questions ahead of time, so we can do our homework in advance, just visit Carsten's panel app here:
http://tiny.cc/apexpanel
Thanks and see you all next week in Germany!
Make sure to attend the sessions presented by Patrick Wolf, Christian Neumüller, Carsten Czarski, Peter Raganitsch, Dietmar Aust, Niels de Bruijn and other members of the APEX community.
I'll be presenting Oracle Application Express 4.2 during my keynote on Tuesday:
Keynote Development: Oracle Application Express (APEX) 4.2 New Features
Tue 20. November
10:00am - 10:45am
Room 12-Istanbul
Then - if you want to dig a little bit deeper and learn all about developing mobile application with APEX - stop by my session on Wednesday:
Building Mobile Web Applications with Oracle Application Express
Wed 21. November
10:00am - 10:45am
Room 12-Istanbul
And if you have any question about APEX, new features, technical details, stuff you've been struggling with, APEX's future, features planed for the next release and beyond – or if you just want to stop by to let us know how much you love APEX, here's you chance: Patrick Wolf, Carsten Czarski, Peter Raganitsch and I will be doing our best answering all you question during the expert panel on Tuesday:
APEX Expert-Panel
Tue 20. November 16:00 - 16:45 Uhr
Room 17-Hongkong
… of course for your more difficult questions, we'd appreciate it if you could post your questions ahead of time, so we can do our homework in advance, just visit Carsten's panel app here:
http://tiny.cc/apexpanel
Thanks and see you all next week in Germany!
Kscope '12 and the Rodeo Special Event
Just arrived back home in New York after a great week at the ODTUG Kscope ’12 conference in San Antonio, TX. It was great meeting many members of our ever growing APEX community again, seeing several outstanding presentations on APEX and other topics and being able to present to our customers what’s new in Oracle Application Express 4.2 and introducing the mobile features we’ve been working on over the past couple of month.
If you've been to our sessions and want to try out for yourself what's new, or if you're just curious what APEX is all about, please sign up for our Early Adopters hosted site at:
https://apexea.oracle.com
And thank you Kscope for organizing yet another amazing special event Wednesday night, I truly enjoyed the Rodeo, certainly something you don’t see every day in New York City. I’ve put together a few scenes of the event and loaded the up on YouTube, please check out the video here:
Application Express 4.2 Early Adopter and Kscope 12
Today we released the first Early Adopter version of Oracle Application Express 4.2 – right in time for ODTUG KScope12. You can sign up and give it a try:
One of our focus areas in this release of course has been Mobile. And while we’re still actively developing many of the new features in APEX 4.2, you’ll find that you can already build pretty nice looking and usable mobile applications.
With KScope starting this weekend, I thought what better way to illustrate some of the capabilities in APEX 4.2, than to publish a very simple KScope scheduler, highlighting the sessions that are going to be presented by members of the Oracle Application Express development team:
I recommend opening this link on your mobile phone. And if you’re reading my Blog on your desktop (people still use those?), then here’s a handy QR code to save you the hassle of typing in the URL:
I list all the sessions with a simple search box and you can also drill down by presenter, or day. And if you feel like letting us know that you’ll be attending some of these sessions, feel free to leave you name and email for your session(s) of choice.
And lastly, you may have noticed I felt a bit inspired by the conference location, and colorized my app accordingly. If you’re building you apps with the APEX 4.2 EA, you’ll find you currently only have a choice of blue with different shades of gray. But jQuery Mobile offers very easy ways to customize your look & feel to your or your users’ taste. So if you want to learn more about that, and want to see mobile development in APEX 4.2 in action, stop by my session on Wednesday at 9:45am:
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):
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.
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:
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:
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).
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 (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.
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
);
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).
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:
Getting Started with Mobile in APEX - Part 2
jQuery Mobile 1.0 Final was released today! Time to take it for a spin. As outlined in my previous post, jQuery Mobile is available for download on the jquerymobile.com web site:
Alternatively, if you were referencing the CDN, you could of course also simply update your jQuery Mobile enabled templates and remove the "RC2" or "RC3" suffix from your file references:
Then just re-run your pages, and you'll be up and running using the shiny, new final release of jQuery Mobile 1.0!
Now running my original "Hello World!" sample with jQuery Mobile 1.0 would be a bit boring. So here's a new sample application, this one should be more interesting. It's a very basic message board application. Point your mobile device (or desktop browser) to this URL:
You'll get a home page showing recent messages, along with the title, author and create date and time. When you click on a message, you'll get to a read-only view of the message details. When you click on the create button on the home page, you can enter a new message, with your name, email and message title. Give it a try, leave me some feedback and comments, and if you like the app, it's available for download as well. It comes with the underlying database objects bundled in as supporting objects, i.e. they're created when you install the application.
For the most part, this is a standard APEX application, nothing too fancy or out of the ordinary. The only relevant templates are those containing "mobile" in their names. The home page was built as a standard classic report page, using a customized named-column report template. The form page is a standard APEX form, which is omitting the form table-grid using the corresponding region template attribute and using the new label template field container attributes. The buttons use simple anchor tags, and illustrate how you can have different button colors, using the new "hot" attribute for buttons. And for the back button, I used a jQuery Mobile button icon, which can be easily included using the HTML data- attributes, data-icon in this case.
So try out the application, and leave your comments in the message board. I'll follow up with additional details on some of the more advanced concepts in upcoming posts.
Alternatively, if you were referencing the CDN, you could of course also simply update your jQuery Mobile enabled templates and remove the "RC2" or "RC3" suffix from your file references:
<link rel="stylesheet" href="http://code.jquery.com/mobile/1.0/jquery.mobile-1.0.min.css">
<script src="http://code.jquery.com/jquery-1.6.4.min.js"></script>
<script src="http://code.jquery.com/mobile/1.0/jquery.mobile-1.0.min.js"></script>
Then just re-run your pages, and you'll be up and running using the shiny, new final release of jQuery Mobile 1.0!
Now running my original "Hello World!" sample with jQuery Mobile 1.0 would be a bit boring. So here's a new sample application, this one should be more interesting. It's a very basic message board application. Point your mobile device (or desktop browser) to this URL:
You'll get a home page showing recent messages, along with the title, author and create date and time. When you click on a message, you'll get to a read-only view of the message details. When you click on the create button on the home page, you can enter a new message, with your name, email and message title. Give it a try, leave me some feedback and comments, and if you like the app, it's available for download as well. It comes with the underlying database objects bundled in as supporting objects, i.e. they're created when you install the application.
For the most part, this is a standard APEX application, nothing too fancy or out of the ordinary. The only relevant templates are those containing "mobile" in their names. The home page was built as a standard classic report page, using a customized named-column report template. The form page is a standard APEX form, which is omitting the form table-grid using the corresponding region template attribute and using the new label template field container attributes. The buttons use simple anchor tags, and illustrate how you can have different button colors, using the new "hot" attribute for buttons. And for the back button, I used a jQuery Mobile button icon, which can be easily included using the HTML data- attributes, data-icon in this case.
So try out the application, and leave your comments in the message board. I'll follow up with additional details on some of the more advanced concepts in upcoming posts.