Oracle Apex Tabular Forms

The Tabular Forms feature of Oracle's Apex web development tool allows users to update multiple rows in a table using only declarative programming.  All of the Javascript and PL/SQL is done behind the scenes, making tabular forms a relatively easy to use and powerful tool.

 After logging into your Apex environment (Oracle offers a free demo site), and navigating to your page, it's simple to create a tabular form:  Create Region -> Form -> Tabular Form . Then,   the tabular form wizard will guide you  through the process of selecting the table and columns, choosing a primary key, choosing columns to update, and adding buttons and branching. 

Run the page, and you find that the tabular form lets you scroll thru every row in the table.  Pretty cool, but maybe you only want a subset of the rows?   So your page has an ID field, and you modify the tabular form with the WHERE clause

select rowid,            /* Apex generates the select */
       table_key, 
       table_col1, 
       table_col2, 
       table_colx  
 where table_key = :id   /* You add the where clause */

Run the page again, and nothing shows up.  No rows, no data, nothing.  If the ID field of your page is  blank, that's the correct response.  Nothing in the table satisfies the where clause, so nothing is displayed.  If you enter an ID value that's not in the table, again, nothing is displayed.  If you enter an ID value that is in the table, only the matching rows are displayed.   Perfect... almost.

There is an Add Row button to add new rows, and using the Add Row button is the way the tabular form is designed to work.  But in some cases, we would like an empty row of cells to display if there isn't any data.  The end-user may find it odd that nothing is displayed on the screen, and the blank row gives them a place to start.

Depending on what version of Apex you're using, you may be able to modify the tabular query by using a UNION to select a row of null values from dual.  But this doesn't work with every release of Apex, so we'll take a different approach.

If we examine the Add Row button, we find that it fires a bit of Javascript:

javascript:apex.widget.tabular.addRow(); 

So, we will execute the JavaScript addRow function when the page loads by using a dynamic action.  Here's how to do it: create a new dynamic action and name it Add_1_Row.  Click Next.  The Event should be Page Load,   don't choose a Condition, and click Next.  The Action should be Execute JavaScript Code,   the code should be javascript:apex.widget.tabular.addRow();  , and click Next.  Then click Create Dynamic Action.

Now, your end-user will always have a blank row to enter data.   And if they need another blank row, the Add Row button is ready for them to use.  Best of all, you don't need to worry about how to process this row of blanks because the Apex multi-row processes auto-magically handle all the inserting and updating for you. Now it's perfect!

For more information about how APEX handles commits in tabular forms, see my post discussing commits in APEX.