RDS, The PluginRegion Display Selector - How I (Would) Like It, Part Two.

Update
For the update on ORA-06550 and ORA-06502 please see the end of the post.

The day has come and I have decided to write the RDS plugin instead of dealing with dynamic action(s) (see the previous post).

My requirements for the new RDS:

1. Possibility to choose the region(s) going into RDS
2. Customizability of the "Show All" label*: (a) show/hide, (b) label name, (c) label position

* I keep calling the "Show All" entry a "label" and I will stick with it.

Ingredients:

1. HTML - the structure of the RDS - how it will be displayed on the page
2. JavaScript - for the click event - showing and hiding corresponding regions
3. SQL - a query returning (at least) the names and ids of the regions going into my new RDS - which would feed the html structure from above

Attention: The blog post prerequisites that Firebug and jQuery are familiar terms to you, so the sections HTML and JavaScript won't need any further explanations.

1. HTML

Because I want my RDS to look exactly like the "original one," I will use the existing structure of the RDS which, simplified, looks as follows:

<div>
  <ul id="ID of the RDS which will be needed for the js with the suffix '_RDS'">
    <li><a href="# + ID-of-the-first-region">TITLE of the first region</a></li>
    <li><a href="# + ID-of-the-second-region">TITLE of the second region</a></li>
    ...
  </ul>
</div>

As you can see, I only need the titles and ids of the regions and these are to be found in the view APEX_APPLICATION_PAGE_REGIONS.

2. JavaScript
I'm not going to reinvent the wheel but use the already existing apex widget:

apex.widget.regionDisplaySelector("HERE ID OF MY PLUGIN");

However, because my plugin behaves slightly different than the original regarding the position of the "Show All" label, I need to simulate a click on the first tab:

$("ID OF MY PLUGIN > li:first > a:first").trigger("click");

3. SQL
I want to keep it simple and still have control over the "Show All", and I don't want to do much in the loop as you will see later. Basically I will have two queries which I will join by union all. I call them: "Region query" and "Label query". "Region query" is the one to get the regions, and the "Label query" is for the "Show All" label.

Region query
My first requirement is the ability to choose the region(s) depending on a given attribute. I decided on class name, because this is easily updated in the regions themselves. The name of the class will be held in the variable l_class, and the corresponding line in the where clause will be:

decode(l_group_class, null, 1, instr(replace(aapr.REGION_ATTRIBUTES_SUBSTITUTION, '"', ' '), l_group_class)) > 0

The long line in plain English: if the variable l_class is not set (the plugin should contain all regions), then the query is not restricted (1 > 0 is always true). Otherwise, the column will be searched for the class name.
The additional replace quotation marks with space ('"' with ' ') is to avoid selecting "a-one", where "one" is desired.
The rows are restricted of course by the id of the application and page as well as by the regions marked as going into RDS.

         --region name needed for the "tab" 
select   aapr.REGION_NAME                                                  reg_name
         -- if no id (STATIC_ID) is given, then the one from apex (REGION_ID)
         -- and add the prefix '#R' or '#' respectively - needed for the click event
        ,decode (aapr.STATIC_ID
                          ,null, '#R' || to_char(aapr.REGION_ID)
                               , '#'  || aapr.STATIC_ID)                   reg_id                    
  from   APEX_APPLICATION_PAGE_REGIONS aapr 
        -- current application
 where   aapr.APPLICATION_ID = wwv_flow.g_flow_id 
         -- current page
   and   aapr.PAGE_ID = wwv_flow.g_flow_step_id
         -- regions going into RDS
   and   aapr.DISPLAY_REGION_SELECTOR = 'Yes'
         -- l_class is the variable holding the class name
         -- if the class name is not set, RDS behaves like the classic one and holds all corresponding regions 
   and   decode(l_class, null, 1, instr(aapr.REGION_ATTRIBUTES_SUBSTITUTION, l_class)) > 0
         -- here will be another condition which is described further below
   and   ...   
         -- it is important to have them in the right order
 order by aapr.DISPLAY_SEQUENCE

Then I wrap the query in order to:
1. keep the order by clause - as I mentioned before, the query is part of a union all and order by would raise an error
2. add a column (rownum) - representing here the display order and replacing the DISPLAY_SEQUENCE column. I could stick with the DISPLAY_SEQUENCE, but rownum gives me more flexibility in order to customize the position of the label "Show All".

select   regions.reg_name
        ,regions.region id 
        ,rownum  seq 
  from  ( the query from above ) regions

Label query
My other requirement for the plugin is the ability to have control over the "Show All" label.
For this I need some variables:
l_All_display - Should "Show All" be displayed or not (1 - displayed; 0 - not displayed), put in the where clause makes sure that the query returns one or no rows (1=1 or 1=0)
l_ALL_label - the custom label for "Show all"
l_ALL_position - the possible positions first or last, default is last (0 - first ; null - last), joined with the region query results in the first or last position

And the query for the label "Show All" (column aliases are added for better readability)

select   l_ALL_label       reg_name
        ,'#SHOW_ALL'       reg_id
        ,l_ALL_position    seq
  from  dual
 where  1=l_All_display

Region query + Label query
I join the queries from above and wrap them:
1. to get the count of the rows, which I will need to find the first and the last row for the <li> class, and
2. to sort the rows - now also with the "Show All" label which, depending on the value in variable l_ALL_position, will take the first or the last position respectively:

select   regions_and_label.*
        ,COUNT(*) OVER ()    reg_count 
  from  ( 
          Region query 
          union all
          Label query
        ) regions_and_label
 order by seq nulls last

Then I add the very last "wrapper" to finally:
1. get the class names for the first and the last row
2. and check if the query should return any rows (at least 1 region and "Show All", or 2 regions without "Show All")

select   decode(rownum, 1, ' class="apex-rds-first"', reg_count, ' class="apex-rds-last"') attr
        ,all_with_count.*
 from  ( the query from above ) all_with_count
 where  reg_count > 1

Now I can loop through the records and, with the help of the htp.p, display the RDS list:

for c in (here the query from above which returns name, id and attribute) 
loop
  htp.p('<li' || c.attr || '>');
  htp.p('<a href="' || c.reg_id || '"><span>' || c.reg_name ||' </span></a></li>');
end loop;

Last but not least

There is still something very important missing: the query doesn't regard the condition(s) of the regions. 

As you know, you can set conditions on regions and, depending on these conditions, the regions will be displayed. I was looking for such a function and found an old blog from Martin How to determine if user can view an APEX region. Unfortunately I couldn't use it in the plugin so I looked further and then in the APEX_PLUGIN_UTIL package found something called IS_COMPONENT_USED.

SQL> desc apex_plugin_util.is_component_used
Parameter                 Type     Mode Default? 
------------------------- -------- ---- -------- 
(RESULT)                  BOOLEAN                
P_BUILD_OPTION_ID         NUMBER   IN   Y        
P_AUTHORIZATION_SCHEME_ID VARCHAR2 IN            
P_CONDITION_TYPE          VARCHAR2 IN            
P_CONDITION_EXPRESSION1   VARCHAR2 IN            
P_CONDITION_EXPRESSION2   VARCHAR2 IN            
P_COMPONENT               VARCHAR2 IN   Y         

This sounded very promising to me. I cannot tell you how often the APEX_PLUGIN_UTIL package has made my life easier. So I set a condition in one of the regions on "Never", and I ran this and got:

ORA-20001: Unable to process condition type "Never" in wwv_flow_conditions.

The day was long, the function is undocumented. So I decided to leave it where it was and add another parameter to my plugin: authorisation function. If the developer had such an authorisation function, she/he could pass it to the query through this parameter.

At this point I decided to not publish the plugin - and if yes, then only on this blog, and let the readers decide if they want to have this half-done solution. A while later (I'm a very slow publisher, so the blog wasn't written yet), I read the call for questions for "Ask the ClariFit Experts" webinar and submitted this problem to Martin. And I got the answer! I used the right column with the wrong value.
APEX_APPLICATION_PAGE_REGIONS is a public view of WWV_FLOW_PAGE_PLUGS (amongst others), but the condition types in both views are not the same. In the public view, the value of the column PLUG_DISPLAY_CONDITION_TYPE is replaced by a column from view APEX_STANDARD_CONDITIONS. Executing the function with the values from WWV_FLOW_PAGE_PLUGS doesn't raise an exception and the function works. Thank you Martin! He wanted to write a blog post about it, so make sure you read it.
Other good news is that there is no need to expose any wwv_flow tables because APEX_STANDARD_CONDITIONS is public.

For the IS_COMPONENT_USED function I need to replace the values from APEX_APPLICATION_PAGE_REGIONS back to the "original" ones from WWV_FLOW_PAGE_PLUGS with the help of APEX_STANDARD_CONDITIONS view.

Because the function returns boolean, I cannot use it directly in a query. In order to do so, the function needs to be wrapped in a function returning something other than boolean, something like this:

...
if apex_plugin_util.is_component_used(
     p_build_option_id => BUILD_OPTION_ID
    ,p_authorization_scheme_id => AUTHORIZATION_SCHEME_ID 
    ,p_condition_type          => the value from APEX_STANDARD_CONDITIONS view
    ,p_condition_expression1   => CONDITION_EXPRESSION1
    ,p_condition_expression2   => CONDITION_EXPRESSION2
    ,p_component => 'region') 
then 
  return 1; 
else
  return 0; 
end if;
...

And the problem is, in order to be able to use it in a query, the function must be public.

Why I want to do everything in the query? Because it's easier. This way I really know which row is the first and which the last - and I need this information for the proper display of the tabs. How can I achieve that? The answer is again provided by APEX_PLUGIN_UTIL. With the function GET_PLSQL_FUNCTION_RESULT I can execute any pl/sql

code in a similar way as with execute immediate but better - I can indeed use it in a query in the where clause:

1 = apex_plugin_util.get_plsql_function_result(
            'begin ' ||
              'if '||
                'apex_plugin_util.is_component_used('||
                     'p_build_option_id => ''' || aapr.BUILD_OPTION_ID ||
                  ''',p_authorization_scheme_id =>''' || aapr.AUTHORIZATION_SCHEME_ID ||
                  ''',p_condition_type          =>''' || (select d from apex_standard_conditions where r = aapr.CONDITION_TYPE) ||
                  ''',p_condition_expression1   =>''' || aapr.CONDITION_EXPRESSION1 ||
                  ''',p_condition_expression2   =>''' || aapr.CONDITION_EXPRESSION2 ||
                  ''',p_component => ''region'') ' ||
              'then ' ||
                'return 1;' ||
              'else ' ||
                'return 0;'||
              'end if; ' ||
            'end;')

What I have learned from this? If I get an error by executing a code using apex public views, I should try the wwv_flow tables and then, if it's working, investigate the view.

Another very helpful trick that I just found on Martin's blog is "How do they do it?". I will definitively try this out.

The plugin can be downloaded from the plugins repository and from the demo application.

Martin, thank you again.


Update
ORA-06550 - The plugin works starting with APEX version 4.0.2 (plugin developed in 4.0.2.00.08). The function IS_COMPONENT_USED is not available in prior versions.
ORA-06502 - In 10g (probably only in XE), there is a problem with executing to_char() in a for loop cursor.
The temporary solution is to set "Static ID" of the regions going into the RDS so that the condition aapr.STATIC_ID is null doesn't get executed:

decode (aapr.STATIC_ID ,null, '#R' || to_char(aapr.REGION_ID), '#' || aapr.STATIC_ID) reg_id

I'm testing a different rendering of the plugin in order to avoid the bug.
The new version is available now.
Thanks to everybody who has pointed out the problem.
I also owe huge credit to Jose Murillo for his thorough testing of version 1.1 of the plugin.

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.

Region Display Selector - How I (Would) Like It

In APEX4 I welcomed very much the new region type - the Region Display Selector. The only thing I don't like is how it displays the regions. When I see all, I see nothing. So I decided to customize the RDS a bit - on page load, not all the regions should be shown, only the first one.

Approach: Find the second tab and simulate the click on it when the page loads.
With firefox and firebug this is done fast: rds tabs is the list with the class 'apex-rds'.
Here is the jQuery for my purpose:
$('ul.apex-rds > li:eq(1) > a').trigger('click');

Explanation:
Find the <a>, which is in the second <li>, which is in the <ul> with the class 'apex-rds' and simulate a click.
And I'm done.
The code can be put into a dynamic action or in a js-file - depending on one's needs.

Done. Almost, because somehow I am confused that the second and not first tab is selected.
Just changing the position of the 'Show all' would make me happy:

$('ul.apex-rds')                        // find the <ul> with the tabs
  .each(function(){                     // in case we have more than one RDS region
    $(this)
      .children('li.apex-rds-first')    // find the tab 'Show all'
      .remove()                         // remove it-the function returns the removed element itself
      .removeClass('apex-rds-first')    // switch the classes
      .addClass('apex-rds-last')
      .appendTo($(this))                // append to the ul again
      .prev('li.apex-rds-last')         // go to the one that was last before
      .removeClass('apex-rds-last')     // remove class
      .siblings('li:first')             // find the new first
      .addClass('apex-rds-first')       // add the class
      .children('a:first')              // find the href in it
      .trigger('click');                // and symulate a click
});

The last thing. I really like the new RDS region... but I am not able to choose which region (with the RDS flag) goes into this particular container. When you create a second RDS on the page, the result is quite confusing.

For an update please see the part two.

Dynamic Action on Button-Item

Reading the post How to Trigger Dynamic Action From Button by Martin Giffy D'Souza, I decided to give the dynamic action on buttons another try.
The action on the 'normal' button (in a region position) can be added as described and works perfectly, but if I want to do the same with the button-item, I get an unwanted effect - despite of disabling/rewriting the click event, the site will redirect: the apex.submit does the job.
The only trick that works is to remove the attribute 'onclick' explicitly attr('onclick','') and then to add the new event.

Create Dynamic Action -> Advanced -> Event: Page Load -> Action: Excecute JS Code

$(this.affectedElements) //when you choose Selection Type: Item(s) (by HTML button)
or
$('#custom') //with known id
  .attr('onclick','')
  .click(function(){alert('my event')})


This can, of course, be splitted into OnLoad action (disable the click first) and then another one to add a custom event.
All roads lead to Rome.