Sunday, 2 December 2007

Problem with 'Not Null' validations

A question popped up on the forum this week about the ability to bypass not null validations. The problem is if you define a 'Not null' validation on a page item, the user can enter a blank space and the validation will allow it. But there is a simple workaround and it requires no changes to any of your existing validations.

Note this will only work in APEX version 3.0 or higher. If you are looking to implement this on 2.2 or 2.2.1, then please refer to the link above to the forum post where this was discussed, where I posted a solution for 2.2 (the views were changed slightly). Unfortunately, pre 2.2 this method is not possible, as I'm referencing the APEX dictionary views that were introduced in 2.2.

To get around this you can use an APEX application process to trim all the items for the current page that have associated not null validations. Create the following application process:

Sequence: 1
Process Point: On Submit: After Page Submission - Before Computations and Validations
Type: PL/SQL Anonymous Block

Process Text:
FOR cur IN
( SELECT items.item_name
FROM apex_application_page_items items,
apex_application_page_val vals
WHERE items.application_id = TO_NUMBER(:APP_ID)
AND items.page_id = TO_NUMBER(:APP_PAGE_ID)
AND items.item_name = vals.associated_item
AND vals.validation_type
= 'Item specified is NOT NULL'
apex_util.set_session_state( cur.item_name
, TRIM(v(cur.item_name)));

I added this to a form on EMP and setup a not null validation on the job item. Then loaded the page, keyed a space for the job field and on inspection of the debug, you can see that before the process fires, session state shows a " " for P12_JOB, which would have passed the not null validation.

And then after the process has fired, it has been trimmed and set in session to "", which causes the not null validation to correctly fail.

Hope it helps,

1 comment:

Kris said...

I like this tip a lot!

Another great addition Anthony.