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.