Wednesday, July 7, 2010

Dynamic Actions

One of the killer features of Apex 4.0 are Dynamic Actions. What a fantastic way to declaratively include JQuery and Ajax events into your application! I never really enjoyed placing javascript calls all over the place to achieve the rich functionality in the earlier versions of Apex. Too much work, and too hard to maintain.

Now you have Dynamic Actions, and all the little knobs and wheels show up in the page editor as any other kind of objects. The Apex-team has done a marvelous job of integrating JQuery into the framework.

How Dynamic are Dynamic Actions?
As I upgrade applications I want to get rid of all the little Javascript function calls that are all over, and replace them with Dynamic Actions. As I go along, there are a few challenges, but no show stoppers (yet). Below is a description on one of the hurdles (well, more like a small bump) passed along the way.

I find that Dynamic Actions are extremely versatile, but you should have some knowledge of JQuery selectors to realise it's full potential.

Invoking Dynamic Actions from Report Columns
In the editor there are no possibility of attaching dynamic actions to tabular forms columns. I wanted to invoke an Ajax-call to commit changes when the user changed a select list in a tabular form, here is a way you can do the same.

If you just want to inspect the code for yourself, I have created a small application you can download from apex.oracle.com: http://apex.oracle.com/pls/apex/p?n=1070334102765726226

First off, create a tabular form, in this example I have a select that uses the APEX_ITEM-api to create application items:
select apex_item.hidden(1, emp.empno) empno
,      apex_item.display_and_save(2, emp.ename) ename
,      apex_item.select_list_from_query(3, emp.deptno, 'select dname d, deptno r from dept order by dname', p_item_id => 'empno'||empno) deptno       
from   emp
Next, create two hidden items, one for the primary key (P1_EMPNO), and one for the changed column value (P1_DEPTNO).
Next, create a Dynamic Action on the page level to invoke the update statement:

Right click Dynamic Actions on the page level, and choose Create

Choose Advanced

Give the Dynamic Action a name

Set the event to trigger on Change, set Selection Type to JQuery Selector and set JQuery Selector to "select[id^=empno]". This is where we set up the Dynamic Action to fire on every change to all items where the item ID starts with "empno". If you notice the select statement above, I explicitly set the item ID when creating the select lists.
Now I have set up when the event is supposed to trigger, next I define what is actually going to happen once the Action is triggered.

Set the Action to Execute PL/SQL Code, and the PL/SQL Code to the update statement below.
update emp
   set deptno = :P1_DEPTNO
 where empno = :P1_EMPNO;

In addition you must set Page Items to Submit with the request (P1_DEPTNO and P1_EMPNO):
Click create and you are almost done.

Now the Dynamic Action is defined in essence, but there is one piece missing: When the user changes the values of the select list, the new DEPTNO-value and the current EMPNO must be reflected in P1_DEPTNO and P1_EMPNO before the PL/SQL-Code (in this case the update statement) is invoked. This is where the pure genious of the Dynamic Actions come into play; Apex allows you to create additional Actions to execute in the same event.

To set the values of P1_DEPTNO and P1_EMPNO, do the following:

Open the editor for the Dynamic Action
Under True Actions region, click Add True Action

Set the sequence to something lower than the PL/SQL-action (normally this means setting it below 10)
Set the Action to Set Value
Make sure the Fire on Page Load is unchecked
Set Set Type to Javascript Expression
Set Javascript Expression to this.triggeringElement.value, which basically means get the value of the triggering element
Under Affected Elements, set Selection Type to Item(s) and Item(s) to P1_DEPTNO


Now we have to do the same for P1_EMPNO, this almost the same as for P1_DEPTNO, but we have to get the EMPNO-value from the ID of the triggering element. To achieve this, Click Add True Action, and set it up as seen below:

The Javascript code gets the ID of the triggering element, and strips off the leading empno-string. The remainder is the actual EMPNO of the employee affected.

When you return to the Dynamic Actions editor, this is what the True Actions should look like:

Run the page to check that it works.

A Word of Caution
This was a special and simplified case, normally you should use the native tabular forms functionality in Apex. There are several reasons for this. There is no MD5-checking here (data has been changed by another user), and every little change will result in a round trip to the server (no batch submit and no way for user to cancel changes).

Either way, Dynamic Actions rock!

5 comments:

  1. Hi Håvard,

    Great example and an interesting use case and thanks for the endorsement!!

    By the way, I noticed that the 2 'True Actions' that set the value for the emp and dept items state to use the 'Execute Javascript Code' action type, but I think this should be 'Set Value' action type (with a 'Set Type' of 'JavaScript Expression').

    Also, you're application download has a runtime error because you have a reference to some local debugging table in the 'Execute PL/SQL Code' action.

    Other than that, looks great.

    Regards,
    Anthony.

    ReplyDelete
  2. Hi Anthony,

    Thanks for the heads up! I could not even retype my own example (and even that example was flawed with old debug-code)... That's what you get from being hasty :-)

    Hopefully both example application and explanation has been rectified now.

    You truly did a great job with the Dynamic Actions!

    Regards,
    Håvard

    ReplyDelete
  3. error in sql query ORA-00907: missing right parenthesis pls help me

    ReplyDelete
  4. Why when I delete a dynamic action, this still works?

    ReplyDelete
  5. I have a similar problem however I am trying to run the following code:
    DECLARE
    no_value EXCEPTION;
    curr_amount NUMBER;
    new_balance NUMBER;

    BEGIN
    IF(:P4_XDEPOSIT = '') THEN
    RAISE no_value;
    ELSE
    IF(:P4_XDEPOSITRADIO = 'Savings')
    SELECT balance INTO curr_amount FROM accounts WHERE accno = :P4_XSAVINGS23;
    new_balance := curr_amount + :P4_XDEPOSIT;
    UPDATE accounts SET balance = new_balance WHERE accno = :P4_XSAVINGS23;
    DBMS_OUTPUT.PUT_LINE("Transaction Completed.");
    END IF;
    END IF;

    EXCEPTION
    WHEN no_value THEN
    DBMS_OUTPUT.PUT_LINE("The deposit value does not exist...");
    END;
    *******************************************************************************

    I am not planning on having any return values as you can see. I get the following error when I run the code:

    1 error has occurred
    ORA-06550: line 13, column 9: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: * & = - + < / > at in is mod remainder not rem then <> or != or ~= >= <= <> and or like like2 like4 likec between overlaps || multiset year day member submultiset The symbol "then" was substituted for "SELECT" to continue. ORA-06550: line 22, column 26: PLS-00114: identifier 'The deposit value does not exi' too long

    Thanx in advance!

    ReplyDelete