Oracle APEX: Interactive Report with Checkboxes

This article describes the process of creating an interactive report page in Oracle APEX, where you can mark records for further processing. The
reader is expected to have basic knowledge of the APEX development environment and Oracle database.


A report with the ability to mark individual records for processing is a very convenient thing, but unfortunately has not yet been implemented out of the box. The APEX API has apex_item.checkbox2 function that allows you to show checkboxes in a report, but the programmer has to process them. Processing is greatly complicated by the fact that in a multi-page report, checkboxes exist only for the current page, and when switching between report pages, the values ​​of the checked checkboxes need to be saved somewhere. In this case, the APEX collection is used for this.

A few words about the APEX collection. The APEX collection is a named data structure that exists within a user session and is designed to manipulate data when building complex data entry forms, wizards, etc. The collection resembles its structure as a table of 50 attributes of type VARCHAR2 (4000), 5 attributes of type Number, 5 attributes of type Date, 1 attribute of type XML, 1 attribute of type BLOB and 1 attribute of type CLOB. Work with the collection through the PL / SQL API APEX_COLLECTION.

So, on the page we will use such tricks and tricks:

• Add checkboxes to the report using the apex_item.checkbox2 function.
• Changing the state of the checkbox will be processed using the javascript function called from Dynamic Action.
• we will save the state of the checkboxes in the APEX collection, for this we will call the OnJemand procedure to write to the collection by AJAX

We assume that we already have a page with an interactive report based on the request:

select  id, name, descr  from geo

where id is the primary key field of the table. We will pass it as an attribute of the checkbox to the collection, and we will use it when processing selected entries.

A.1 Create a process on the page:

Create → Page Component → Process →
Process type: PL / SQL code
Name: AJAX_UpdateChBoxCollection
Point: Ajax Callback
PL / SQL Page Process fill it with the following code:

declare
    l_value  varchar2(4000);
    l_seq_id number := 0;
    seq number := 0;
    l_collection_name constant varchar2(30) := 'CHBOXCOLL';
begin
------------------------------------------------------------------
-- Get the value of the global var which was set by JavaScript
------------------------------------------------------------------
l_value := apex_application.g_x01; 
------------------------------------------------------------------------
-- If our collection named doesn't exist yet, create it
------------------------------------------------------------------------
    if apex_collection.collection_exists( l_collection_name ) = FALSE then
       apex_collection.create_collection( p_collection_name => l_collection_name );
    end if;
---------------------------------------------------------------------
-- See if the specified value is already present in the collection
---------------------------------------------------------------------
    for c1 in (select seq_id  
                 from apex_collections  
                where collection_name = l_collection_name  
                  and c001 = l_value) loop
        l_seq_id := c1.seq_id;
        exit;
    end loop;
-------------------------------------------------------------------
-- If the current value was not found in the colleciton, add it. 
-- Otherwise, delete it from the collection.
-------------------------------------------------------------------
--    Htp.Prn('Seq:'||l_seq_id);
    if l_seq_id = 0 then
        begin
            seq := apex_collection.add_member( p_collection_name => l_collection_name,
                                               p_c001            => l_value );
--            Htp.Prn(' Set:'||l_value||' seq_id:'||seq);
        end;  
    else
        begin
            apex_collection.delete_member( p_collection_name => l_collection_name,
                                           p_seq             => l_seq_id );
--            Htp.Prn(' Rst:'||l_value);
        end;    
    end if;
    commit;
end;

This procedure creates a collection with the name “CHBOXCOLL” if it has not already been created,
and adds / removes an entry to the collection for the state-changed checkbox. The name of the collection is everywhere written in caps. It must be unique. As a result, the collection will have attributes of the marked entries.

A.2 Next, add to the page javascript code of the function that will call this process:

Edit Page → JavaScript → Function and Global Variable Declaration

function ajax_call_func(val) 
   { apex.server.process( "AJAX_UpdateChBoxCollection", 
                         { x01: val}, 
                         { dataType: "text",
                            success: function( pData ) 
                                             { console.log(pData); }  
                           }
                         );
    };

This function will call the function we created earlier with the name “AJAX_UpdateChBoxCollection” and pass the value “val” to it through the global variable apex_application.g_x01.
A result of type “text” can be returned from the procedure “AJAX_UpdateChBoxCollection”
using the function Htp.Prn ('some kind of result').

A.3 Create checkboxes in the report, for this we edit the request as follows:

select apex_item.checkbox2(p_idx => 1,
                           p_value => id,
                           p_attributes => 'class="chbox_UpdColl"',
                           p_checked_values => a.c001) cbox,
        id,name,descr
from geo,apex_collections a
 where a.c001 (+)= id
   and a.collection_name (+)= 'CHBOXCOLL'

In this case, a column is added to the request based on the apex_item.checkbox2 function,
as well as our collection associated with our table by id. We only need the collection to get the state of the checkbox at the initial page load and when switching between report pages. The type of column with checkboxes should be “Standart report column”.

I will briefly describe the parameters used apex_item.checkbox2:

p_idx => 1

the variable number APEX_APPLICATION 1 corresponds to F01; 2 - F02, etc.

p_value => id is the

value that will be passed to the collection, in this case this is the id field of the

p_attributes => 'class = "chbox_UpdColl"'

HTML attributes, in this case, the class = "chbox_UpdColl" tag is used, which is then used as the jQuery selector in Dynamic Action.

p_checked_values ​​=> a.c001

uses the value from the collection to indicate the state of the checkbox.

For clarity, we add another report based on the Sql request, which we will call Collecton and which will show us the contents of the collection:

select a.seq_id,a.c001
from apex_collections a
 where a.collection_name (+)= 'CHBOXCOLL'

A.4 Create a Dynamic Action that will fire upon changing the state of the checkbox and call the JavaScript function created in A.3 :

Event: Change
Selection Type: jQuery Selector
jQuery Selector: .chbox_UpdColl
Condition: none

Action: Execute JavaScript Code
Fire When Event Result Is: True
Fire On Page Load: False
Code:

var
$checkBox = $(this.triggeringElement);
ajax_call_func( $checkBox.val() );

The value of the 'class' attribute from the parameters of the apex_item.checkbox2 function in Section 3 is written into the jQuery Selector field . Before him, you must definitely put an end to it.

We add one more action to the created Dynamic Action - updating the collection report after clicking on the checkbox:

Action: Refresh
Selection Type: Region
Region: Collecton The

functionality of the checkboxes is already working, it remains only to add a handler for the records marked with checkboxes. I am going to issue an example of such a processor with sequential extraction of data from the collection using the cursor and writing it to a file as a separate article.

The basic idea is drawn here.. The author saves the marked checkboxes with a list in Item on the page.
The disadvantage is the maximum length of the stored list is 4000 characters. Using the collection in our case removes this restriction.

About using Ajax Callback in APEX is well written here .

The code was tested on the site apex.oracle.com on Application Express 5.1.0.00.45. Suggestions
and constructive criticism are welcome.