Monday, March 05, 2007

Avoiding ORA-01722 in Apex tabular forms

Have you ever experienced that uncomfortable feeling as it happens when you find out you've spent the last three hours in reinventing the wheel?

Well, luckily enough, it didn't happen to me today ;-)

I had a page in an Oracle Application Express application, based on a tabular form.
Tabular forms are a powerful tool, but when it comes to validating the content of each cell, they can quickly turn into a programmer's nightmare.

For instance, how to prevent that a user enters invalid data and is taken to the unhandled pl/sql exception page where it will be notified of some friendly error like the following?

Error in mru internal routine:
ORA-20001: Error in MRU: row= 1,
ORA-01722: invalid number, update schema.table set ...

This error most likely occurs because the user entered either an invalid decimal point or an invalid thousands separator in a numeric column.

So, being in such a situation, my first thought was to turn all my numeric columns into varchar2 and then handle the problem line by line, checking if the numeric format was acceptable or not.

But there was a sort of voice in the background asking me: "is this mess really needed? aren't there any other viable options?"

I was almost sure that there was something in Apex allowing me to specify a numeric format model even for tabular forms.

And there is indeed.

Open up the tabular form report and click on the edit icon of the column.
The attribute i was looking for is the first in the "Column Formatting" block and is named "Number/Date Format".
I didn't pick the format model from the list, but entered a new one: 999G999G990D99.

So, now, a user is free to enter numbers as plain digits with a decimal separator or formatted numbers including the thousands separators, without worrying about ORA-01722.

A small fix for a developer but a huge leap for user friendliness!


Patrick Wolf said...


if you want to have even more "user friendliness", you may should take a look at my ApexLib Framework. One of the features is browser validation for date-picker and numeric items. Another is inline error display for Tabular Forms.


Byte64 said...

Hi Patrick,
it looks very interesting and promising, i'll need to study your framework very carefully.
I come from the "old school" and to put it straight i find javascript a ghastly mess, but i do admit that in certain situations is the only way forward.

Thank you!

Patrick Wolf said...

I'm "old school" too! That's why I want to hide the javascript mess from our developers :-)

Doing a lot with Javascript lately, I have to confess that it also has it's nice parts. I think in the past the development was a mess, but with Firebug and Firefox it works out quite well.

But still, I want to hide my developer from using Javascript as much as possible, because they tend to "forget" to implement the same business logic checks in the database too... That's why I try to automatically generate all the checks for the browser.


yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles