Marc Sewtz

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!

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)

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

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)

You can register for the conference and review the full agenda here:

http://www.bgoug.org/en/events/details/90.html

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

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!

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:

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

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

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

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

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

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

select * from emp

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

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

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

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

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

create or replace function blob2clobase64 (
 p_blob in blob,
 p_escape in char default 'N'
) return clob is
 l_pos       pls_integer := 1;
 l_buffer    varchar2 (32767);
 l_res       clob;
 l_lob_len   integer := dbms_lob.getlength (p_blob);
begin
 dbms_lob.createtemporary (l_res, true);
 dbms_lob.open (l_res, dbms_lob.lob_readwrite);
 loop
  
     l_buffer := utl_raw.cast_to_varchar2 (
                   utl_encode.base64_encode (
                       dbms_lob.substr (p_blob, 48, l_pos)
                   )
               );
     if (p_escape = 'Y') then
         l_buffer := replace(l_buffer,'+','%2B');
         l_buffer := replace(l_buffer,'/','%2F');
         l_buffer := replace(l_buffer,'=','%3D');
     end if;
  
     if length (l_buffer) > 0 then
         dbms_lob.writeappend (l_res, length (l_buffer), l_buffer);
     end if;
     l_pos := l_pos + 48;

     exit when l_pos > l_lob_len;
  
 end loop;
 return l_res;

end blob2clobase64;

So in my example, using a table called eba_pdfimg_images, that includes the data and images I want to print, the XML generation would look like this:

   -- generate XML data
 for c2 in (
     select dbms_xmlgen.getxml('
         select
             id,
             file_name,
             mime_type,
             description,
             blob2clobase64(image,''Y'') image
             from eba_pdfimg_images
     ') xml_data from dual
 ) loop
     l_xml_data := c2.xml_data;
 end loop;

Once I query up my report layout, converted that to a CLOB, and generated my XML data with images as outlined above, I can simply call our API:

    apex_util.download_print_document (
     p_file_name           => 'image_demo',
     p_content_disposition => 'ATTACHMENT',
     p_report_data         => l_xml_data ,
     p_report_layout       => l_print_layout,
     p_report_layout_type  => 'rtf',
     p_document_format     => :P1_FORMAT
 );

This will generate a file called image_demo, and based on whether you choose to generate Word or PDF, you'll get ‘.rtf' or ‘.pdf' file back.

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

Dynamic Images in PDF Reports

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

Download Sample App

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

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: