Monday, August 17, 2009

Subversion Autodeploy post-commit Hook

Since the apache user is non-interactive, it's difficult to get a post-commit hook to trigger an svn update elsewhere on the system. You can either temporarily make the apache user interactive, or perform the checkout in the hook one time. For the latter option, empty the deployment directory and update the post-commit hook with this command...
svn checkout file:///path/to/svn/project/ /path/to/project/dev/deployment --non-interactive
Now make a trivial change and perform a commit. Check that the project has been deployed. Change the post-commit hook to this...
svn update /path/to/project/dev/deployment --non-interactive

Sunday, July 26, 2009

It's All Text!

Every APEX developer needs to know about a handy firefox extension called It's All Text!. Install this and it will allow you to seamlessly edit any textarea in an external editor. The default file extension applied to such files can changed to ".sql". So if your editor handles syntax highlighting based on file type, you're in business. I use SciTE and it all works very well.

It's almost like having a real IDE. In fact, with some tweaking this extension could do a whole lot more for APEX developers. I'm thinking meaningful file naming/directory structure based on the structure of the application and SVN integration at least. APEX page save error messages sent back to the text editor for finding line numbers (SciTE can handle this very easily, if the APEX page save error message was dumped into a file, then SciTE can be configured to pick that text up when a hotkey is pressed and errors with line numbers will be linked to the source code). This might have to be a little side project.

To change the default file type right click the "edit" button, select "Edit with new extension...", type ".sql" and check "Save for future use".

I don't have to explain how simple and effective this little tool is. Just install it and wonder how you ever lived without it!

On a side note, there is a similar extension good for editing HTML regions called Write Area.

Friday, June 19, 2009

Haunted by Headers

Had an interesting problem today tracing the origin of some HTTP headers. An application which uses mod_plsql (non-APEX) was constantly returning some troublesome header information which we needed to remove, particularly when a certain procedure was called. I searched every where in the OAS interface, but none of the settings seemed to be applicable. Eventually I found the culprit was an apache declaration in dads.conf. Why it was there I can only speculate, not having built the application.

On the up side, I can now clarify some more of the behaviour of the undocumented htp.init procedure. During the process of tracing the problem, I attempted to use htp.init to suppress the headers from showing up when calling a particular procedure. Apparently this isn't how it works. The application server still amends header information to the response before it is sent.

Monday, June 15, 2009

Building a Scraper Using UTL_HTTP

Someone set me the task of embedding pages from another one of our online applications into regions of an APEX app for the purpose of maintaining a single navigation and look and feel. My first thought was that it can't be done. As far as I was aware you couldn't perform a GET request from PL/SQL (I'd breifly checked in the past and that was the conclusion I'd arrived at). So instead I quickly threw something together in PHP that scraped the application and then used str_replace to embed additional style/navigation and achieve a pretty good compromise. It was quick and dirty, and getting it to work on the PHP4 version that comes with APEX was a bit of a challenge. It wasn't really an ideal solution, so I kept digging and found the UTL_HTTP package.

It's not quite as simple as calling a function that returns the page content, but it's possible. I followed the documentation as well as an O'Reilly example. The simple package I built doesn't handle proxies or authentication, but it does do a better job at handling large pages as CLOB data. I've pulled out the exception handling to make it simpler to understand. Refer to O'Reilly's example when you are ready for that.

CREATE OR REPLACE PACKAGE WEB_SCRAPER AS
FUNCTION HTTP_GET_PAGE(p_url in VARCHAR2) RETURN CLOB;

FUNCTION DOCUMENT_BODY(p_document in CLOB) RETURN CLOB;

FUNCTION HTTP_GET_PAGE_BODY(p_url in VARCHAR2) RETURN CLOB;

PROCEDURE PRINT_CLOB(p_content in CLOB);
END WEB_SCRAPER;
/

CREATE OR REPLACE PACKAGE BODY WEB_SCRAPER AS
FUNCTION HTTP_GET_PAGE(p_url in VARCHAR2) RETURN CLOB AS
/* Performs a simple HTTP GET request and returns the content */
l_request Utl_Http.req;
l_response Utl_Http.resp;
l_content_buffer VARCHAR2 (32767);
l_page CLOB;
BEGIN
DBMS_LOB.createtemporary(l_page, FALSE);

l_request := Utl_Http.begin_request (url => p_url, method => 'GET');

Utl_Http.set_header (r => l_request, NAME => 'User-Agent', VALUE => 'APEX-Scraper/0.1');
l_response := Utl_Http.get_response (r => l_request);

BEGIN
LOOP
UTL_HTTP.read_text(l_response, l_content_buffer, 32767);
DBMS_LOB.writeappend (l_page, LENGTH(l_content_buffer), l_content_buffer);
END LOOP;
EXCEPTION WHEN Utl_Http.end_of_body THEN
NULL;
END;

Utl_Http.end_response (r => l_response);
RETURN l_page;
END HTTP_GET_PAGE;

FUNCTION DOCUMENT_BODY(p_document in CLOB) RETURN CLOB AS
/* Return everything between the body tags */
BEGIN
RETURN REGEXP_REPLACE(p_document,'^.*<[[:space:]]*BODY[^>]*>(.*)<[[:space:]]*/[[:space:]]*BODY[[:space:]]*>.*$','\1',1,0,'imn');
END DOCUMENT_BODY;

FUNCTION HTTP_GET_PAGE_BODY(p_url in VARCHAR2) RETURN CLOB AS
/* Convienience function combining DOCUMENT_BODY and HTTP_GET_PAGE */
BEGIN
RETURN DOCUMENT_BODY(HTTP_GET_PAGE(p_url));
END HTTP_GET_PAGE_BODY;

PROCEDURE PRINT_CLOB(p_content in CLOB) AS
/* Use this instead of htp.p which only supports Varchar2 */
l_buffer_size Number := 8191; --Can't always use 32767. See bug #5896994
l_clob CLOB := p_content;
l_chunks Number;
l_cloblen Number;
BEGIN
l_cloblen := dbms_lob.getlength(l_clob);
l_chunks := floor(l_cloblen / l_buffer_size) + 1;
FOR i IN 0 .. l_chunks LOOP
htp.p(dbms_lob.substr(l_clob, l_buffer_size, (i * l_buffer_size) + 1));
END LOOP;
END PRINT_CLOB;

END WEB_SCRAPER;
/

Tuesday, June 9, 2009

Eliminating Bugs

Fantastic article...
http://duartes.org/gustavo/blog/post/of-aviation-crashes-and-software-bugs

Gustavo talks about eliminating software bugs by eliminating the possibility to create them.

I think Apex does a pretty good job on this front, as long as you don't start putting "execute immediate" statements in PL/SQL blocks. Not so good at preventing XSS though. Although it does have the "Strip HTML" option set by default in reports, it's over zealous and you often have to switch it off because it can't handle "<" and ">" symbols in legitimate text (really it should just convert them to harmless &lt; and &gt;). Normal display as text items have the "escape special characters" option, but this has the same problem.

In fact, Oracle already has the function required to render user data safely... HTF.escape_sc. It's probably a good idea to wrap every item in the report select statement with this, or create a view that serves this purpose and only use the view when displaying user data. I've toyed with using the function to sanitise user input, but the problem there is each time you save the same form, the escape sequences get longer and longer (ie. & becomes &amp; becomes &amp;amp;).

They should smarten that function up and include a default input sanitisation process for every form, then permit you to downgrade it's paranoia level for exceptional situations. Allowing for exceptional circumstances goes against what the article is saying. But the author doesn't discuss the downsides and tradeoffs of a Nazi development environment. I don't think it's possible to have an environment that completely guards against writing buggy code. It's just another impossible ideal. But there's nothing wrong with making steps towards it.

Thursday, May 7, 2009

Schema Refactoring

Ever wanted to change a column or table name? I'm pretty sure there aint no way to globally update changes like this throughout your application via the APEX interface. So here's a little tip on how you can get away with it and the gotchas to look out for.

Simply export the application, make a copy of the exported SQL file and then open it in your favourite text editor. Perform a search and replace for all instances of the required change. I recommend reviewing each replacement one by one. It may seem like a tedious option, but imagine trying to search the application manually for all these references via the web interface.

Keep track of the pages, particularly reports, that you are altering. You'll want to test them all after the import. You can usually find the page id at the top of the section you're messing with, or else an item name prefix in the vicinity will give it away.

Import the application, then check each of the pages you've altered for workingness. Often I've noticed that report regions may throw a "no data found" exception for no obvious reason (the report itself will not render at all). The resolution to this is to:
  • duplicate the region by using the region copy tool
  • move any region items to the copy
  • confirm the copy works as expected
  • delete the original
This is a handy workaround to know about for when regions start behaving oddly in general. This problem is not only triggered by editing an export file. It's some kind of bug that tends to crop up when you make major changes to a report region's query. I've seen plenty of people complaining about it on the forum, but no official explanation of how or why it occurs. Perhaps some cache like mechanism is at play somewhere.

You could also try deleting the application before importing it, I've not tried it. But if you intend to eventually migrate to production and you want to minimise your outage, I wouldn't recommend that as an option. I've had weird bugs appear after deploying to production that didn't exist in the exact same application in dev or test. Better to annihilate the risk of delaying the inevitable by getting your self a new region ID.

Perhaps I could put together a groovy tool which intelligently identifies all database objects in an export file, allows you to change them and also manages the assignment of new region IDs for altered parts of the application. That would rock!

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.