Set Multiple Items From Query

From time to time, it happens I have to set multiple items without submitting the page. So one day I decided to write a solution - not just for a particular table or form but a more generic one that I could reuse again.

The goal - create a procedure which would accept two parameters: a query and page items to set. The query would of course deliver values for these items.
The approach - On Demad Process (retrieving values) and JavaScript function (populating values).

In this post I assume that the reader is already familiar with incorporating JavaScript into APEX and what On Demad Process is as well as how to make an AJAX call from APEX. If not, please check here.

On Demand Process

The On Demad Process should retrieve the values from the query and return them JSON formatted. Fortunately for me, there was already a procedure that takes care of it: APEX_UTIL.JSON_FROM_ITEMS as I found it in APEX forum and here. One needs to set the session state of the items and then call this procedure. So more than half the work was already done:

declare
 l_dname  varchar2(200) := wwv_flow.g_x01;
begin
 for c_dept in (select * from dept WHERE DNAME = l_dname) loop
  APEX_UTIL.SET_SESSION_STATE('P4_DEPTNO',c_dept.DEPTNO);
  APEX_UTIL.SET_SESSION_STATE('P4_LOC',c_dept.LOC);
 end loop;
 apex_util.json_from_items('P4_DEPTNO:P4_LOC');
end; *

* The code snipe is from the Rutger's blog mentioned above.

My effort now was to turn the code above into something more dynamic where the query and the items can be passed as a parameter, something like this:

declare
  p_my_query       -- here my query as a parameter
  p_items_to_set   -- items to set also as a parameter
begin
 -- loop through the columns
 for cursor_for_my_query in ( p_my_query ) loop
   -- set the session state for the given items: p_items_to_set
   SET_SESSION_STATE ( array_of_items_to_set ( loop_variable ), array_of_column_values_from_cursor ( loop_variable ) );
 end loop;
 -- the rest...
 json_from_items(p_items_to_set);
end;

There is only a problem with the cursor: to reference columns one needs to know the column names - which are, at this point, unknown because the query is passed during the runtime. There is a way to get the column name from the dynamic query with the help of DBMS_SQL but it's a long way. Another trick would be passing the query to a collection and then walking through it to set the session state of items. I haven't tried that because, lucky me, at the moment I'm working on the APEX 4.0 and there is the package APEX_PLUGIN_UTIL which does the job I need. The code below (on demand process, called GET_ITEMS) shows how to achieve it. Attention! The code is simplified for better readability.

declare
  -- the sql statement passed through the global variable x01
  l_stmt           varchar2(4000) := apex_application.g_x01;
  -- the items that we want to set (x02)
  l_items_to_set   varchar2(1000) := apex_application.g_x02;  

  -- help variables
  a_items     apex_application_global.vc_arr2;
  l_RowSet APEX_PLUGIN_UTIL.t_column_value_list;
  l_sqlHandler APEX_PLUGIN_UTIL.t_sql_handler;
begin
  -- Get the sql handler
  l_sqlHandler := APEX_PLUGIN_UTIL.GET_SQL_HANDLER ( p_sql_statement => l_stmt
                                                    ,p_min_columns => 1
                                                    ,p_max_columns => 100
                                                    ,p_component_name => null );
  -- Prepare query - for the first row only
  APEX_PLUGIN_UTIL.PREPARE_QUERY ( p_sql_handler => l_sqlHandler, p_max_rows => 1 );

  -- Get data
  l_RowSet := APEX_PLUGIN_UTIL.GET_DATA ( l_sqlHandler ) ;

  -- Get the items and put them into a table, so they can be easily looped
  a_items := APEX_UTIL.STRING_TO_TABLE (l_items_to_set, ',' );

  -- Loop through the columns and items and set the session state
  for col in 1..l_RowSet.count loop
     APEX_UTIL.SET_SESSION_STATE( a_items(col), l_RowSet(col)(1) );
  end loop; 

  -- Call the procedure to create JSON string of items and values
  APEX_UTIL.JSON_FROM_ITEMS(l_items_to_set, ',');
end;

 Here the process from above explained in plain English:

1. Prepare the query: e.g., columns and rows to be retrieved (here only one row)
2. Fetch the data
3. Walk through the columns and set the session state for the given items
4. Call the procedure JSON_FROM_ITEMS which gives the JSON formatted string which can then be used in the JavaScript function (below).

JavaScript function

Here the corresponding JavaScript function to call the on demand process:

function set_multiple_items() {
  var get = new htmldb_Get(null, $v('pFlowId'), "APPLICATION_PROCESS=GET_ITEMS", $v('pFlowStepId'));
  var gReturn  = null;
  // pass the parameters that we need:
  // the query
  get.addParam('x01', 'select ename, job, hiredate, sal, comm from emp where empno=7839'); 
  // and the corresponding items to set
  get.addParam('x02', 'P1_ENAME,P1_JOB,P1_HIREDATE,P1_SAL,P1_COMM');
  gReturn = get.get();
  // assign the values
  json_SetItems(gReturn);
  get = null;
};

After working with it for a while I decided to turn the code into a plugin. I added one more parameter - Page Item To Submit, so I can change the item with JavaScript and include it in the where clause.
The plugin can be seen here in action and will be is published soon on the apex-plugin page.

I suppose that in the next APEX version this plugin will not be necessary.

Last but not least: I couldn't find any documentation neither for the APEX_UTIL.JSON_FROM_ITEMS nor json_SetItems, so I owe a huge credit to Rutger and APEX forum for writing about this.