Commitment: Commits in APEX

Commitment is such a loaded word these days. Maybe we humans can't quite get it right, but certainly we can count on our computers to get it right. After all, they're binary machines, 0 or 1, false or true: absolute logic. Or can we?

While debugging a PL/SQL block in APEX, I noticed that the code was marking the input data as processed, even if the procedure terminated with a raise_application_error.  I was focused on debugging the error causing the raise_application_error, so I didn't care that the input was marked as processed.  After fixing the problem, then it occurred to me -- wait, a minute -- why was the input marked as processed when the transaction terminated in error?

When we do transaction processing, we expect the transaction to be atomic.  Either all parts of the transaction goes in, or none of the transaction goes in.  Imagine going to the ATM machine, moving $50 from savings to checking, and the move fails in the middle of the financial transaction.   That would leave the savings debited $50, nothing credited to checking, and $50 floating in the bit-ether.  If we do transaction processing correctly, then either the money is successfully moved, or the money is left in savings.Nothing is left half-done.

Back to Apex.  I clearly did not have a correctly-working transaction.    There was nothing in my procedure that would do a commit.  But, somewhere, somehow, something was committing the database changes, even though I didn't expect it to.

There's a lot going on here.  Partly it's semantics and syntax.  My APEX code was not doing anything I hadn't done in COBOL/DB2 with host variables, and the COBOL/DB2 transactions always worked correctly.  Partly, it's an understanding, or misunderstanding of what's happening in Apex.   Let's start with syntax and semantics first.

Imagine we have COBOL/DB2 program and some PL/SQL in an Apex block. Both code snippets will do the same thing. Here's the COBOL:

   EXEC SQL
      INSERT INTO MYTABLE(MY_COLUMN) VALUES(1)
   END-EXEC.

   EXEC SQL
      SELECT DUMMY
      INTO :WS-DUMMY
      FROM SYSIBM.DUMMY1
   END-EXEC;

   EXEC SQL
      ROLLBACK
   END-EXEC.

Now lets look at the PL/SQL code in APEX. Each of these anonymous blocks runs in its own APEX process in the same page after the page is submitted.

begin

   insert into mytable (my_column) values(1);

end;

begin 

   select dummy
   into :p1_dummy
   from dual;

end;

begin

   rollback;

end;

Well, that's pretty simple. Except for wrapping the PL/SQL inside an anonymous block and wrapping the COBOL up for the DB2 pre-processor, these code snippets should do the same thing. But they don't. Run each piece of code several times. The COBOL example will never add a row to MYTABLE, while the ORACLE APEX page adds a row to MYTABLE. Notice I said "adds a row", but more on that later.

This isn't an Oracle/DB2 issue.  Login to an Oracle SQLPLUS session, and try the PL/SQL code snippets in SQLPLUS.  The SQLPLUS session yields the same results as the COBOL test.

Syntax and semantics:  in an ideal world, there's a tight correlation between syntax and semantics.  We would like things that look the same to behave the same. Our variables, :WS-DUMMY and :p1_dummy, look like two ordinary "host variables". An astute reader of Apex documentation will call :p1_dummy a "session variable", and that's our first clue. Session variables may look like host variables (syntax is the same), but session variables do not behave like host variables (semantics differ). Mostly they do behave the same, with an important exception: changing a session variable causes APEX to take a commit. 

 Back to "adds a row":  only one row is added.  If we run our code snippet in Apex a few times, and examine the table, we find there is one row.  Why aren't there a few rows?  After all, didn't we select data into :p1_dummy each time?  That's the second point:  changing a session variable causes APEX to take a commit. If we move the same value into a session variable, if the value does not change, then APEX does not take a commit.   This means that our code will behave differently depending on the data!

For gremlins, this is a good thing.  For developers, this is not so good.  Here are a few tips to avoiding obscure errors caused by buggy transaction processing:

1. During development, turn on DEBUG.  Examine the debug logs, looking for text like Session State: Saved Item "P1_DUMMY" New Value="".  Anytime we see this, APEX has taken a commit.
2. Use local variables declared in the anonymous block rather than session variables. Use session variables to store values that will be displayed on the web page, or to save values that you need between page submits. Otherwise, use local variables.
3. If you pass session variables as IN OUT arguments to an external procedure, changing the parameter values in the procedure is the same as changing the session variables.
4. APEX only performs commits after a process is run. If you find an error, rollback the transaction and set an error before your process exits.  This is true for both anonymous blocks in APEX and external procedures called by APEX.
5. If you need a commit, explicitly code the commit. Do not assume that APEX will take a commit just because the procedure set a session variable.  If the value of the session variable didn't change, APEX won't take a commit.
6. Be aware of how APEX processes tabular forms (more on that below).

Searching the APEX document is rather discouraging. Searching for keywords like COMMIT and TRANSACTION do not turn up any applicable information. Searching the Internet was more fruitful.  Daniel McGhan blogged this topic in August, 2012, and he notes seven situations where APEX takes a commit.

1. On load, after a page finishes rendering 
2. On submit, before branching to another page
3. On submit, if one or more validations fail, before re-rendering the page
4. After a PL/SQL process that contains one or more bind variables has completed
5. After a computation
6. When APEX_UTIL.SET_SESSION_STATE is called
7. When APEX_MAIL.PUSH_QUEUE is called

The Oracle forums are another good source of information.  According to an Oracle employee responding to a question in the Oracle forums:

Commits are issued when your code does so explicitly, when a page show or accept request reaches the end, or when anything within your code causes session state to be saved. Session state -altering actions include PL/SQL assignments to (or "select into") bind variables which are page- or application-item references, calls to session-state updating APIs, and page or application computations.

Our pages often include some simple items(text boxes, select lists, date pickers, etc.) plus a tabular form for repeating elements.  If we're going to process the page as one transaction properly, we need to know if there are any implicit commits in the APEX Page Processing post-submit processes.   If we create a tabular region, we find that Apex adds two post-submit processes:   ApplyMRU and ApplyMRD. 
Ideally, we should be able add our post-submit process and have all of the post-submit process run as one transaction. Either everything is committed, or none of it committed. We do not want half a transaction.

In the absence of good documentation to guide us, let's do some testing. We'll test in APEX 4.2, the results may differ in other versions. We can test for implicit commits by building a one-page applicaton with a tabular region on the DEMO_CUSTOMER table. For our first test, let's try some inserts and updates. Our three post-submit processes will be:

10. ApplyMRU
15. Rollback
20. ApplyMRD

Click the Submit button, wait for the page to redisplay, and the envelope, please:  Any updates or added rows are committed.  The rollback has no effect; any updates or added rows are committed. This is not the behavior we expect. We expect a rollback to undo any inserts or updates from ApplyMRU.

For our second test, let's try some deletes. We'll move our rollback step after the ApplyMRD. Our three post-submit processes will look like this:

10. ApplyMRU
20. ApplyMRD
25. Rollback

Check a few delete boxes, click the Delete button, confirm that we want to delete the rows, wait for the page to refresh, and the envelope, please:  APEX displays a message saying the rows are deleted, but in fact, the deleted rows are still in the table. The rollback worked as we expected.

There are two important points: First, ApplyMRU takes an implicit commit. Second, ApplyMRU and ApplyMRD do not behave the same way! ApplyMRU and ApplyMRD are not executed at the same time, their process is driven by either the Submit button or Delete button, so we don't need to worry about running them together if the tabular form is the only updatable elements on the page.

But we do need to be careful if we run other processes after the submit button is clicked. If ApplyMRU runs first and commits changes, and if a second process runs and fails, then we have an half-completed transaction. Clearly, that's a bad thing. So, to the list of suggestions above, let's add two more:

7. Order post-submit process so that ApplyMRU and ApplyMRD run after any other process. If the early processes raise an error, the ApplyMRU and ApplyMRD will not run, and there will not be a half-completed transaction.
8. In some cases, it will make more sense to split a page into two (or more pages), and save all the database processing until the last page. APEX can save session variables on the first pages, and the session variables are available on the last page when all the database updates occur.

APEX is powerful development environment. However, if we want our transactions to process properly, if we don't want to spend time tracking down obscure bugs, then we need to be aware of when APEX takes implicit commits.

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.