Monday 14 December 2015

Tutorial: Include action button in report

In reality, this 'add' button could represent any action you would like in a report that would execute PL/SQL upon press of a row level button.

In this example I click on a button in a report to add the row to collection, without submitting the page.

Prepare page

Add Static id to your report region:

Add hidden page item: P2_ADD
Set protected = No if you need to submit the page for other processes.

Create before header process to initialise collection

Add link column

Define a new column in your report that will serve as the link button, it only returns null. For classic reports you could create a virtual column for the purposes of a link.

My report on emp uses this SQL
select e.*, null add_btn from emp e

Modify the new column and set the column type to 'Link'.

Set URL to: javascript:void(0);
Link text: Add
Link Attributes: data-id="#EMPNO#" class="add t-Button t-Button--warning t-Button--simple t-Button--stretch"

The data tag creates an attribute that we can interrogate easily with jQuery, returning the ID of the record.
The classes represent the same classes that would be applied when choosing relevant template options. You can explore these with the UT button builder.
The 'add' class is added for our dynamic action.

Create dynamic action

Create a new dynamic action that will respond to button press, using on click of a jQuery selector.

Use the following selector to identify add button clicks on your report.
#p2_my_report .add

Add an action to execute the following JavaScript. It sets the page item with the value of the ID set in the data tag.
$s('P2_ADD', $(this.triggeringElement).data('id'));

Don't forget to set this to not Fire on Page Load. this.triggeringElement represents the button pressed, which is generated as the following HTML.
<a href="javascript:void(0);" 
   class="add t-Button t-Button--warning t-Button--simple t-Button--stretch">Add</a>

Other JavaScript options

If other information was required, you could define more data tags, or traverse the DOM to find other values in the row. For IR you would need to first define static ID for the column as SAL. Classic reports automatically use the column alias.

If you were defining a remove function, then a second statement could be added to immediately hide the row from view without needing to refresh the report by locating the surrounding tr tag.
Though this may make pagination feel a little strange, as the number of rows displayed won't add up.

Or you could add a declarative true action to hide the triggering element, so the button can't be double clicked by a frustrated user.

Partial Page Refresh

May 2017 - I've added this section in response to a reader comment. I can't believe I neglected this property in the first place.

You'll find a problem if the report is refreshed due to a range of actions such re-sorting, applying filter, or perhaps invoked as a refresh action in yet another dynamic action - the on click dynamic action on our action button no longer works!

This is easily adjusted using the Event Scope property for the on click dynamic action, using the 'Dynamic' option (formerly 'Live').

Set Dynamic Action Event Scope to Dynamic

This maps to a jQuery setting that, as the item help describes:
Binds the event handler to the triggering element(s) for the lifetime of the current page, irrespective of any triggering elements being recreated via Partial Page Refresh (PPR).
The default option is static perhaps as the lowest common denominator, favouring speed. Most reports would have this adjusted to Dynamic.

The second property allows you to define the surrounding container of what's being refreshed, ie, the region. It's my understanding that you would include the following value.

And this would reduce the search area required to find our particular buttons, but I thought that was the purpose of supplying this as a surrounding ID/repeating class combination.

Execute the PL/SQL

You could then define a second action that executes PL/SQL, including P2_ADD in 'Page Items to Submit', so you can then refer to :P2_ADD as a bind variable in the PL/SQL. Note, you should always explicitly convert any value from session state that is not a string.

Alternatively, you could define an onChange event on P2_ADD which does the same thing. This would allow different UI on the page to invoke the same action.

The onChange dynamic action should only execute when the item is not null, and an action after the PL/SQL should clear the item. This allows the same value to be selected successively, otherwise the value wouldn't 'change' the second time around.
In the screenshot & example below I also refresh the region containing the collection.


So that describes a pattern I use frequently, and some variation of which is asked on the forums all the time. I plan to extend this example to include the collection report as a modal dialog with the ability to add & remove.

Run the demo to see it in action.

If you want to explore this further, you might like my book on jQuery with APEX. </shameless-plug>


  1. Hi Scott,
    The cover of the book that I recived looks quite different.
    Hm... - I think you should (have to) exchange your's at several web locations :-)

  2. I've updated the blog to use the final image, but I notice many of the online sites still use the first drafted icon. That's out of my control ;p

  3. thank you Scoot i have a question how can i have the id of the row where we clicked the button i need to use it in an pl/sql block

  4. Nice post Scott. Just what I needed.

  5. No worries. A favourite pattern of mine.

  6. What if the report is refreshed due to some other dynamic action ( e.g.: dynamic action that inserts a row into the report) ? the Add button's functionality is lost . Is there a way to preserve this ?


  7. Thanks for your tutorial
    but there is a problem in it :
    when you search in your first report for items then the add button in results doesn't work.
    also I tried this with a report that that has paging , when you go to the next page again the add button doesn't work.
    do you have any solution for this?

  8. Hello anonymous visitor,

    You've identified something I can't believe I neglected to include. The post will be updated accordingly.

    You need to set 'Event Scope' on the dynamic action to 'Dynamic'.

  9. What about, if i have two buttons?

  10. What about, if i have two buttons?

  11. You would change the first class in the list within Link Attributes to something else, eg:
    ... class="remove ...

    Then the jQuery selector in each relevant dynamic action would be
    #p2_my_report .add
    #p2_my_report .remove

  12. Helpful post. Thanks Scott.

  13. I could not make it work at all, I click the button and it does nothing, I did not quite understand how to do the dynamic action, it got very vague. If anyone can help me, Thanks.

  14. I could not make it work at all, I click the button and it does nothing, I did not quite understand how to do the dynamic action, it got very vague. If anyone can help me, Thanks.

  15. A recent forum post explores this well, you may find what you're missing conceptually in there

    Typically need to check DA scope, if you're trying to click after refreshing the region.
    Also try opening page in debug mode, then open browser console. See if any response appears in there on click. The selector may be wrong.

    I've just added to my list a post to explain debugging, using this solution as an example.

  16. Thanks for this very interesting post Scott, this is exactly what I need.
    This work perfectly well for the first click but does nothing on the second and followings...
    Even the first event on the button click does not seem to be fired anymore. I do the clean and refresh, etc... Do you have any idea on what could be wrong on my process ? Thanks in advance ! BR. Pierre

  17. Hi Pierre.
    If you're clicking on the same button, submitting the same data, then you either need to clear them item (remembering to add it to Page Items to Return), or remove the IS NOT NULL client condition on the DA. See my wording around that particular image in the post.

  18. The demo doesn't work now. I sometimes get a Ajax error. Other times, it just doesn't do anything.

  19. I'm not experiencing any problems at my end. There is a browser console error, but that's something to do with mixing content.

  20. I am using this example to try and create a button that will change the delete flag in that row to 'Y'. What would the SQL look like in the dynamic action to be able to change this flag?

  21. aball, I presume it would be a call to an API
    my_pk.logical_delete(p_id => :P2_ADD);

    Which does an update of your table.
    update my_table
    set delete_flag = 'Y' -- personally, I prefer deleted_date ;p
    where id = p_id;

  22. I'm assuming the my_pk means the primary key of the table but what if my table has more than 1 primary key. Also if I am understanding correctly do I need to create the API and if so how do I go about doing so?

  23. No, my_pk is an example name for a package that contains your procedural code.
    If you have a compound primary key, then you would be filtering all those in your where clause. The API can be generated from the SQL Workshop, or you could write it yourself.

  24. Would I need to change the data-id field in the link attribute field to account for this compound key?

  25. Ahh yes, that is correct. You would define multiple data- fields, eg:
    data-country="#COUNTRY#" data-state="#STATE#"
    And you would use .data('country')

  26. This has helped a lot but I am still stuck. I have this for the JavaScript Code to set the values of my page item:
    $s('P1_DELETE', $(this.triggeringElement).data('tax'), $(this.triggeringElement).data('prot'), $(this.triggerintElement).data('proc'), $(this.triggerintElement).data('port'), $(this.triggerintElement).data('low'));

    Would this be correct? I want to say it is not but I am very new to JavaScript and then how would I get all the values out of P1_DELETE for when I call on my logical_delete procedure?

  27. Close - you need to define multiple items
    $s('P1_DELETE_TAX', $(this.triggeringElement).data('tax'));
    $s('P1_DELETE_PROT', $(this.triggeringElement).data('prot'));
    And ensure you list these in page items to submit.
    You can then refer to them individually
    my_pk.logical_delete(p_tax => :P1_DELETE_TAX, p_prot => :P1_DELETE_PROT, ...);
    A verboseness which helps the argument for using surrogate keys.

  28. BEGIN
    my_pack.logical_delete(P_TAXONOMY_TX => to_char(:P1_DELETE_TAX),P_PROTOCOL_NM => to_char(:P1_DELETE_PROT), P_PROCESS_NM => to_char(:P1_DELETE_PROC), P_PORT_TYP => to_char(:P1_DELETE_PORT),P_LOW_PORT_NB => to_number(:P1_DELETE_LOW));

    This is the pl/sql code I am using to execute my package procedure but I am getting the error:
    PLS-00302: component 'LOGICAL_DELETE' must be declared and I am not sure why I am getting this error or how to fix it.

  29. You still need to write the package that does the work for you (the DML).
    PLS-302 refers to the fact no package/procedure yet exists.
    Bind variables are strings, no need for to_char().