Monday, 10 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: http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_app.htm#autoId2

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:

http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/concept_sub.htm#BEIIBAJD

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:

begin

 if :apex$row_Selector = 'X' then

  delete from child_table where some_id = :id;

 end if;

end;

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.

No comments:

Post a Comment