Checkboxes in Interactive Reports – Passing them via AJAX – Oracle APEX

Having multiple details for a master table on a single page is pain. Imagine if each of those details each has another detail or two. It’s a chain of never ending suffering. Especially when you’re doing it all on a single page and all the interactivity is done via simple Interactive Reports and popup Forms. A problem that usually arises is that when you want to delete/update multiple rows in an interactive report and use checkboxes for selecting those rows, once the page is submitted you lose control of which rows you had selected. This can all be implemented using Branches and Page Load Dynamic Actions, but why go through all the hassle?

What I’m going to propose in this post is using AJAX (which beautifully stands for Asynchronous JavaScript and XML) for deleting rows selected using the checkboxes.

Although I must note that none of this was possible without the help of Roel Hartman and Maxime Tremblay in the APEX slack channel.

The first step is to create a table called CHECKBOX_TABLE. This is a much simpler version of the EMP table which has a SRL column (short for serial) for storing primary keys and a NAME column which stores the name of the employee.

This is the SQL for creating such a table :

CREATE TABLE  "CHECKBOX_TABLE" 
   (	"SRL" NUMBER NOT NULL ENABLE, 
	"NAME" VARCHAR2(50), 
	 CONSTRAINT "CHECKBOX_TABLE_PK" 
         PRIMARY KEY ("SRL")
  USING INDEX  ENABLE
   )
/

After creating the table, we’ll create an Interactive Report on the table using the following select statement :

select 
CHECKBOX_TABLE.SRL as SRL,
CHECKBOX_TABLE.NAME as NAME,
APEX_ITEM.CHECKBOX2(p_idx => 1, 
                    p_value => SRL,
                    p_attributes => 'class="boxes"') AS SELECTOR
 from CHECKBOX_TABLE CHECKBOX_TABLE

We’ve rendered our checkboxes using the APEX_ITEM.CHECKBOX2 function. The checkboxes have an ID of 1 (representing the APEX APPLICATION Global Variable 1, referenced by APEX_APPLICATION.G_F01) and their values are set according to the ID of each employee (SRL). In the attributes section a class called boxes has been added to each checkbox so that we can easily reference it in our jQuery selector.

The next step is to create the process which actually deletes the rows we have selected. The code for the process is as below :

BEGIN
 FOR I IN 1..APEX_APPLICATION.G_F01.COUNT 
 LOOP
 DELETE CHECKBOX_TABLE
 WHERE SRL = APEX_APPLICATION.G_F01(I);
 END LOOP;
 htp.p('{ "message": "' || APEX_APPLICATION.G_F01.COUNT || ' rows deleted" }');
END;

It’s a simple PL/SQL which deletes the rows based by looping through the G_F01 array (selected checkboxes). I will explain the htp.p section in a bit.

So where the issue? If I set this code/process as a normal process in my page’s processing section and get it to run when I press a specific button, it runs perfectly. The page gets submitted via that specific button, the process gets run and when the page reloads the selected rows are deleted. But what if I insert the same code in a dynamic action and get the action to Execute PL/SQL Code? Fail! It won’t run because the G_F01 hasn’t been submitted.

So how can we run the above code in a dynamic action and also set and then pass the values of the G_F01 array to the server? That’s where AJAX jumps in.

In order to get it to work, we must move the PL/SQL code’s point of processing to AJAX Callback. I’ll be calling it DELETE_ROWS. You can see how I’ve defined it in the image below :

ajax callback

Let’s create a button and a Dynamic Action on that button which executes some JavaScript and then refreshes the interactive report region. This is the JavaScript code :

var f01_array = [];

/* Push the values of each checkbox in the above array */
$('.boxes:checked').each(function() {
 f01_array.push($(this).val());
});

/* Run the AJAX process to delete the rows and pass the F01 array */
apex.server.process( "DELETE_ROWS", 
 { f01 : f01_array },
 { success : function(data){ 
 alert(data.message); 
 }
 }
 );

Here’s an image of how it’s all laid out :

javascript code

What happens in the code above is that a simple JavaScript array has been defined called f01_array. We will be pushing the values of the checkboxes into this array one by one as they are checked. Afterwards we call the apex.server.process JavaScript API  to run the DELETE_ROWS process we created by passing its name to it. We also populate the global f01 array by passing the f01_array to it. As Roel pointed out, lots of people (including me) don’t know that these arrays can be populated in the apex.server.process function.

In the end when the AJAX call is successful and gets the response returned after running the PL/SQL block, it shows the number of rows deleted through an alert prompt. This data is sent back using this bit of code :

htp.p('{ "message": "' || APEX_APPLICATION.G_F01.COUNT || ' rows deleted" }');

We’re passing a JSON string containing the number of rows deleted.

It is also stated in the API documentation for the pOptions Object that we can pass to apex.server.process that :

See jQuery documentation of jQuery.ajax for these supported
options: accepts, dataType, beforeSend, contents,
converters, dataFilter, headers, complete, statusCode,
error, success. The dataType option defaults to json.

Visiting http://api.jquery.com/jquery.ajax/ reveals that we can set our dataType as text to prevent any issues when not returning JSON in our PL/SQL using:

{ dataType : “text” }

That’s all there is to it. You can visit the link below to see a working example :

https://apex.oracle.com/pls/apex/f?p=9468:7

Update – Add Select All Checkboxes in Column Heading

I have received a few emails regarding how I implemented the Select All checkbox in the column header so I decided to update this blog post to document how it’s done for future use. All that needs to be done is to create a checkbox instead of its heading which has an onclick event that selects all the other checkboxes. The code is as following :

<input type="Checkbox" onclick="$f_CheckFirstColumn(this)">

I also recommend not letting the users do any actions on this certain column, as clicking on the column heading could cause the filter menu to open. This image shows all the required settings :

select all checkboxes

Note : When using Universal Theme this functionality doesn’t work and it is some sort of a semi-bug. The reason is that the column header and column rows are two separate <table> tags. What needs to be done is to edit your interactive report’s Attributes and set its Heading’s “Fixed To” to “None”.

attributes fixed to

 

If any questions exist feel free to contact me via email. If you’re more of a twitter fan, you can find my twitter handle at the bottom of the page in the footer.

5,850 total views, 3 views today

8 Replies to “Checkboxes in Interactive Reports – Passing them via AJAX – Oracle APEX”

  1. hey i have tried to implement what you have done.
    i tried copying your exact process but i keep getting an error.
    ‘Error: SyntaxError: Unexpected token s in JSON at position 0’.
    im lost in terms of trouble shooting do you have any idea why im getting this error?
    Thanks

    1. Hi Taha,

      I also received an email regarding the same question from you. I replied through email. Check your inbox and get back to me.

  2. Hi, I tried to implement the same. But it gives an error ‘Error: SyntaxError: Unexpected token P in JSON at position 0’.
    Could you please help me out.?

    1. Hi Kiran,

      Can you recreate an example on apex.oracle.com and give me the credentials? I’ll check it out for you and tell you where you’ve gone wrong.

  3. Hi there,
    I created almost similar process that has to insert selected [checked] rows into a table, but I kept getting the error message “Error: parsererror – SyntaxError: Unexpected token P in JSON at position 0”
    I don’t know what I am doing wrong?

    Here is the JS [Button with DA]:
    if (confirm(‘Do you really want to select this record?’)) {
    var f01_array = [];
    /* Push the values of each checkbox in the above array */
    $(‘.section-ids:checked’).each(function(){
    f01_array.push($(this).val());
    });
    /* Run the AJAX process to delete the rows and pass the F01 array */
    apex.server.process( “INSERT_ROWS”,
    { f01 : f01_array },
    { success : function(data){
    alert(data.message);
    }
    }
    );
    }
    And here is the process:
    DECLARE
    section_id WWV_FLOW_GLOBAL.VC_ARR2;
    begin
    for i in 1 .. apex_application.g_f01.count
    loop
    Insert into nms_selected_sections t1 (t1.section,t1.date_selected, t1.selected_by, t1.xml_contents)
    select t2.id, t2.approval_date, t2.created_by, t2.xml_contents
    from sections t2
    –where id = apex_application.g_f01( apex_application.g_f01(i));
    where id = apex_application.g_f01(i);
    –where id = HTMLDB_APPLICATION.g_f01(i);
    end loop;
    htp.p(‘{ “message”: “‘ || APEX_APPLICATION.G_F01.COUNT || ‘ Rows Inserted…” }’);
    –commit;
    end;

    Your help is very appreciative.
    Thank you,
    amal

    1. Hi Amal,

      Can you please create an example on apex.oracle.com so I can test? There seems to be no problem with your code, so create the example and give me access to your workspace so I can debug it.

      Thanks

  4. Thx for this example in regards of the checkboxes!

    One question is left to me. How do I uncheck the box of the header when
    I uncheck one of all the checked rows?

    Thx in advance
    Christian

    1. Hi Christian,

      Thanks for the feedback. In order to do so, you can :

      Step 1 : Add the following class to the HTML for the checkbox in the heading ===> class=”selector”

      Step 2 : Create a dynamic action with the Event of “Click” and make its selection type a jQuery selector. This is the selector === >.boxes

      Step 3 : Set its true action to “Execute JavaScript Code” with the following code :

      if($(‘.selector:checked’).length > 0) {
      $(‘.selector’).prop(‘checked’, false)
      }

      I’ve added the exact same behaviour to the demo page in the tutorial.

Leave a Reply

Your email address will not be published. Required fields are marked *

 
Loading Facebook Comments ...