The Oracle APEX Award

Just announced today is a competition in Germany called The Oracle APEX Award, with a grand prize of € 5,000 (approximately $7700 USD). Now it can't be just any application - it should be business related, based on APEX 3.0 or later, delivered with supporting objects and an installation script (so it is a single file deliverable, just like the Packaged Applications), and run on Oracle Database 10g or Oracle Database 11g.

Beyond the money, though, all Oracle Application Express solutions from the competition will be promoted by Heise, Mittelstandswiki and on the German Oracle General Business Sites.

In case you were worried that this competition would be won by Patrick Wolf, he can't participate, as he is one of the judges.

Why APEX?

Why use Application Express? What is it good for? Good questions.

Today I stumbled upon a 3 minute viewlet about Oracle Application Express. And I honestly couldn't tell you who did this, although I do remember David Peake (the Application Express Product Manager) making mention of this a month or two ago.

If you're curious about Oracle Application Express, this is worth 3 minutes of your life.

Zip it!

For those of you thinking this is a posting about Dr. Evil from the Austin Powers movie, you'll be disappointed. However, if you have ever wanted a way to create a zip file in PL/SQL in an Oracle database, then this one is for you.

This question has come up many times over the years. There are many ways to export files from Oracle Application Express (applications, images, data in XML, etc.), but wouldn't it be great if this could be downloaded as a single file, or at a minimum, as a zip archive of these files? Of course - but I was never aware of a simple way to pull this off.

Then along comes the Oracle Multimedia team (a supremely competent and professional team - led by Sue Mavris) . At Oracle OpenWorld last year, the Oracle Multimedia team demonstrated DICOM support in the 11gR1 database. They created a couple applications in Application Express for this demonstration. I was recently reviewing these applications and I came across an interesting piece of code which does the creation of a zip archive for any number of BLOBs. All credit for this code must be given to Fengting Chen from the Oracle Multimedia team - I just modified it slightly for this posting.

This solution requires the Java VM in the Oracle database. I tried this out on an 11gR1 11.1.0.6 database, but I imagine this may work for earlier database versions as well. It's nothing more than a Java class that is compiled into the database and then a call specification which is used to publish the Java method to SQL and PL/SQL. It's unbelievably elegant.

Using either SQL*Plus or SQL Commands in SQL Workshop, run the following script:

create or replace java source named ZipLobs as<br /><br />//---------------------------------------------------------------------------<br />// File name: ZipLobs_java.sql<br />// Description: Java Stored Procedure that zips the data of BLOBS returned<br />// by the query and writes into a destintation BLOB.<br />//---------------------------------------------------------------------------<br /><br /><br />import java.util.zip.ZipEntry;<br />import java.util.zip.ZipOutputStream;<br />import java.io.OutputStream;<br />import java.sql.Blob;<br />import java.sql.Connection;<br />import java.sql.Statement;<br />import java.sql.ResultSet;<br />import oracle.jdbc.OracleDriver;<br /><br />public class ZipLobs {<br /> public static int writeZip(<br /> String query,<br /> Blob[] outLob) {<br /> try{<br /> //ceate the zipoutputstream from the end of the outLob<br /> Blob zipLob = outLob[0];<br /> OutputStream os = zipLob.setBinaryStream(1);<br /> ZipOutputStream zos = new ZipOutputStream(os);<br /><br /> //execute the query.<br /> int chunksize = 32768;<br /> Connection conn = new OracleDriver().defaultConnection();<br /> Statement stmt = conn.createStatement();<br /> ResultSet rset = stmt.executeQuery(query);<br /> while (rset.next()) {<br /> // get the id and the data columns<br /> String filename = rset.getString( 1 );<br /> Blob src = rset.getBlob( 2);<br /><br /><br /> //add one zip entry<br /> ZipEntry entry = new ZipEntry(filename);<br /> zos.putNextEntry(entry);<br /><br /> //write data to the zip lob<br /> long len = src.length();<br /> long offset = 1;<br /> byte[] buffer;<br /> while (offset < len) {<br /> buffer = src.getBytes(offset, chunksize);<br /> if (buffer == null) <br /> break;<br /><br /> zos.write(buffer, 0, buffer.length); <br /> offset += buffer.length;<br /> }<br /> zos.closeEntry();<br /> }<br /> zos.close();<br /><br /> outLob[0] = zipLob;<br /><br /> }catch (Exception e) {<br /> e.printStackTrace(System.out);<br /> return 1;<br /> }<br /> return 0;<br /> }<br />}<br />/<br />

After this is compiled, it will create an object of type JAVA SOURCE as well as another object of type JAVA CLASS, both of which may initially be invalid. To correct this, issue the statement:

alter java source ziplobs compile;

Lastly, create the call specification to make this class visible from PL/SQL:

  --
  -- The zip utility that zips data in multiple BLOBs and returns
  -- into a single BLOB
  --
  -- Parameters:
  --   p_query:  The SQL query which must select the filename as the first
  --             column and the BLOB data to be zipped as the second column
  --
  --   p_zip:    BLOB variable which will contain the contents of the zipped BLOBS
  --
  -- Return      0: Success 
  --             1: Error occured
  --
  -- Example:
  --
  --   ziplobs('select fname, resume from docs where id < 10', l_zip );
  --
create or replace function ziplobs(
    p_query    in varchar2,
    p_zip      in out nocopy blob )
return number
as language java
  name 'ZipLobs.writeZip(
        java.lang.String,
        java.sql.Blob[]) return int';
/

That's all there is to it! Now you have a procedure which you can use to zip all sorts of things. You could use this to zip up a collection of PDF's and present it as a single file download to the end user. You could author a database job which uses DBMS_XMLGEN to generate XML files for the data in your tables, zip all of them up, and store it directly in the database. You could write a procedure which takes a number of files, zips them up, and e-mails them.

Using the PL/SQL package APEX_MAIL which supports e-mail attachments (as of Application Express 3.1), here's a practical example:

declare
    l_id number;
    l_blob blob;
    l_dummy integer;
begin
    l_id := apex_mail.send( p_to => 'fred.flintstone@bedrock.com',
                            p_from => 'barney.rubble@bedrock.com',
                            p_subj => 'Images',
                            p_body => 'Here are the images I promised -- Barney' ); 
    dbms_lob.createtemporary( l_blob, TRUE );
    l_dummy := ziplobs( 'select filename, blob_content from apex_application_files ' ||
                        'where mime_type like ''%gif%'' and rownum < 5', 
                        l_blob );
    apex_mail.add_attachment( l_id, l_blob, 'ImagesForFred.zip', 'application/zip');
    commit;
end;
/

While I would like to think that this would eventually make it into a distribution of Application Express, the biggest impediment is the dependency upon Java in the database. We removed this dependency beginning with Application Express 2.2, at the request of a number of customers. But with that said, I'm sure this will make its way into a number of packaged applications and other utilities provided by our team.

Preserving checked checkboxes in a report

Paul from Oracle recently sent me an e-mail with the following request:

"I am using the report template to display the database table contents. Before each report data in the display I added a checkbox to allow user to select the data. I can get the checked data id from global package array apex_application.G_F01 when user submits the page. However, this array is cleared whenever the user browses the report to the next page using the ApEx navigation. How could I save the checked checkbox when user is browsing the report using "next" or "previous" links? This issue also appears if you change the number of rows displayed. I looked on the APEX forum on OTN and could not find a solution. Any help you can give is greatly appreciated."

I understood the problem exactly. Paul has a report on a page with a checkbox column (presumably using APEX_ITEM.CHECKBOX). A user will check a few boxes and then click the Next link to display the next set of rows in the report. But now when the user goes back to the first page, the original checked checkboxes are no longer checked. This is because the checked checkboxes are not preserved in session state.

I poked around the OTN forum myself and could not find this addressed. I even browsed Carl's and Denes' demonstration applications, and even though there are all kinds of exotic examples, this simple (and seemingly common problem) didn't seem to be addressed - at least not for the common man.

Using Application Express collections and a little AJAX, we should be able to solve this rather elegantly.

In my workspace on apex.oracle.com, I:

1. Started the creation of a new application
2. Added a report, Page Source = Table, Table Name = EMP, Implementation = Classic
3. Clicked the Create Button

When running this application, you should get something that looks like:




Standard stuff, right?

Now, to add the checkbox using the APEX_ITEM.CHECKBOX API, I changed the SQL query and added one line:

select
apex_item.checkbox(1, empno) cbox,
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
from "EMP"
where
(
instr(upper("ENAME"),upper(nvl(:P1_REPORT_SEARCH,"ENAME"))) > 0 or
instr(upper("JOB"),upper(nvl(:P1_REPORT_SEARCH,"JOB"))) > 0
)


Now, after shuffling the cbox column up in the report display order and clearing out the column header, it should now look like:


Now here's where the fun starts. You can liken Application Express collections to temporary tables - they're an amorphous bit bucket to store vectors of information, in contrast to the scalar declarative item types. The nice thing about collections is that they are tied completely to an Application Express session. As those get cleaned up automatically, so does the data in collections. So we will use an Application Express collection to maintain a list of the EMPNO values of the checked rows.

First, I created an unconditional On Demand application-level process named "UpdateCheckboxValue" using the following code:

declare
    l_value  varchar2(4000);
    l_seq_id number := 0;
    l_collection_name constant varchar2(30) := 'EMP_COLLECTION';
begin
    --
    -- Get the value of the global which will be set in JavaScript
    --
    l_value := wwv_flow.g_x01;

    --
    -- If our collection named EMP_COLLECTION doesn't exist yet, create it
    --
    if apex_collection.collection_exists( l_collection_name ) = FALSE then
        apex_collection.create_collection( p_collection_name => l_collection_name );
    end if;

    --
    -- See if the specified value is already present in the collection
    --
    for c1 in (select seq_id
                 from apex_collections
                where collection_name = l_collection_name
                  and c001 = l_value) loop
        l_seq_id := c1.seq_id;
        exit;
    end loop;

    --
    -- If the current value was not found in the colleciton, add it. Otherwise, delete it from the collection.
    --
    if l_seq_id = 0 then
        apex_collection.add_member(
            p_collection_name => l_collection_name,
            p_c001            => l_value );
    else
        apex_collection.delete_member( 
            p_collection_name => l_collection_name,
            p_seq             => l_seq_id );
    end if;
    commit;
end;

So this elegant piece of code will take care of creating a collection named EMP_COLLECTION, and then determine if we should add or remove the value from the collection. The name of the process, "UpdateCheckboxValue", is relevant, as we'll use it next.

Next, after glancing at Carl's simple AJAX and On Demand example, I figured out the JavaScript I needed. So I edited the page attributes of page 1 and added the following to the HTML Header section:

<script type="text/javascript">
<!--

function f_UpdateCollection( cb ){
var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=UpdateCheckboxValue',0);
get.addParam('x01',cb.value);
gReturn = get.get();
get = null;
}
//-->
</script>



Now, I have a JavaScript function on my page which will invoke the On Demand application-level process I defined above. All I need to do is hook it up. To do that, I can now modify the call to APEX_ITEM.CHECKBOX to invoke this function in an 'onchange' event:

select
apex_item.checkbox(1, empno, 'onclick="f_UpdateCollection(this)"') cbox,
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
from "EMP"
where
(
instr(upper("ENAME"),upper(nvl(:P1_REPORT_SEARCH,"ENAME"))) > 0 or
instr(upper("JOB"),upper(nvl(:P1_REPORT_SEARCH,"JOB"))) > 0
)

If you now run the application and click the checkboxes, you may notice a slight lag. That's because, behind the scenes, a request is being made from your browser to the Application Express engine, which is calling the On Demand application-level process and modify data in the collection for your session.

But we're not quite done. We need to somehow "merge" what we have stored in the collection with the query for our report. This can be achieved by modifying the call to APEX_ITEM.CHECKBOX to add the fourth argument which indicates if a value should be checked or not, and also modifying the query itself to perform an outer join against APEX_COLLECTIONS.

select
apex_item.checkbox(1, empno, 'onclick="f_UpdateCollection(this)"',a.c001) cbox,
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
from "EMP", apex_collections a
where
(
instr(upper("ENAME"),upper(nvl(:P1_REPORT_SEARCH,"ENAME"))) > 0 or
instr(upper("JOB"),upper(nvl(:P1_REPORT_SEARCH,"JOB"))) > 0
)
and a.c001 (+)= empno
and a.collection_name (+)= 'EMP_COLLECTION'


This query will still report on every row in the EMP table, and optionally on the EMP_COLLECTION where the join condition is met. In the reference to a.c001 in the call to APEX_ITEM.CHECKBOX, for those rows in EMP for which there is no match, the resultant value will be NULL. For those rows which do match (a.c001 = EMPNO), then the value of
a.c001 should be the corresponding EMPNO value, and this will result in the checkbox being checked.

There certainly will be a need to clear the collection, so I added a PL/SQL process on the page which will execute on Submit and when the Reset button pressed of:

apex_collection.create_or_truncate_collection(
    p_collection_name => 'EMP_COLLECTION' );
commit;

This will clear the collection if it exists, and create it if it doesn't exist. I easily could have used apex_collection.truncate_collection, but I'd have to account for the case where the collection is not already created.

The complete application is here: http://apex.oracle.com/pls/otn/f?p=blog_20080319:1:0

I completely realize some "expert" is going to flame me, telling me this was solved in 2004 and here's the proof. But I was astonished that I couldn't find an article discussing this type of problem and solution.

Caution: The implementation above can be quite "chatty". Every time someone clicks a checkbox, they will make a request to the Web server and the database. There are other ways to implement this solution, but this will work for reports with and without PPR.

Web browser "compatibility"

Is this an article dealing with Oracle Application Express? Barely.

I recently read the latest treatise from Joel Spolsky of "Joel on Software" fame. Joel does an excellent job of explaining the history of the Web "standards" which have gotten everyone into a little bit of a mess. And even though standards have been developed for HTML and Web browsers, it's always a challenge to maintain backward compatibility.

http://www.joelonsoftware.com/items/2008/03/17.html

When I read this, I couldn't help but think of poor Carl on our team who wrestles with these same challenges for every release of Oracle Application Express. It's hard enough to produce a Web application development environment which enables you to build applications to work elegantly with Firefox and Internet Explorer. This is why you don't see certification for Opera, Netscape Navigator, Safari and the like. They're all a little bit different.

I'm still waiting for the Oracle Application Express theme which supports Lynx.

Oracle Application Express and NTLM authentication over HTTP

Jason Straub, my friend, work colleague and fellow member of the APEX Columbus Development Organization, has decided to join the Oracle Application Express blogging world. Jason's first posting is about NTLM authentication with Oracle Application Express and how this can be achieved without the use of mod_ntlm.

http://jastraub.blogspot.com/2008/03/ntlm-http-authentication-and.html

And the winners are.....

In mid-February, I solicited the feedback from the APEX community about Application Express and which languages we should consider translating to. In later February, Sharon also posted an announcement on the Application Express discussion forum on OTN.

With the crush of the APEX 3.1 release behind us, I decided to review the results. And they were surprising. Out of a total of 149 legitimate responses, the top 5 were:

1. Hungarian
2. Russian
3. Arabic
4. Dutch
5. Polish

Granted, I can't claim that this was a scientific survey. It only reached the folks who decided to read my blog posting (unlikely) or follow the link from the announcement on the OTN discussion forum. Or maybe the folks in Hungary started an e-mail campaign and decided to stack the deck. Regardless, this gives me enough information now to approach the managers in these respective regions to assess market opportunity.

Thank you to everyone who provided your invaluable feedback. The full results can be viewed here:

http://apex.oracle.com/pls/otn/f?p=apex_lang_survey:survey_results:0

Oracle Application Express Technology Briefing

On Thursday, March 27, 2008, Oracle and Progressive Skill will present a technology briefing on Oracle Application Express. The event location is Smith & Wollensky Restaurant. Discussions will include:

• Reasons for failed/delayed custom software projects
• Advantages of Rapid Application Development
• How Oracle Application Express facilitates Rapid Development better than any development tool on the market
• Successful software applications built using Oracle Application Express
• Real world examples of custom built Oracle Application Express Applications

For more information and to register, you can go here.

Application Express 3.1 released on OTN

Today, February 29, 2008, Application Express 3.1 was released for download on the Oracle Technology Network. Start at http://apex.oracle.com, follow the link to Download Application Express, and away you go.

Sharon posted an announcement here.

apex.oracle.com upgraded to Application Express 3.1

Yesterday, February 27, the hosted demonstration instance of Oracle Application Express at http://apex.oracle.com was upgraded to Application Express 3.1. This is one of the last milestones in the release criteria for Application Express. If we can successfully upgrade an instance with over 11,000 workspaces and over 30,000 applications, this usually bodes well for the product release.

It's an incredibly valuable learning experience running and managing apex.oracle.com. There's nothing like using your own software.

There were over 1,100 participants in the hosted Early Adopter's release of Application Express 3.1. The feedback was invaluable, from comments to suggestions to bug reports. The Application Express community has my gratitude.

Joel