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:
- Prepare the query: e.g., columns and rows to be retrieved (here only one row)
- Fetch the data
- Walk through the columns and set the session state for the given items
- 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
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.
