Marc Sewtz

Getting Started with Mobile in APEX - Part 2

2011-11-17T17:33:00.003-05:00

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:

http://jquerymobile.com/download/

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:

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

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.

Download Mobile Sample App 2

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.

Getting Started with Mobile in APEX - Part 1

2011-11-11T14:59:00.011-05:00

With jQuery Mobile moving closer to production, I finally started working on my paper documenting how to develop mobile applications with APEX 4.1 We've put a lot of changes into APEX 4.1 that make the integration of frameworks like jQuery Mobile easier. Full mobile support, with mobile templates and components that are optimized for mobile devices, is currently planned for APEX 4.2. But that doesn't mean that you couldn't start building mobile apps with APEX today. It'll just be a bit more manual work for now. So while working on the paper, I've figured I'll start blogging about this as well, taking you step by step through the process of enabling mobile development in APEX 4.1, integrating the jQuery Mobile library and building your first mobile app.

So let's get started. The first thing you'll need to do is set a system preference in APEX that enables mobile development, to do this, logon as SYS and run the following:


exec apex_040100.wwv_flow_platform.set_preference('MOBILE_DEVELOPMENT_ENABLED','Y') ;

Don't worry, this will have no effect on any of your existing applications. What this system preference does is that in a number of places in APEX, you'll now see a select list that lets you choose between a "Desktop" and "Mobile" mode. The most important place where that's relevant is when editing a page template, i.e. you can now define that page template to be a mobile page template or a full-size / desktop page template. Once we have jQuery Mobile fully integrated, currently targeted for APEX 4.2, this will trigger the inclusion of the jQuery Mobile library, and the omission of some JavaScript and CSS references that are not needed for mobile. For the time being though, all this is doing is that it makes APEX render form elements on your mobile pages without a table grid. This gives you better control over the HTML generated by APEX, which allows the generation of HTML code that follows to jQuery Mobile syntax. Having a mobile page template in your current theme also triggers the mobile option in the create page wizards. So you can choose to build a mobile page, which hides page types from the wizard that don't yet work well on mobile, like e.g. Flash charts. And as you step through the wizard, the wizard will pick up your default mobile templates, instead of the standard templates, but only if you have actually set your mobile defaults for your theme.

Next you'll need to install or reference the jQuery Mobile libraries. These libraries are not yet bundled into APEX because jQuery Mobile is not yet production software. However you can download the libraries directly from jquerymobile.com or even easier, reference the CDN-hosted libraries in your page template. To reference the CDN, simply include the following in your page template:

<link rel="stylesheet" href="http://code.jquery.com/mobile/1.0rc2/jquery.mobile-1.0rc2.min.css" />
<script src="http://code.jquery.com/jquery-1.6.4.min.js"></script>
<script src="http://code.jquery.com/mobile/1.0rc2/jquery.mobile-1.0rc2.min.js"></script>

If you prefer downloading and installing jQuery Mobile in your APEX instance, go to this URL and download the ZIP File from there:

http://jquerymobile.com/download/

Then extract the contents of this ZIP file and load them into your APEX images directory. When using the APEX Listener or Apache/mod_plsql, you can simply copy the files to the images directory. When using the Embedded Gateway, you will need to connect to your database via WebDAV or FTP and upload the files into XML DB. To follow the folder structure that APEX uses for other jQuery libraries, make sure to copy the files to a folder you create as \i\libraries\jquery-mobile\1.0rc2\ (that's assuming your downloading the Release Candidate 2 of jQuery Mobile). The JS and CSS files should be copied directly in this folder, the images directory one below. Once installed, you can include the following in your page template:

<link rel="stylesheet" href="#IMAGE_PREFIX#libraries/jquery-mobile/1.0rc2/jquery.mobile-1.0rc2.min.css" />
<script src="#IMAGE_PREFIX#libraries/jquery/1.6.1/jquery-1.6.1.js""></script>
<script src="#IMAGE_PREFIX#libraries/jquery-mobile/1.0rc2/jquery.mobile-1.0rc2.min.js"></script>

And then you're ready to get started with Mobile development. To create a minimalist mobile page template, use the following code samples:

Header:

<!DOCTYPE html>
 <html>
   <head>
     <meta charset="utf-8">
     <meta name="viewport" content="width=device-width, initial-scale=1">
     <title>#TITLE#</title>
     #HEAD#
     <link rel="stylesheet" href="http://code.jquery.com/mobile/1.0rc2/jquery.mobile-1.0rc2.min.css" />
     <script src="http://code.jquery.com/jquery-1.6.4.min.js"></script>
     <script src="http://code.jquery.com/mobile/1.0rc2/jquery.mobile-1.0rc2.min.js"></script>
   </head>
   <body #ONLOAD#>
     #FORM_OPEN#

Body:

<div data-role="page" id="#TITLE#">

  <div data-role="header" data-theme="b" data-position="fixed">
    <h1>#TITLE#</h1>
  </div>

  <div data-role="content">
    #BOX_BODY#
  </div>

  <div data-role="footer" class="ui-bar" data-position="fixed">
    #REGION_POSITION_08#
  </div>

</div>

Footer:

#FORM_CLOSE#  
</body>
</html>

After that, create a new page, choosing the newly created mobile template, and include e.g. a HTML region with some static text, like "Hello World". Then open that page in your mobile device. You should see something like this:

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

You can download this sample app here (this is an updated version, in my previous version I used an application alias that caused the app to not work if it was installed more than once on an instance like apex.oracle.com), just import into your own workspace and run:

Mobile Demo 1

Please note that this demo references the CDN, if you want to reference your locally installed jQuery Mobile files, please modify your page template as outlined above.

This concludes my first "Getting Started with Mobile" blog post. Next time I'll cover the jQuery Mobile syntax and how you would go about structuring your mobile APEX pages.

Oracle Database Cloud Service

2011-11-10T16:56:00.003-05:00

Learn all about Oracle's Database Cloud Service at my upcoming presentations in New York and Birmingham, UK. Announced at Oracle Open World 2011, the Oracle Public Cloud is a set of integrated services that provide access to Oracle Fusion Applications, Oracle Fusion Middleware, and the Oracle Database. The focus of my sessions will be on the Database Cloud Service, which includes Oracle Application Express, access to the Oracle Database using RESTful Web Services and a set of business productive application, which are based on Oracle Application Express and can be installed with just a few clicks.

UKOUG, Birmingham, UK, 12/06/2011

NYOUG, New York, 12/13/2011

See you there!

Oracle APEX 4.0 Cookbook

2011-03-01T17:39:00.001-05:00

Thanks to some recent train travel, I had a chance to catch up on some reading, and so I looked at the recently published Oracle APEX 4.0 Cookbook by Michel van Zoest and Marcel van der Plas. It's great to see more and more APEX books coming out, and of course there are already several excellent ones out there. This new one I found quite interesting due to the way it's been structured. The authors basically have chapters covering the main areas of interest, i.e. creating basic applications, themes, websheets, web serivces, APIs, etc. And then within each chapter, they have smaller sections that lay out, step by step, how to go about e.g. creating a form, creating a report, creating a theme, etc, all broken down into very manageable pieces. And in each section, they tell you what you need to get started, then walk you through the steps, and then provide some background info on how it all works. So I think this is the kind of book an APEX developer might put on his desk (or e-reader) to quickly look up specific features while they are developing their applications. I think this book provides a great value to both experienced APEX developers and developers who just recently got started. It's not necessarily targeted towards complete beginners though, i.e. it's not so much an introduction into APEX as the authors pretty quickly get into more advanced features, like APIs, AJAX, JavaScript, etc. So for anyone looking for an APEX book they can use to quickly look up certain features, and how to use them, this book is doing an excellent job.

8th Annual NYC Metro Oracle Users Group Day

2010-12-07T11:14:00.002-05:00

If you're in the NYC metro area tomorrow, come join me at the 8th Annual NYC Metro Oracle Users Group Day. The day kicks off with Andy Mendelsohn's keynote on Oracle 11g. Andy Mendelsohn is Oracle's Senior Vice President of Database Server Technologies, and our team, the Oracle Application Express group, is part of that organization.

And a little bit later, at 11:45am I'm on with my presentation about the architecture and configuration options of Oracle Application Express. I'm planing to dig a little bit deeper than usual into how APEX actually works, and walk you through the different setup options with the APEX Listener, Embedded PL/SQL gateway etc and review the install and update process. I presented several different APEX topics at a number of recent NYOUG meetings, and in addition to questions about these feature, I also always get a lot of architecture and configuration related questions, so I thought this time, I''ll dedicate my whole session to this.

You can find more information about the event here:

http://www.oracle.com/dm/11q1field/60403_8th_annual_metro_users_group_day_ny.html

culture shock ;-)

2010-11-19T16:11:00.003-05:00

This week's DOAG 2010 conference in Nuremberg finally gave me an opportunity again to visit Germany after not having been there in a few years, and to present to a very engaged local APEX community in my native tongue. I think the conference was a great success and I was excited to see how many APEX sessions were scheduled this week - and it seems all the APEX sessions drew large audiences. I think Patrick Wolfs's and my sessions had about a hundred participants, close to capacity of the rooms. And Carsten Czarski had managed to hook us up with an additional session in the Demo Kino, so I ended up speaking for about three hours straight on Tuesday. I had a few customers come up to my podium after my presentations, thanking me for the information on APEX 4 and my demos and after they had seen on the schedule that I'm from New York, congratulating me on my excellent German skills. Of course they were less impressed, after I revealed to them that I had actually grown up in Germany.

I also really enjoyed the DOAG party Wednesday night. I was having dinner with Todd Trichler, Kuassi Mensah and Tom Kyte. The event organizers had setup a great buffet, live music and very neatly arranged tables. And each table had very attentive waitresses, who took good care of us. A few minutes into the dinner Bryn Llewellyn stopped by, and someone in my group suggested we could move a little and Bryn could pull over a chair from a neighboring table - which he promptly did. You should have seen the look on the face of one of our waitresses, she appeared to be in complete shock and disbelieve that someone would dare break the order of the tables and chairs. And just as I was making a joke to Todd, explaining some of the cultural differences, Steven Feuerstein stopped by, grabbed a chair from a neighboring table as well and unfortunately for him, took a chair from the VIP table were Oracle Germany's top management was having dinner. That of course was too much for the poor waitress, who promptly moved the chair back into it's proper position. Thankfully one of the DOAG officials became aware of the situation and personally setup a chair, plates and silverware for Steven on our table and we got to enjoy the rest of our dinner. Here's a photo taken by Markus Eisele (http://blog.eisele.net/):

APEX at Oracle Technology Fairs in NY & NJ

2010-10-12T13:50:00.004-04:00

Are you an Oracle Application Express user in the New York / New Jersey metropolitan area? If so, stop by one of Oracle's upcoming Technology Solutions & Systems Fairs. Those are free, half-day events where I'll be demonstrating Oracle Application Express, SQL Developer and the SQL Developer Data Modeler. I'll be available to discuss any questions, issues or feedback you might have.

Here are the dates, locations and links to register:

Marriott Bridgewater, NJ
Tuesday, October 19, 2010, 9:00 a.m. – 1:00 p.m.

Hilton New York, NY
Tuesday, October 26, 2010, 9:00 a.m. – 1:00 p.m.

See you there!

Getting Ready for Oracle Open World 2010

2010-09-15T21:55:00.004-04:00

Are you coming to Oracle Open World next week and are you wondering what sessions to attend while you’re there? Well, if you’re travelling with your iPhone, iPad, or Android, then look no further, here’s you mobile web app you’ve been waiting for. With this app - build with Oracle Application Express 4.0 - you’ll easily find all the APEX related sessions, hands-on labs and the Demo-Grounds schedule by date, company, presenter and location:

http://apex.oracle.com/pls/apex/f?p=oow2010

… if you’re travelling with any other device, like one of these laptop computers or netbooks they’ve been using way back when, you’ll still get to enjoy this app, as long as you’re using a Webkit based browser, like Apple Safari, or Google Chrome.

Hope to see you there, I’m up on stage on Monday, 11:30am, Hotel Nikko, Golden Gate, talking about Tabular Forms in APEX 4.0.

Oracle Application Express 4.0 available now!

2010-06-23T08:55:00.002-04:00

After more than two years of development, a number of hosted early adopter's releases and after having successfully updated apex.oracle.com, Release 4.0 of Oracle Application Express is now available for download on the Oracle Technology Network (OTN).

The main enhancements and new features of Oracle Application Express 4.0 include:

Websheets
Team Development
Dynamic Actions
Plug-Ins
Enhanced Interactive Reports
Improved Tabular Forms
Updated Charts
Support for RESTful Web Services
Modernized Themes & Templates
Dynamic calendars
and many more...

You can read all about the new release, the new features, how to get started and how to install Application Express on our updated OTN home page. And if you're not quite ready yet to update to the latest release, or if you haven't used APEX before, and just want to get to know it, you can sign up for a free workspace at http://apex.oracle.com.

Scott Spadafore

2010-03-22T15:31:00.004-04:00

Nearly 10 years ago, Scott and I worked on a Consulting project in Chicago, enthusiastically writing J2EE applications, extending the Oracle E-Business Suite, and headed for near certain project failure, when our team got introduced to some obscure group of Sales Consultants out of Oracle Government, Education and Healthcare. They showed us a web development framework called “Oracle Platform”, which was based on PL/SQL of all languages. Surely this couldn’t be any good, so un-cool, so last century, so not Java. Yet given the mess our project was in, we had no alternatives, had to give it a try and actually managed to bring this project to a successful close.

We were so much older then, ….

Web Bid & Compliance Team, July 2001

A few months later, Scott and I joined the “Oracle Platform” team, which today is better known as the APEX team – and the rest, as they say, is history.

Scott passed away last night. I truly enjoyed working with him over all these years and hanging out with him, whenever we had a chance to meet. I don’t think APEX and the APEX community would be what they are today without his many contributions. Thank you Scott!

OTN Developer Day in New York City on 01/13

2010-01-04T13:14:00.005-05:00

Spend a day with us learning the key tools, frameworks, techniques, and best practices for building database-backed applications. Gain hands-on experience developing database-backed applications with innovative and performance-enhancing methods using tools such as Oracle Application Express, SQL Developer, Java and .NET. Meet, learn from, and network with Oracle database application development experts and your peers.

APEX 4.0 Early Adopter available now!

2009-12-21T09:46:00.002-05:00

On Friday night last week we launched the Early Adopter’s release of Oracle Application Express 4.0. You can try it out by signing up for a free workspace on our hosted instance at:

http://tryapexnow.com

You’ll be asked to fill out a brief survey, after which you’ll be able to request a workspace.

This early adopter's release does not yet include all the features you’ll see in the final product. Other features, that are already included, are not yet complete. And this being an early adopter’s release, there are several bugs and issues you’ll encounter.

One of the areas I’m focusing on, is the new APEX user interface, and the new theme 1. Both are still work in progress, i.e. we recommend using Firefox for now. Other browsers, including Internet Explorer will be supported down the road.

Please send us your feedback using the feedback link found in the upper right-hand corner of the Application Builder.

Live from New York – It’s APEX 4.0!!!

2009-11-30T17:23:00.002-05:00

Want to see what’s new in APEX 4.0, and get a live demo of new features like Dynamic Actions, Updated Flash Charts, Plug-Ins, Tabular Form Validations, Improved Timestamp Support and many other features currently in development? Then come to New York City next Tuesday, December 8th, and visit the NYC Metro Oracle Users Group Day at the New Yorker Hotel:

NYC Metro Oracle Users Group Day

This year, we’re doing the Oracle Technology Solutions Fair as part of this event and I’ll be showing APEX 4.0 all day at the Oracle Database Tools demo table. So don’t miss this opportunity to get a first-hand look at APEX 4.0 in action.

Scenes from the OOW 09 Appreciation Event

2009-10-19T21:59:00.006-04:00

In addition to the photos I posted Wednesday night, I managed to catch a few video scenes from the OOW party on my photo camera. I loaded them into iMovie '09, mixed in a few photos, and here's the result:

Oracle Application Express Forms Converter Book

2009-10-19T21:42:00.008-04:00

On my way back from Oracle OpenWorld I finally found some time catching up on some of the books about Oracle Application Express, and took a closer look at the “Oracle Application Express Forms Converter” book, written by Douwe Pieter van den Bos. I think this book should be really useful for any customer considering converting their Oracle Forms applications to Oracle Application Express. It’s well written and well structured. I liked the fact that a good portion of the book is dedicated to actually understanding the reasons for moving to a more modern technology like Oracle Application Express, understanding the functionality of the original Forms application and the conversion process as well the architecture.

When we developed the Forms conversion utility in Oracle Application Express we knew that we wouldn’t be able to create a magic box, which would convert 100% of the original application, or in some way would emulate Forms in a different environment. So we spent a good amount of time implementing features to track, review and annotate all components of the original Forms application, while providing automatic conversion of components that have an equivalent component in Oracle Application Express. This ensures that all components of the original Forms applications are accounted for. The book nicely outlines how to make use of these project-tracking features, starting with deciding which components to convert, assigning developers to different modules, and annotating and tagging the different part of the imported Forms application meta data. The actual conversion process is explained clearly, including preparing and optimizing the imported Forms application definition, preparing and refining the application model and generating the actual Oracle Application Express application.

Just about the only part where I’d like to see some additional details in a future edition of the book would be the chapter about reviewing and customizing the generated application. In most Forms conversion efforts, this is probably the part where most time is being spend on, so it would be nice to see some best practices, learn more about what to do with components that were not automatically converted, etc.

The book concludes with a section on “Delivering your Application”, stressing the importance of user acceptance and training. This is a critical part of any conversion project, especially when the user interface of the new system is significantly different from the previous system. I recommend anyone interested in converting Oracle Forms to Oracle Application Express to read this book.

The APEX Family at the Oracle OpenWorld 2009 Appreciation Event

2009-10-15T04:32:00.004-04:00

Joel, Anthony, Mike, Hilary, David, Christina, Jason, Marc, Patrick, Scott

Oracle OpenWorld - Demo Grounds and APEX Session Schedule

2009-10-07T17:33:00.006-04:00

Oracle OpenWorld is almost here and if you’re going there, be sure to stop by the Demo Grounds next week. Several members of the Oracle Application Express development team will be on site, doing demonstrations of what’s to come in Oracle Application Express 4.0 all day long. As David Peake was pointing our on his Blog, we got a great location this year, so that we can accommodate larger crowds for our not-so-private demonstrations. Also, be sure to take a look at our Oracle APEX session schedule. This will help you finding all the great presentations and hands-on labs that are going on next week. You can search the schedule to find your favorite presenter, export the schedule to CSV and PDF and most importantly, bookmark the link for onsite viewing with your iPhone.

Updated Statement of Direction

2009-10-05T23:06:00.005-04:00

Just in time for Oracle OpenWorld we updated our Oracle Application Express 4.0 Statement of Direction. The biggest new additional is the new 'Team Development" feature, which will make development of APEX applications as a team even easier and more productive. We already adopted "Team Development" for our own work on APEX 4.0, and it’s been really useful, thanks to its tight integration with the Application Builder and the ability to provide direct feedback for any APEX page. Stop by at the demo grounds new week at Oracle Open World to see this new feature in action.

Using SQL Developer and the Data Modeler to aid your Oracle APEX development.

2009-08-10T14:51:00.002-04:00

After using the recently introduced Oracle SQL Developer Data Modeler for a number of projects, I thought it would be fun to talk about how all these wonderful tools we develop in the Database Tools group can be used together to improve our productivity as Software Developers and explore some of numerous integration points of Oracle Application Express, Oracle SQL Developer and the new Data Modeler.

So on Wednesday, September 9th, I will actually have to opportunity to demonstrate these tools as the speaker at the NYOUG Web SIG meeting, which happens to take place here in my New York office, at 520 Madison Avenue. We’ll look at how to create new and enhance existing database models with the Data Modeler, then build an APEX application on top of these data structures and use SQL Developer to browse APEX information and to remotely debug your APEX application's PL/SQL code. If you happen to be in there area, please stop by, additional information can be found here:

NYOUG WebSIG meeting, Wednesday, September 9th

Additional presentations about Advanced Printing with APEX

2008-11-07T19:33:00.002-05:00

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.

Advanced Printing in Application Express

2008-11-01T13:07:00.002-04:00

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

Oracle APEX Forms Converter at NYOUG

2008-08-29T15:34:00.002-04:00

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

APEX 3.1.2 Patch Released

2008-08-29T15:31:00.001-04:00

Oracle Application Express 3.1.2 has been released to. The full installation is available here, the patchset is available on MetaLink - patch number 7313609. Please read the Patch Set Notes for details on the bugs fixed in this release.

Oracle Application Express with Oracle eBusiness Suite

2008-08-14T15:15:00.001-04:00

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.

WebCast: What’s next for Oracle Application Express?

2008-07-16T11:14:00.002-04:00

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.

Integrating Google Maps

2008-06-20T19:46:00.006-04:00

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:



  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();
  }

Verifying that the PDF print server works

2008-06-18T10:14:00.008-04:00

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

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

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

Static HTML form to test print server:

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

Sample XML Data:

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

Sample XSL-FO style sheet:

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

Using Dynamic Images in PDF Reports

2008-06-13T11:52:00.007-04:00

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.

Austrian Competence Center for Oracle APEX

2008-06-11T11:03:00.008-04:00

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

Marc Sewtz, Patrick Wolf, Ingrid Kriegl and Carsten Czarski

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

http://www.sphinx.at/it-consulting/de/home/

Enabling end users to create their own PDF report layouts

2008-05-15T21:01:00.007-04:00

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.

Where Parking Rules the Days, a Little Miracle

2008-05-15T12:30:00.012-04:00

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

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

2008-04-20T13:33:00.015-04:00

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.

Generating CSV files and storing them in the database

2008-04-18T13:46:00.010-04:00

This week I faced the following problem: we wanted to generate CSV files based on SQL queries, and store them in the database rather than directly downloading them from a report region. We also wanted a report, from which you could choose which CSV file to download and lastly, the rows in those CSV files could potentially exceed the 32k limit that we currently have with standard report regions.

In Application Express 3.1 we made it really easy to store files in tables with a BLOB column and to build reports on those tables with a download link that allows downloading the files to your client. So this feature takes care of storing and downloading the CSV files. No I only needed to write some logic to actually generate the CSV file and insert it into a table.

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

create or replace function get_report ( p_query varchar2 ) return blob is l_cursor integer; l_cursor_status integer; l_col_count number; l_desc_tbl sys.dbms_sql.desc_tab2; l_col_val varchar2(32767); l_report blob; l_raw raw(32767); begin -- open BLOB to store CSV file dbms_lob.createtemporary( l_report, FALSE ); dbms_lob.open( l_report, dbms_lob.lob_readwrite ); -- parse query l_cursor := dbms_sql.open_cursor; dbms_sql.parse(l_cursor, p_query, dbms_sql.native); dbms_sql.describe_columns2(l_cursor, l_col_count, l_desc_tbl ); -- define report columns for i in 1 .. l_col_count loop dbms_sql.define_column(l_cursor, i, l_col_val, 32767 ); end loop; -- write column headings to CSV file for i in 1 .. l_col_count loop l_col_val := l_desc_tbl(i).col_name; if i = l_col_count then l_col_val := '"'||l_col_val||'"'||chr(10); else l_col_val := '"'||l_col_val||'",'; end if; l_raw := utl_raw.cast_to_raw( l_col_val ); dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw ); end loop; l_cursor_status := sys.dbms_sql.execute(l_cursor); -- write result set to CSV file loop exit when dbms_sql.fetch_rows(l_cursor) <= 0; for i in 1 .. l_col_count loop dbms_sql.column_value(l_cursor, i, l_col_val); if i = l_col_count then l_col_val := '"'||l_col_val||'"'||chr(10); else l_col_val := '"'||l_col_val||'",'; end if; l_raw := utl_raw.cast_to_raw( l_col_val ); dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw ); end loop; end loop; dbms_sql.close_cursor(l_cursor); dbms_lob.close( l_report ); -- return CSV file return l_report end;

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

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

logon as demo/demo123

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

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

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

Oracle Application Express 3.1 available now!

2008-03-03T17:19:00.001-05:00

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:

http://apex.oracle.com/pls/otn/f?p=2357:8

Using application and page items in PDF reports

2007-10-24T14:42:00.000-04:00

One of the benefits of the advanced printing capabilities built into Oracle Application Express is that users are able to include application and page level item values in their print documents along with the actual report result-set. These values can be used to filter the report result set when used as bind variables in the where-clause. And they can also be used as additional dynamic data shown along with the report result-set, e.g. dynamic page headers and footers or showing the master record of a master detail form.

A common challenge users face when integrating page level items is that values need to be written to session state first before they can be referenced in the print document. If your print button or link is located on a different page than your page items, this won’t be a problem because when navigating to the print page, your item values get written to session state. But in some scenarios, you have both the page items and your print button or link on the same page, an example would be a search form and report page, where you use your search form to limit your search results, another example would be a form & report page (e.g. master-detail), where you want to fill in some values into the form, and then print the document without navigating to another page.

An easy way around the session state issue is actually writing session state without leaving the page. This can be done by performing a submit action, but then, rather than redirecting to another page (or reloading the current page), you would directly initiate the PDF download. When working with report queries, this can be achieved very easily. Rather then specifying the print URL as the redirect target of a button, you would make the button a submit button, and then define a branch to the pseudo page 0 and use a special request string, referencing your report query.

The syntax for the request string is:

PRINT_REPORT=[your report query name]

So if your report query were called “employees”, then your request string would be:

PRINT_REPORT=employees

Similar to report queries, this technique can also be used when working with report regions. This is particularly helpful when working with search or filter regions and you want the filter to be applied to both your result-set on your web page as well as the print document. In this case, you would print-enable your report query (on the print attributes page) but not specify a print link. Instead you would create a submit button similar to the sample above and create a branch that is executed when this button gets pressed. In this scenario, the target page of your branch would be the current page, and just like shown above, you would define a special request string:

FLOW_XMLP_OUTPUT_R[Report Region ID]

This request causes the page not to reload but initiate the PDF download instead. To specify this request, you need to lookup the region ID of your report regions. So if for example your report region ID is 90104803966374878, then the request would be:

FLOW_XMLP_OUTPUT_R90104803966374878

In summary, there are cases where item values are not available in session state when you need them for printing your documents. And in order to make sure you write them to session state on time, always perform a submit action before initiating the download of your PDF document.

2007-08-24T10:04:00.000-04:00

We have a new statement of direction available on OTN outlining our road map for the next two releases of Oracle Application Express:

http://www.oracle.com/technology/products/database/application_express/apex_sod.html

Having written the current reporting engine, incl. tabular forms, I’m very excited about finally getting to work on the much asked for tabular form validations and of course after adding PDF printing in 3.0, I’m looking forward to continue my work on this feature as well.

After working through Patrick’s APEX 3.1 enhancements thread on the OTN forum, I think much of what customers had asked for will actually make it into 4.0 (3.1 will just be an incremental update).

Here’s what we have planned:

Oracle Application Express 3.1

An incremental release of Oracle Application Express, 3.1, will incorporate the following:

- Optional runtime-only installation, which will install the minimum number of database objects and grant the minimum number of privileges to run Application Express applications in a production environment
- PL/SQL API to manage a runtime installation of Application Express
- Documented and supported Application Express JavaScript libraries
- Enhanced integration with Oracle SQL Developer for MS Access to Application Express migration

Oracle Application Express 4.0

The next major release of Oracle Application Express will be 4.0. This release will focus on declarative support for Web 2.0 features in Application Express, as well as the extensibility of the Application Express framework. Application Express 4.0 will incorporate the following:

- Interactive reporting region types which natively integrate Web 2.0 features to filter, break, sort data, etc.
- Extensible item framework, enabling developers to "plug in" their own custom item types
- Declarative support for AJAX validations
- Declarative support for cascading select lists and other similar item types
- Improved tabular forms, including support for validations, integration with collections, and additional item types
- New item/region types, including DHTML calendar and AJAX tree
- Improved PDF report layout
- Enhanced print attributes allowing for more control of PDF document structures
- Improved error message handling
- Numerous functional and performance improvements

Oracle PL/SQL Programming Conference

2007-08-16T16:15:00.000-04:00

After presenting Oracle Application Express and Oracle SQL Developer at a number of events in Europe during the month on June, I’ll be presenting SQL Developer next month much closer to home for a change – on September 19th I’ll be across the Hudson River in Newark, NJ at the Oracle PL/SQL Programming Conference and talk about performing common database tasks with Oracle SQL Developer. It would be great to see you there, you can learn more about this event here:

http://www.odtugopp.com/

Application Express 3.0.1 & Oracle Develop Conference

2007-06-14T08:24:00.000-04:00

It’s been a busy few weeks since my last post. Lots to do getting Application Express 3.0.1 ready, and preparing for the upcoming Oracle Develop Conference in Europe, where I will be presenting Application Express, do an APEX hands-on lab, and two sessions on SQL Developer. We just released version 1.2 of SQL Developer, and I’m really excited about the ever closer integration with Application Express. Your APEX applications now show up in the object tree just like any other database object, you can drill down to pages, regions, shared components, etc and you can even import and export APEX applications from within SQL Developer. I’ll write more about this soon.

First though, it’s off to Europe for the Oracle Develop Conference. In case you’re in the area, here are the dates again: Munich (June 18-19), Prague (June 21-22), and London (June 26-27). To find out more, go to this page:

http://www.oracle.com/technology/events/develop2007/index.html

Here’s an overview of my sessions, they’ll be the same at all three locations:

Day 1:

12:15 - 13:15: Introducing Oracle SQL Developer: Features and Futures
16:15 - 18:30: Hands-on Lab: Oracle Application Express - Building a Functional Application

Day 2:

14:45 - 15:45: Advanced Database Development with Oracle SQL Developer
17:30 - 18:30: Oracle Application Express, Now and in the Future

Hope to see you there.

Get your BIP on

2007-04-18T20:53:00.000-04:00

Looks like the standalone release of Oracle BI Publisher 10.1.3.2 - which you need when using the product as the PDF rendering engine for Oracle Application Express - is nearing production:

http://blogs.oracle.com/xmlpublisher/2007/04/16

Of course you can already get it today from the BIEE10.1.3.2 installer, which is available for download on OTN:

http://www.oracle.com/technology/software/products/ias/htdocs/101320bi.html

I found this BLOG entry particular useful, because it points out the exact version of the Java SDK required, and provides other useful links and information on how to set this up.

Report Queries and Session State

2007-04-13T13:08:00.000-04:00

With Oracle Application Express 3.0 we added the ability to print report regions as PDF documents (or export to Word or Excel). Setting this up is fairly straightforward, you just go to the report attributes page or region definition page, and there you'll find a new tab to set up printing attributes. All you have to do is change the "Enable Report Print" attribute to "yes", run your page and click on the print link to download your document, provided of course you or your system administrator has already configured a rendering engine, such as BI Publisher.

Not as obvious or well known is "the other printing feature", called report queries, which I had already talked about in my previous posting about PDF & charts. I think it's worth taking another look at this option. The main difference between the two printing options is that with report region printing, you need to have a report region defined, and of course, that also requires having a page defined. But often users just want to integrate a number PDF reports, based on real-time data, with their application. That's where report queries come in, they can be easily integrated with existing applications, just by calling them through standard APEX buttons, page branches, tabs, list items, or any other navigational component, that allows using a URL as the link target.

Besides being available as a stand-alone component, report queries also make it very easy to associate RTF report layouts with them, for maximal customizability of your report documents. The create report query wizard helps setting this up, here's a brief over of the steps involved:

- Go to Shared Components / Report Queries
- Click on Create
- Enter your query, click next
- Test your query to ensure you're getting results back
- If you used bind variables, enter test values, those values will be used throughout the wizard
- Optionally include session state of page or application items (more about this later)
- Download your report data as an XML file
- Open that file in the BI Publisher Template Builder Word plug-in
- Use the plug-in to design your report layout
- Save the finished layout as an RTF file
- Load the RTF file into your APEX wizard and click next
- Test the output, and if satisfied, use the URL shown to integrate with your APEX application

With this technique, you can create pretty much any report layout. You can include images, like company logos, you can define grouping and break columns, you can include charts (see previous posting), you can show sums for numeric columns, etc. The main advantage of using this in Oracle Application Express though is that you can actually include session state of page items and application items as I mentioned above.

So what does that mean and why would you want to include session state? It means that you're not limited to your query result-set in what data you can include in your PDF documents. In fact, you're not even limited to what's stored in your database tables. You can actually include data from your current session. So if you have a classical master-detail form, like an order and order item form, you could include the current session values of your page items that hold your order (master) data as well as the report containing your detail order items. You can also use Application Express to fill out standard forms, like e.g. a W-2 tax form, and print out the completed form in PDF. We created a sample application that's doing exactly that, you can try out the application here:

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

And download it form here:

http://www.oracle.com/technology/products/database/application_express/html/3.0_new_features.html

Once you have downloaded the application and installed it, go to shared components, and take a look at report layouts. There you'll find the W-2 RTF document, which was created in Word. This document illustrates how you can include page and application items as well as report rows with your PDF documents.

Now one final tip: if you're looking at the PDF demo application, you will see that on page 3 (the form page), we did not use a button that simply redirects to the report query URL. Instead we used a standard submit button, and then we used a branch pointing to page 0, specifying the request string as print_report=[report name]. This ensures that the data actually gets written to session state before rendering the PDF. This is important, because otherwise you would print the PDF with the data that has previously been written to session state and not what you see on your screen. And in pointing to page 0 with this special "print-report=" request, we make sure that the user doesn't actually leave the page, but instead gets file open / download dialog to view the finished PDF document.

Oracle Develop hits the road

2007-04-11T16:49:00.000-04:00

This spring, Oracle Develop—the premier conference for developers—is coming to a city near you. Don't miss it. You'll experience two days of expert-led, in-depth technical sessions, hands-on labs, advanced how-tos, and detailed tutorials.

I'll be presenting Oracle Application Express 3.0 New Features and Oracle SQL Developer at the events in Munich, Prague and London. I'll be also doing an Application Express 3.0 hands-on lab.

June 18-19, 2007 | Munich, Arabella Sheraton
June 21-22, 2007 | Prague, Prague Conference Center
June 26-27, 2007 | London, ExCeL

If you have a chance to attend any one of those events, I'm looking forward to meeting you there.

Including Charts in PDF documents

2007-04-11T15:40:00.000-04:00

One question that is often asked about the new PDF printing feature in Oracle Application Express 3.0 (APEX) is whether it is possible to include charts in the PDF document. And the answer is yes, it's actually quite easy to implement using the BI Publisher template builder plug-in for MS Word (and BI Publisher as the PDF rendering engine for APEX). It's important to understand however, that this is not a "printer-friendly" version of your web page. So if you have e.g. a Flash chart region and a report region on your page, you can't simply print this as PDF. What the PDF printing feature in APEX does is, it generates XML data of your report result-set and using an XSL-FO report layout (or RTF template), the XML data is transformed into PDF (or other supported output formats). Which is then send back to the browser for download.

So in order to create a PDF document that contains e.g. a chart and report, you need to create a report query and associate that report query with an RTF based report layout. Report queries can be found under Shared Components.

I created a simple demo application, which can be found here:

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

In this sample I created a report region on the EMP table and a pie chart using the new APEX 3.0 Flash charts. The chart shows the sums of the salary column for each job title. I then created a report query, using the report query create wizard under Shared Components. I entered the same query that I used for the reports region. I tested the query and proceeded to the page that lets me download the XML data of my report. I downloaded the file and opened it in the BI Publisher template builder plug-in for MS Word.

You can fine more information on the template builder and BI Publisher in general here:

http://www.oracle.com/technology/products/applications/publishing/index.html

Using the template builder, I created a chart and report definition and saved this as an RTF file, which I then uploaded back into Application Express. The RTF report layout can be downloaded on the report page of my demo application, there's a button called "Download RTF report layout" next to the Print button.

After completing the wizard, I copied the print URL and made this URL the target for my "Print Chart and Report" button. Now when I press this button, I'm getting a PDF document, which is quite similar to my web page, it includes a pie chart and the report. Of course there are differences, but a PDF document is intended for printing, and a web page is intended for being viewed in a browser. So some differences are to be expected.

... getting started

2007-04-11T10:02:00.000-04:00

Time to get started with my own BLOG on Oracle Application Express. Of course I’ve been wanting to do this for a long time, but never quite got around to it. With Oracle Application Express 3.0 available now though, I’d like to do my part in helping the APEX community make the most out of all the new features. And after I found myself answering many APEX 3.0 related questions on our OTN forum, in particular regarding the new PDF printing feature, I figured this would be a good place to collect some of these answers, tips and tricks.