Sunday, 9 December 2012

Tabular Form Validations And Processes Beyond APEX 4.0

Before APEX 4.1, if you wanted to add more complex validation using PL/SQL, you would have to do a loop through the apex_application.g_f0x array and act accordingly - as per:

Aside from validations, you may want some process to do something with values before deleting/updating/creating.

Apex 4.1 introduces some new substitution strings:

APEX$ROW_NUM - the row number being processed
APEX$ROW_SELECTOR - If a check has been marked in the checkbox (value will be 'X')
APEX$ROW_STATUS - C if created; D if deleted; U if updated

I wasn't really sure how to work with these, and nor did I really investigate, when I first saw them in the builder guide. A recent thread in the forums, and it all makes sense!

With these changes, you no longer need to deal with the array, but instead can just refer to the above substitution strings in combination with bind syntax to access specific columns. The important steps are that when you first create the validation or process, you specify the tabular form it is associated with. According to the help text of execute condition, you must also specify the execute condition as For Each Row, if referencing any tabular form columns in the process - However, in my tests, I didn't experience this, Once or For Each Row didn't make any difference.

Side note: from what I can see, this doesn't work with Manual Tabular forms, so in that case, you will still need to use the g_f0x array.

For example, lets check the id column matches the row number (not a good example, as sorting will mess this up, but just to give you the idea)

Add a new validation, and specify the type as function returning boolean

return :APEX$ROW_NUM = :ID;

(Referencing the column name with bind syntax)

Similarly, if you had some client side function to re-order rows, you could then have a page process:

update tabular_test
set num_col = :APEX$ROW_NUM
where ID = :ID;

One more example might be that you want to delete child records before performing the multi row delete. Another process:


 if :apex$row_Selector = 'X' then

  delete from child_table where some_id = :id;

 end if;


Alas; no need to deal with the apex_application.g_f0x array (for the most part). Just associate a validation/process to a tabular form and make use of these new variables and reference tabular form columns using bind syntax.


  1. Thanks so much for the nice post.In my case, I need to call a function on a given row that would validate the row before committing to the database.

    Let's say I have a table t with columns x,y,z that are in the tabular form.
    I need to validate via a function - my_func(:x,:y) whose input would determine if the row is good to be commited. How can I acomplish this ?

    1. Hi Alice,

      It's pretty straight forward to set that up - just specify the tabular form when you create the validation, and do as described.

      Here's an example:


  2. Thanks so much for the example. It's of tremendous help.

  3. I tried the example. Both yours and the one provided by Patrick Wolf with the table. Needless to say that after going through trials and tribulations and was finally able to compile the function that gets the messages from the table, and provided all the necessary access, my generic error unique constraint JOB_UK is still displayed instead of the message in the table. I setup the message at the application level under application properties. Not sure what I am missing. Do you by any chance have the code sample the way it is setup in the apex application ?

    1. Are you talking about the Error Handling API?