Oracle APEX: Interactive Report with Checkboxes

This article describes the process of creating a page in the Oracle APEX environment with an interactive report in which you can mark the entries for further processing. Supposed to
that the reader has basic knowledge of the APEX development environment and the Oracle database.


A report with the ability to mark individual records for processing is a very convenient thing, but unfortunately it has not yet been implemented out of the box. APEX API has apex_item.checkbox2 function that allows you to show checkboxes in the report, but the programmer has to process them. The processing is greatly complicated by the fact that in a multipage report, checkboxes exist only for the current page, and when navigating 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, designed to manipulate data when building complex data entry forms, wizards, etc. The structure resembles a table of 50 attributes of type VARCHAR2 (4000), 5 attributes of type Number 5, 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:
')

• checkboxes in the report will add using the function apex_item.checkbox2.
• We will process the checkbox state using the javascript function called from Dynamic Action.
• we will keep the state of checkboxes in the APEX collection, for this we will call the OnDemand AJAX procedure for writing to the collection.

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 the marked records.

P.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 is filled 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 named "CHBOXCOLL" if it has not yet been created,
and adds / deletes an entry in the collection for the state that changed the checkbox. The name of the collection is written everywhere caps. It must be unique. As a result, the collection will have attributes of the selected records.

A.2 Next, add to the page the 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 previously created function with the name “AJAX_UpdateChBoxCollection” and pass the value “val” to it via the global variable apex_application.g_x01.
The “text” result can be returned from the “AJAX_UpdateChBoxCollection” procedure when
using the function Htp.Prn ('some kind of result').

P.3 Create checkboxes in the report, for this we edit the query 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, the query adds a column based on the function apex_item.checkbox2,
as well as our collection associated with our table by id. We only need the collection to get the state of the checkbox when the page is initially loaded and when switching between report pages. The type of checkbox column should be “Standart report column”.

I will briefly describe the used parameters apex_item.checkbox2:

p_idx => 1

variable number APEX_APPLICATION 1 corresponds to F01; 2 - F02 and TP.

p_value => id

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

p_attributes => 'class = "chbox_UpdColl"'

HTML attributes, in this case, the class = tag “chbox_UpdColl” is used, which is then used as a 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, let's add another report based on a Sql query, 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' 

P.4 We will create a Dynamic Action which will be triggered by a change in the state of the checkbox and call the JavaScript function created in P.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 jQuery Selector field records the value of the 'class' attribute from the parameters of the apex_item.checkbox2 function in Clause 3 . Before it is necessary to put an end.

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 checkbox functionality is already working; all that remains is to add a handler to the checkbox-marked entries. I am going to arrange an example of such a handler with sequential extraction of data from the collection using the cursor and writing them to a file as a separate article.

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

The use of 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
Wishes and constructive criticism is welcome.