Friday, November 21, 2008

Change is Coming....

Change is coming...and no, I'm not referring to the forthcoming change in Washington. I'm referring to Oracle Application Express.

Since the first supported release of Application Express (Oracle HTML DB 1.5), Application Express has been delivered as a supported feature of the Oracle Database, supporting database releases 9.2.0.3 and higher. So even though Oracle HTML DB 1.5 was delivered as a feature of the Oracle Database Release 10gR1, a customer could actually download it from the Oracle Technology Network, install it in their Oracle Database 9iR2 9.2.0.3, and be in a supported configuration.

For the forthcoming release of Oracle Application Express 3.2, which introduces Oracle Forms Conversion, the minimum database version will continue to be 9.2.0.3. But for Oracle Application Express 4.0, the minimum database version will be Oracle Database 10gR2 10.2.0.x - possibly even 10.2.0.4.

Thursday, November 13, 2008

What's up, DOAG?

It's pronounced "what's up, dog?"...or if you're a Cleveland Browns fan like I am, it's pronounced "what's up, Dawg?" (my thanks to Sergio for this play on words).

The conference of the German Oracle User's Groups, Deutsche Oracle-Anwendergruppe 2008 Konferenz + Ausstellung (DOAG), is happening Monday 01-DEC-2008 through Wednesday 03-DEC-2008 in Nürnberg, Germany. Here is the conference program in German and English. There are a fair number of presentations about Oracle Application Express, including mine about what's coming new in Oracle Application Express in 2009.

I'm looking forward to the entire conference. Maybe some of the local attendees can take us on a walking tour of the Nürnberger Christkindlesmarkt.

Saturday, November 01, 2008

Carl Backstrom Memorial Announcement

Please join the family in celebrating the life of Carl Backstrom

on Thursday, the sixth of November

two thousand and eight

at one o'clock in the afternoon

Orange Terrace Park

20010 Orange Terrace Park Parkway

Riverside, CA 92508



In lieu of flowers the family has set up a Memorial Fund
in behalf of Carl's daughter, Destany.


Donations to Carl's Memorial Fund can be made several ways:

Domestic wire transfers
Account Number 152460903
Citibank ABA Number 322271724
International wire transfers SWIFT Code: CITI US 33
Checks
Make payable to Susan Bailey (Carl's Mother)
Address: 3395 S. Jones Blvd #403
Las Vegas, NV 89146

“And in the end, it's not the years in your life that count. It's the life in your years.” - Abraham Lincoln





Carl Backstrom Memorial Annoucement
Get your own at Scribd or explore others:

Open Source Web Design

Maybe everyone else on the planet knew about OSWD other than me. Regardless, here goes...

My brother-in-law, Matt Wagner, recently asked me to help him with his Web site. This was an interesting proposition, because I have not invested that much time learning about proper Web design (I never had to - I'm a database guy and we always had Carl and Marc for that kind of stuff). So I took this as an opportunity to actually learn something and I embraced the challenge. My brother-in-law already had his domain name, and he had a rough idea about the layout and the content that he wanted to put on his Web site, but he didn't know the first thing about HTML or how to propagate this information out to GoDaddy.

The last conversation I had with Carl was a week ago and I wanted to get his feedback about what I had done with Matt's Web site. Here is what pointed Carl to. I was so proud of myself, having figured out how to make some practical use of styles and also my over-the-top use of the effects from MooTools. Let me tell you - Carl laughed and laughed. He said the colors were odd, there was no contrast with the font and the background, the effects were funny, and he encouraged me not to use Serif fonts ("just not in style"). He also told me, with a chuckle, that I should start practicing jQuery and forget MooTools.

Carl did point me to the Open Source Web Design site and told me to pick one. For someone like me, who is artistically and graphically challenged, this site is a wealth of excellent templates and ideas. Needless to say, my second attempt at this, which we're continuing to iterate upon, is much, much better.

Thursday, October 30, 2008

From Carl's Mother

Carl's sister Cyndi asked me to share this message from Carl's mother, Susan:


....a very sincere heartfelt thank you everyone for all the caring words coming our way from Carl's internet 'family'.. please know that your words are read and are a comfort to us at this terrible time... his sisters will be writing more later.

Love to all,

Carl's Mom .. Susan

Wednesday, October 29, 2008

Carl Backstrom = WYSIWYG


This posting isn't about Oracle Application Express. It's merely some simple thoughts about our good friend, Carl Backstrom.

I paid attention to the pages of postings on the APEX OTN discussion forum about Carl. Some are from people whom Carl helped once or twice. Others, Carl tirelessly helped many times. Very few of these people actually met Carl, yet they were affected by his death - he touched them in some positive way. I received e-mail from others who only met Carl once or twice, and even they admitted they were affected by this, and couldn't quite pinpoint why. Well...I'll tell you why.

Carl was the definition of WYSIWYG. There was no air about Carl. He was not pretentious in any way. He did not have an agenda. He was not artificial. He was very human and authentic. His omnipresent positive attitude and enthusiasm were sincere and infectious. He was someone you enjoyed being around, someone you wanted to be around. He possessed an intangible, endearing quality.

I enjoyed that Carl was ever so unassuming. If you met Carl for the first time, you might have thought that he's some nice casual fellow who also likes to be a skater in his spare time (turns out, he was a snowboarder). Little would you know that what underlaid his visage was one of the brightest, hardest-working, most creative and visionary minds on the planet when it comes to AJAX, JavaScript, Web 2.0, Web design, RIA - whatever you want to call it. I am not embellishing this fact.

Carl's greatest character flaw, as I see it, was that he could rarely if ever say "no" to someone who was seeking his help. He was always ready to assist someone, no matter who they were, no matter the size of the problem, big or small. What a wonderful "flaw" to have. It is something I can only hope to aspire to.

I shall miss him dearly.

Tuesday, October 28, 2008

Carl Backstrom

As has already been mentioned on the APEX OTN forum and other places on the Internet, our good friend and colleague, Carl Backstrom, passed away on Sunday, 26-OCT-2008.

I spoke with Carl's family last evening. I should have more details later this week, but his family is tentatively planning on a memorial service sometime next week in Riverside, CA. Additionally, his sister said that they're thinking of setting up a trust fund for Carl's daughter, in lieu of flowers or cards. I'll post these details as I get them.

Carl was simply a great guy who touched so many people in such a positive way.

Friday, October 24, 2008

Book for Oracle Application Express and Oracle Forms developers

As most folks already know, the next release of Application Express is 3.2. The primary (and almost sole) new feature in Application Express 3.2 will be Oracle Forms Conversion. This was discussed and demonstrated at Oracle Open World 2008 in San Francisco in September.

When I was at Oracle Open World 2008, I had the good fortune of meeting James Lumsden from Packt Publishing. As it turns out, Packt Publishing is starting a book on Oracle Application Express for Oracle Forms Developers. "It will be a title that shows Forms developers how to ‘get things done’ in Apex, in a practical hands-on way, but with regular cross-reference to their established development techniques, practices and approaches."

Why am I writing about this? Because Packt Publishing is interested in talking to you if you have a desire to contribute to this type of book. If you're interested in further exploring this opportunity, please contact James Lumsden at jamesl@packtpub.com.


* Note: I have no relationship with Packt Publishing. I have contributed to books from Wrox Press and Apress but never Packt, so I cannot give any positive or negative feedback.

Thursday, August 28, 2008

Application Express 3.1.2 Released

This afternoon we released Application Express 3.1.2. As for every patch set for Application Express, we released this in the form of a patch set on MetaLink (Patch Number 7313609) as well as a full release which can be downloaded from OTN. Thus:

  1. If you have Application Express 3.1 or 3.1.1 installed, you'll want to download the APEX 3.1.2 patch set and apply it.
  2. If you have Application Express 3.0 or earlier installed (all the way back to HTML DB 1.5), you'll want to download and install the entire APEX 3.1.2 release from OTN.
  3. If you don't have Application Express installed, you'll want to download and install the entire APEX 3.1.2 release.
Application Express 3.1.2 is inclusive of the modifications made for Application Express 3.1.1 as well as some new bug fixes. It also includes corrections for a couple of regressions (unfortunately) introduced in the APEX 3.1.1 patch set as well as the issues introduced in APEX 3.1.1 with the labels and 2D Flash charts. Lastly, we took Billy V's comments to heart and revised the patch set installation instructions (although I'm sure we'll get further opinions).

The Application Express 3.1.2 patch set was applied to http://apex.oracle.com on Wednesday, 27-AUG-2008.

Friday, August 08, 2008

Oracle Application Express and Oracle MetaLink

When customers have questions about the scalability of Oracle Application Express or Oracle’s commitment to Application Express, the use of Application Express in Oracle MetaLink is often cited by other customers.

Recently, an e-mail from the Oracle MetaLink team went out to MetaLink users, inviting them to try out the new Oracle MetaLink and get their feedback. The new MetaLink is not written with Oracle Application Express, prompting some customers to write an e-mail to me and ask me what’s going on. There was also a recent discussion on one of the ODTUG mailing lists about this very topic. Here are some statements which will undoubtedly be inferred from this change:

  1. Oracle is no longer committed to Oracle Application Express
  2. Oracle Application Express couldn’t handled the scalability needs of Oracle MetaLink

Let me say that both of these statements are false.

Oracle acquired many companies and products over the past few years. Included in these acquisitions was software to help manage the customer relationship. This really became a business decision of either continuing to maintain and extend custom-written software in Oracle Application Express and PL/SQL, or use the off-the-shelf software that Oracle sells. As Tom Kyte often references the “Buy versus Build” decision, this one was even simpler – “Buy versus Own”.

That’s the decision in a nutshell. Anything else inferred from this change would be factually incorrect.

Tuesday, July 29, 2008

Using Oracle Application Express and the Oracle eBusiness Suite?

Are you using Oracle Application Express in conjunction with the Oracle eBusiness Suite? If so, then we'd like to hear from you!

David Peake recently blogged about this, and is collecting information from the Oracle Application Express community. 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.

If you are currently using Oracle Application Express with the Oracle eBusiness Suite (or other Oracle Applications, for that matter), I encourage you to read his blog and take his one page survey. You can provide as much or as little information as you wish, and you have my personal assurances - no sales or marketing representative will call.

Thursday, July 17, 2008

Export data from Oracle Application Express and import via Oracle SQL*Loader

The other day, Josie from Oracle asked me:

"When I export the data, both as csv and xml the date is exported like this: 2005-08-29T00:00:00. sqlldr has a fit about that!"

What she was saying, in rather abbreviated form, was that she was having difficulty using the Oracle utility SQL*Loader to import a data file which was exported from Application Express -> Utilities -> Data Load/Unload. In particular, Josie was having difficulty loading the data of datatype DATE.

If you Unload to Text the EMP table, you'll get something that looks like:

"7839","KING","PRESIDENT","","1981-11-17T00:00:00","5000","","10"
"7698","BLAKE","MANAGER","7839","1981-05-01T00:00:00","2850","","30"
"7782","CLARK","MANAGER","7839","1981-06-09T00:00:00","2450","","10"
"7566","JONES","MANAGER","7839","1981-04-02T00:00:00","2975","","20"
"7788","SCOTT","ANALYST","7566","1982-12-09T00:00:00","3000","","20"
"7902","FORD","ANALYST","7566","1981-12-03T00:00:00","3000","","20"
"7369","SMITH","CLERK","7902","1980-12-17T00:00:00","800","","20"
"7499","ALLEN","SALESMAN","7698","1981-02-20T00:00:00","1600","300","30"
"7521","WARD","SALESMAN","7698","1981-02-22T00:00:00","1250","500","30"
"7654","MARTIN","SALESMAN","7698","1981-09-28T00:00:00","1250","1400","30"
"7844","TURNER","SALESMAN","7698","1981-09-08T00:00:00","1500","0","30"
"7876","ADAMS","CLERK","7788","1983-01-12T00:00:00","1100","","20"
"7900","JAMES","CLERK","7698","1981-12-03T00:00:00","950","","30"
"7934","MILLER","CLERK","7782","1982-01-23T00:00:00","1300","","10"



What should stand out to you is the value for the EMP.HIREDATE column. Why is it formatted this way?

To explain it simply, users of Application Express span all possible countries, territories and languages. A date format that works in one language may not work in another. A good example is a date value that contains an actual month name or an abbreviation of a month name. Today's date in English in Oracle date format DD-FMMonth-RRRR would be 17-July-2008. But change your language to German and you'll get 17-Juli-2008. If your data contains '17-July-2008' and you try to import it into a system with German language settings, it will fail - 'July' is not a valid month name in German.

For the export of DATE type data from Application Express, we needed to use something that works across all languages. We could have devised our own canonical date format. But instead, we decided to employ the international representation of date and time, namely, ISO 8601. So for those who scratch their head and wonder where that odd "T" comes from in the date value, here is your answer.

With this understanding in place of why this value is in this odd-looking format, let's get back to Josie's original question - how do I import this using SQL*Loader? Using a SQL *Loader SQL Operators and escape characters, it's quite easy. Here is a SQL*Loader control file which can be used to load the EMP data from above:

load data
infile "/tmp/emp.txt"
append into table emp
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(EMPNO, ENAME, JOB, MGR, HIREDATE "to_date(:HIREDATE,'rrrr-MM-dd\"T\"HH24:mi:ss')", SAL, COMM, DEPTNO)



The critical element in the field list, of course, is:

HIREDATE "to_date(:HIREDATE,'rrrr-MM-dd\"T\"HH24:mi:ss')"

which is simply applying a TO_DATE SQL operator to the HIREDATE field of the data file. Additionally, the data value will be represented as a string in a specific format. The double-quotes before and after the 'T' must be escaped, so SQL*Loader doesn't try to interpret that as the end of the expression.


Happy loading.

Monday, July 14, 2008

Oracle eBusiness Suite and mod_plsql

There have been a fair number of questions and concerns about the use of mod_plsql and the Oracle eBusiness Suite. And unfortunately, this has created some confusion about what is and is not supported by Oracle. I know a fair number of customers, both large and small, who are successfully using Oracle Application Express and the Oracle eBusiness Suite just fine.

Steven Chan, who is a Senior Director in the Oracle Applications group, has recently blogged about the availability of a new whitepaper on MetaLink. The whitepaper entitled mod_plsql and Oracle E-Business Suite Release 12 (MetaLink Note 726711.1) is intended to discuss many of the issues around mod_plsql and the Oracle eBusiness Suite.

Thursday, May 22, 2008

Application Express 3.1.1 released

Today (22-MAY-2008), the Oracle Application Express 3.1.1 patch set was released. This patch set can be applied to an instance running Oracle Application Express 3.1.0.00.32.

Soon (hopefully later today), the full distribution of Application Express 3.1.1 will be available for download on OTN. This distribution will not enable you to patch an existing Application Express 3.1 instance to Application Express 3.1.1. You will need the patch set from Oracle MetaLink for this.

This patch set does not introduce any new features. This patch set fixes a collection of software defects (i.e., bugs) found internally as well as issues reported on the active Application Express OTN discussion forum. All resolved issues are documented in the README.html file contained with the Application Express 3.1.1 patch set.

To find this on MetaLink:

  1. Login to MetaLink
  2. Click the "Patches and Updates" tab
  3. Click "Simple Search"
  4. For Patch Number/Name, enter: 7032837
  5. Click Go

Friday, May 16, 2008

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.

Wednesday, April 16, 2008

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

//---------------------------------------------------------------------------
// File name: ZipLobs_java.sql
// Description: Java Stored Procedure that zips the data of BLOBS returned
// by the query and writes into a destintation BLOB.
//---------------------------------------------------------------------------


import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import oracle.jdbc.OracleDriver;

public class ZipLobs {
public static int writeZip(
String query,
Blob[] outLob) {
try{
//ceate the zipoutputstream from the end of the outLob
Blob zipLob = outLob[0];
OutputStream os = zipLob.setBinaryStream(1);
ZipOutputStream zos = new ZipOutputStream(os);

//execute the query.
int chunksize = 32768;
Connection conn = new OracleDriver().defaultConnection();
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(query);
while (rset.next()) {
// get the id and the data columns
String filename = rset.getString( 1 );
Blob src = rset.getBlob( 2);


//add one zip entry
ZipEntry entry = new ZipEntry(filename);
zos.putNextEntry(entry);

//write data to the zip lob
long len = src.length();
long offset = 1;
byte[] buffer;
while (offset < len) {
buffer = src.getBytes(offset, chunksize);
if (buffer == null)
break;

zos.write(buffer, 0, buffer.length);
offset += buffer.length;
}
zos.closeEntry();
}
zos.close();

outLob[0] = zipLob;

}catch (Exception e) {
e.printStackTrace(System.out);
return 1;
}
return 0;
}
}
/


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.

Wednesday, March 19, 2008

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.

Tuesday, March 18, 2008

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

Wednesday, March 12, 2008

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.

Friday, February 29, 2008

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.

Thursday, February 28, 2008

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

Saturday, February 16, 2008

Should Oracle Application Express be translated into other languages?

Today, Oracle Application Express is delivered in a total of 10 languages:

  • de - German
  • es - Spanish
  • fr - French
  • it - Italian
  • ja - Japanese
  • ko - Korean
  • pt-br - Brazilian Portuguese
  • en-us - English United States
  • zh-cn - Simplified Chinese
  • zh-tw - Traditional Chinese

As you may already know, a developer-created Application Express application can be translated into any language. But for the development environment of Application Express (Application Builder, SQL Workshop, Access Migration, Utilities, Internal Administration), should this be translated into additional languages?

I am specifically interested in the market opportunity for Application Express, and not necessarily your personal preference.

If you wish to let your voice be heard, please feel free to take this one-question survey at:

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

Tuesday, February 12, 2008

Who uses apex.oracle.com?

For those who don't know, Oracle hosts a demonstration version of Oracle Application Express at http://apex.oracle.com. Anyone on the planet can sign up for a workspace, and as long as you don't run a production or commercial Web site, don't do anything illegal and also understand that there is no Service Level Agreement, it's all yours to try out Oracle Application Express. The ultimate goal, though, is for anyone to try Application Express with without having to download, install, or configure anything. All you need is a modern Web browser. And trust me - no one from sales or marketing will call.

In late 2007, I became inspired and installed some Google Analytics JavaScript code in the Login Message and System Message areas of the Application Express instance (you access this from internal administration, Manage Service -> Messages). This tracks access to the Login page and almost all of the pages in Application Builder, SQL Workshop and Utilities. It does not track any information for user-created applications - only the development environment and the general APEX login page. And it's only used for general information and also to satisfy my curiosity.

If you've ever used Google Analytics before, you're already aware of the wealth of information that is recorded. So who uses apex.oracle.com? Looking at the number of visits broken down by country from December 1, 2007 through February 12, 2008:
  • United States - 30%
  • United Kingdom - 10.5%
  • India - 10%
  • Germany - 6%
  • Netherlands - 3%
What I did find most surprising was that Japan was listed as #12, even though I know there has been reasonably good interest in Application Express in Japan, China and Korea since the days of HTML DB 1.5.

The top 25 are here:



I also found interesting the keywords people used on Google to navigate to apex.oracle.com. Four of the top ten are related to Denes Kubicek. Congratulations, Denis!



Please don't confuse these statistics with overall scalability of this instance or Application Express. I'm only presenting the Google Analytics visits. For the past week, on apex.oracle.com, there were:
  • 1,637,781 page views
  • 2,093 distinct applications used in the past week
  • 2,947 distinct users authenticated in the past week
  • 224 new workspaces approved
  • 13,745 total workspaces
It's running on a Dell PowerEdge 1950 with 2 Dual Core Xeon processors. Average load on the database server hovers around 16%.



Update: In response to Denes' request for the top used applications, I've found the following in descending order of activity in the past week:


PROMED: ProMED-mail Public Site
WORK: OTN EXAMPLES
DKUBICEK: My Demo Application
RTI: OCU_BCK
ASKSTEVEN: AskSteven
EBA: Checklist Manager 1.0
EBA: Online Store 1.0
DBTOOLS: SQL Developer Exchange
OSC: OnScreen
HENRY: Avance Issue Tracker 1.0
ACES: Oracle ACEs
MIKA: MyFirehouse
PALGW: Applicant Tracking System
EBA: Asset Manager 1.0
EMAIL_HELPDESK: Email Helpdesk
JOEL2: Workspace Purge



Granted, I did have to filter some stuff out, because Application Builder and SQL Workshop are always in the top 10. Also, there are some applications for SQL Developer and JDeveloper check-for-updates which are not really relevant to Denes' question.

I think Denes is really trying to rub it in. Note how the Workspace Purge application has generated much activity as people scramble to preserve their seldom-used workspaces.

Oracle Unbreakable Linux and Oracle VM Server Virtualization - Briefing in Columbus, Ohio

For those who care (and are in Central Ohio), next week Wednesday, February 20, 2008, my good friend Sergio Leunissen will be in town presenting Oracle Unbreakable Linux and Oracle VM Server Virtualization. All of the details are here:

http://www.oracle.com/dm/08q3field/12389_ev_ent_arc_brie_feb20.html

Monday, January 21, 2008

Less is More

Like most people, over the past 20 years I have amassed quite a collection of books that I find difficult to part with but will rarely open, if ever. I guess I have been saving them for that one special day when I'll thank my lucky stars that I held onto these books forever and shipped them all over the U.S., going wherever I have lived.

Well, those days are over. My personal goal is a "less is more" lifestyle (or as my brother says, "the more you own, the more owns you"). All of these books will be donated and hopefully someone can either read them or the recipient of the donation can sell them for some money. I've discovered books that I borrowed long ago, I've discovered books that I obviously must have "borrowed" from my high school (which I'll have to return), and I came across a host of computer books from my days at school and early in my career. Although many of these books taught fundamental concepts that apply to today, it's almost laughable how out of touch some of them are with today's technology:
  • Assembler Language Programming for the IBM System/370 Family by George Strubel
  • File Management Techniques by Billy G. Claybrook
  • Pascal Plus Data Structures by Nell Dales and Susan Lilly
  • Data Structures Using Pascal by Aaron M. Tenenbaum and Moshe J. Augenstein
  • Programming in Ada by J.G.P. Barnes
  • LISP by Patrick Henry Winston and Berthold Klaus Paul Horn
Oh yeah, I got plenty of use out of that LISP book. But don't laugh at that Ada book. Ada heavily influenced the design of Oracle PL/SQL.

Oracle HTTP Server, Apache 2.0 and connection pooling

Three years ago, at the Deutsche Oracle-Anwendergruppe in Mannheim, a customer from a large German manufacturer asked me about the availability of Oracle HTTP Server based upon Apache 2.0. They had already standardized on WebSphere based upon Apache 2.0, and they did not want to support both Apache 2.0 and Apache 1.3 configurations. Certainly, a reasonable request.

Within a year, Oracle released a version of the Oracle internet Application Server based upon Apache 2.0. Most Apache modules delivered with iAS were usable in an Apache 2.0 environment. But the most glaring omission was mod_plsql, which was not ported to Apache 2.0 and still required Apache 1.3.9. To this day, I'm still baffled by the decision to not port mod_plsql at that time.

Fast forward to Fall 2007, and with the Oracle Database Release 11g distribution is something called Oracle HTTP Server (Apache 2.0) (10.1.3.3.0) for Linux x86. This is the version of Oracle HTTP Server that is based upon Apache 2.0 and has a supported version of mod_plsql. At last!

One of the benefits of Apache 2.0 is that it supports various Multi-Processing Modules. You can choose to either use the legacy process-based (prefork) architecture or the multi-threaded (worker) architecture. One of the benefits of using the multi-threaded implementation with mod_plsql is that it supports a true database connection pool. In the previous implementations of mod_plsql on Oracle HTTP Server on Linux, there is one database connection per HTTP Server process. There really isn't any "pool". But in the multi-threaded implementation, there is a database connection pool that is shared among all threads of a child process by mod_plsql.

The size of the database connection pool cannot be adjusted within mod_plsql. If N concurrent requests require database connections, then N connections will be created. If a connection is idle for more than X minutes (governed by PlsqlIdleSessionCleanupInterval), it is cleaned up. Indirectly, the Apache configuration for maximum processes and maximum threads defines the worst case peak value for possible database connections.

So I downloaded this thing called "Oracle HTTP Server (Apache 2.0) (10.1.3.3.0) for Linux x86", installed it, configured it, and thought I was good to go. But I examined the number of processes running on Linux, and I also examined the number of database connections, and I didn't notice anything different. I believe the default was set to the multi-process module, simply for backward compatibility reasons.

MetaLink Note 299125.1 gives instructions how to configure the multithreaded MPM in Oracle HTTP Server/Apache 2.0 (without recompiling). The relevant portion of this note:

SWITCHING FROM PREFORK TO WORKER
--------------------------------

- Edit opmn.xml and find the section which defines the HTTP Server:

<ias-component id="HTTP_Server">
<process-type id="HTTP_Server" module-id="OHS2">
<module-data>
<category id="start-parameters">
<data id="start-mode" value="ssl-disabled"/>
</category>
</module-data>
<process-set id="HTTP_Server" numprocs="1"/>
</process-type>
</ias-component>

- Add start parameter "mpm":

<ias-component id="HTTP_Server">
<process-type id="HTTP_Server" module-id="OHS2">
<module-data>
<category id="start-parameters">
<data id="start-mode" value="ssl-disabled"/>
<data id="mpm" value="worker"/>
</category>
</module-data>
<process-set id="HTTP_Server" numprocs="1"/>
</process-type>
</ias-component>

and save this change to the opmn.xml file

- Execute "opmnctl reload"

- Stop Oracle HTTP Server and start it up again (just "restart" will not suffice)
To verify that Oracle HTTP Server is really using the "worker" or multi-threaded MPM, examine the output of the process listing "ps -ef" on the server operating system. If you see one or more processes named "httpd", then you are still running the "prefork" or mulit-process MPM. If you see one or more processes named "httpd.worker", then you are indeed running the multi-threaded MPM of Apache 2.0.


* Oracle HTTP Server on Windows has always been multi-threaded.

Stand back, OraNA. Here I come.

Sunday, January 20, 2008

It's too cold

I couldn't help but notice this one on My Yahoo this morning:





For those of you who hail from outside the U.S., this would be:


I'm tired of the cold. I'm tired of living in the cold. Although I've spent the majority of my adult life in Dublin and Powell, Ohio, I've also lived in Naperville, Illinois and Moorestown, New Jersey. Guess what? They're all very cold in the winter.

I've always justified it by saying that I am able to relish the full four seasons in a year and also have a white Christmas. Better yet, given the many things there are to like about Central Ohio (well educated and congenial Midwest people, very reasonable cost of living, safe, good schools, not crowded), I've often said that it's much better for me to travel on business/pleasure to these warmer destinations, just for a vist. Well, the older I get, the more I realize that my time is limited and I have this all backwards. It would be much better to pay the money, live in a smaller place, and just exist in an environment where you can get outside and truly live all the time.

Saturday, January 19, 2008

ODTUG Kaleidoscope 2008

As luck would have it, my abstract "Go Global with Application Express!" has been accepted for presentation at ODTUG Kaleidoscope 2008. I'm quite happy about this because:

1) I'm covering a topic that I believe is relevant to many people, whether they realize it or not. If it isn't today, it will be eventually. Europeans typically have to design and support multi-lingual and multi-locale applications (those poor Swiss get hit the hardest), it's common throughout Asia, especially in China with the support of Simplified and Traditional Chinese, and the requirement to develop these types of applications will only continue to grow in America.

2) There should be a plethora of sessions on Application Express, once again. Some of the ones that the committee just "couldn't bear to turn down" include Patrick Wolf, John Scott and Dimitri Gielis. The ever-growing APEX community will be there.

3) I love crawfish.

See you in New Orleans in June.

Monday, January 14, 2008

Tag backlash

It's interesting to note the backlash against this game of blog-tag that originated here. Some have said that this has polluted the Oracle news aggregators, and you know what? They're right.

Granted, when Carl told me that I was tagged (and after he explained it to me), I thought it was rather sophomoric, especially given the looming APEX 3.1 milestones and all the other commitments on my time. But I have no remorse for posting my 8 boring things, simply because I think there has to be 0% chance my minuscule and meaningless blog has made it into an Oracle news aggregator. So I have not contributed to the pollution.

Thursday, January 10, 2008

What you never knew (and probably never want to know)

Okay...first of all, I'm not an active blogger (obviously). How I became embroiled in this BlogTag business is still beyond me. It's ultimately due to Flavio. Carl Backstrom didn't even know I had a blog until our buddy Flavio called me out. I'm sure Tom Kyte would find this "game" completely annoying and would refuse it in a second. I'm sure Carsten Czarski would sure enjoy this, as well as my good friend Sergio Leunissen.


So here are the most inconsequential 8 things I could conjure.
  1. I'm the seventh child of the seventh child. Don't worry, there's nothing magical or spiritual about this. My mother was the seventh child, so you only need to worry about the seventh son of the seventh son, so says Iron Maiden.
  2. I'm second-generation American. My grandparents were immigrants from Poland and Germany and came to America with literally nothing. My brothers and sisters and I bought a plaque at Ellis Island in their remembrance.
  3. I hardly ever read novels of fiction, maybe one book a year at best. My wife, in-laws and father are all avid novel readers. It's bizarre, but I just don't find it compelling. Don't think I don't read, because I do all the time. But my reading list is usually www.opinionjournal.com (every day), Barron's, Money Magazine, Kiplinger's, and occasionally the computer trade magazines.
  4. I wouldn't be at Oracle if it weren't for Tom Kyte. I worked with Tom and a bunch of other excellent individuals (Chris Beck, Tim Taylor, David Bliss, Don Bostrom, George DeGraaf, among others) back in 1990-1991. They worked for a subcontractor. I worked for a subcontractor to the subcontractor. Those were the days when we were changing the world, and when very few people ever heard of SGML.
  5. My most memorable job was my one at McDonald's in high school and during my summer breaks from college. You might laugh at this being a favorite job, but I learned a great deal. It is from the general manager there (Lou Stallman, wherever he may be) who taught me respect and courtesy for customers and co-workers alike, as well as the quality to take pride in all you do and to always do it well. And there was never any standing around or goofing off there, as they often said "if you have time to lean, you have time to clean."
  6. I spent the summer of 1985 in Quantico, Virginia at the U.S. Marine Corps Officer's Candidate School. My goal was to become a Marine Corps aviator. I got in better shape during that summer than anyone normally could in an entire year. And there was no fantastic array of exercise equipment, it was always go-go-go. This was another one of those value-forming experiences, where I truly learned what discipline was all about. As luck would have it, that's the summer that my uncorrected vision went South, my fantasy of making it into Aviation School vanished, and I did not return to OCS the next summer (well, this was really one of a number of reasons).
  7. I started off at Ohio State in Electrical Engineering. I went through class after class of mathematics, did fairly well at it, and when I was done and ready to start the electrical engineering coursework, I thought "whew, finally done with all that math". Guess what? Electrical engineering is nothing but math. I remember calling my father and explaining to him that I wished to switch to computer engineering and get out of electrical engineering. My Dad (who always wanted an engineer in the family) calmly explained to me that I was making a mistake, and that there wouldn't be any money in that. Funny, huh?
  8. I am very competitive, probably too much so. I love to win, I hate to lose. I love to gamble at many things. I enjoy betting, from football games, basketball games, Super Bowl pools, March Madness pools to craps and blackjack.
This is exactly why I don't blog. I started this two days ago and off and on I finally finish it. Maybe one of these days I'll actually write something about Application Express.