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

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

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

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

Generating CSV files and storing them in the database

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

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

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

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

l_report blob;
l_raw raw(32767);
begin

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

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

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

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

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

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

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

-- return CSV file
return l_report

end;

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

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

logon as demo/demo123

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

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

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

Oracle Application Express 3.1 available now!

Oracle Application Express 3.1 was released a few days ago, you can download the latest release here:

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

Many of you have probably already heard of or even tried out the new interactive report regions. If you haven’t heard about this new region type yet, I’d recommend starting with the brief introduction which can be found here:

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

In addition to interactive report regions, there have been numerous other enhancements, and one of my focus areas were the enhancements to report printing. The two main enhancements in this area are support for multiple SQL source statements for report queries, allowing to combine several reports or combinations of reports and charts in a single print document, and additionally, the introduction of a PL/SQL print API. I will cover these enhancements and some of the other changes on my Blog in the coming weeks. For now, I’d like to point out the 3.1 new features application, which showcases some of the printing enhancements along with interactive reports and other 3.1 new features:

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

Using application and page items in PDF reports

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.

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

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

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.