Monday, January 19, 2009

Clickable Active Tabs

All the default templates in Application Express seem to have disabled active tabs (ie. you can't select the tab that is already selected). To enable these tabs, edit the page template and copy the anchor tag around #TAB_LABEL# from the "Non Current Standard Tab" section and paste it over the #TAB_LABEL# place holder in the "Current Tab" section.

Wednesday, January 7, 2009

Redirection Process Follow Up

It seems there are a few undocumented features you can add to the mix to get the redirect to work more efficiently. I'm lead to believe that htp.init clears any headers that may have been set previously and setting apex_application.g_unrecoverable_error to true will tell APEX to stop processing the page after the redirect process is complete. Though both are not officially documented, there is a comment in the package next to apex_application.g_unrecoverable_error that reads:
indicates error has occured which requires error page

So here's what I'm using now:

htp.init;
owa_util.redirect_url('f?p='||:APP_ID||':XYZ:'||:APP_SESSION||'::'||:DEBUG||':::');
owa_util.http_header_close;
apex_application.g_unrecoverable_error := true;

According to the doco, this should result in the following raw HTTP response where <curl> is the resultant url:
Location: <curl>\n\n

Here's some references:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/w_util.htm
http://www.psoug.org/reference/htp.html
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/w_htp.htm
http://www.google.com/search?q=apex_application.g_unrecoverable_error

Update: After inspecting the response, seems we don't need to explicitly close headers.
Update: See my post about HTTP Headers for more details about htp.init behaviour.

Avoiding Varchar(4000)

I usually never use varchar(4000) unless there is a damn good reason to do so. Most people don't plan to use the 4000 character limit, but it does seem to be the default in APEX. If you are planning to use varchar(4000) you should probably use a clob instead. Why place an arbitrary limit on a column if you don't want one and you can avoid it?

Perhaps the need to query the column is outweighed by the risk that the user will want to enter more than 4000 characters of data, usually you don't need to directly query large blocks of text. If you don't ever need to search the block of text then just use a clob. Trust me, it will save you much grief in future.

Many times in the past I've peer reviewed data models, systematically replacing varchar(4000) with clob columns. But looks like I didn't follow my own advice on my current project. Now I'm smashing my head against the desk. The users of this shiny new system are all academics, so I should have known it was just a matter of time before someone tried to post a 10 page rant. Now I'm trying to convert the columns to clobs and APEX is being less than forgiving.

So far my approach was to create a temporary clob column, move the varchar data into the clob, drop the varchar and rename the clob. APEX then throws "ORA-01008: not all variables bound" exceptions when I visit the form that previously used the varchar version of the column. I've tried deleting the related page items and the exception went away. Creating new clob based page items keeps giving me that exception though. Gah! I'm this close to just rebuilding the form from scratch.

Take my advice, don't use varchar(4000)!

Update: I now know the trick to resolving ORA-01008 is usually to make a copy of the region and delete the original.