Friday, December 16, 2011

RDS, The Plugin
Region 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.

8 comments:

  1. I'm getting
    ORA-06550: line 1, column 71: PLS-00302: component 'IS_COMPONENT_USED' must be declared ORA-06550: line 1, column 51: PL/SQL: Statement ignored

    any idea how to get round this?

    ReplyDelete
  2. The function is part of the apex_plugin package, which is distributed with Apex 4.0 and above. The plugin requires at least this version. I assume that you are running an older version? It would be helpful to know which one.
    In the meantime you might check out Martin's solution - just replace funtion 'IS_COMPONENT_USED' with his f_apex_permission_flag and adjust the query respectively. Using f_apex_permission_flag there is no need for the "wrapper" apex_plugin_util.get_plsql_function_result - which is not avaiable in your Apex version anyway.

    Best,
    tereska

    ReplyDelete
  3. Hi,

    I never worked with plugins before.
    So, I followed your instructions, downloaded the plugin, created new plugins and imported the .SQL, used the same class as I was using for my other regions but didn't see anything. Is there anything else I am missing here?

    Thanks.

    ReplyDelete
  4. The plugin needs at least 2 regions with 'Region Display Selector' set to 'Yes'.
    Have you checked the demo page of the plugin? Here you will find very detailed instructions (with images) on how to use and work with this plugin.
    Please let me know if you still have any issues with getting it to work.

    Best,
    tereska

    ReplyDelete
  5. Hi Tereska,

    I downloaded, installed and used your plugin in an application hosted @ apex.oracle.com. It worked great and I was very happy with the result. However, when installed in my local apex instance, same example as before ( on oracle xe) I have the following error

    "ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind Error in PLSQL code raised during plug-in processing."

    It's of course something related to my environment but I do not know what, could you help me?

    Thanks,

    ReplyDelete
  6. Did you test it on 4.0? I does not seem to work.

    ReplyDelete
  7. Yes I got the same error but in 4.1.

    "ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind Error in PLSQL code raised during plug-in processing."

    ReplyDelete
  8. Hello Anonymouses,

    I developed and tested the plugin on XE (11.2.0.2.0) with Apex 4.0 (4.0.2.00.08).

    Would you mind sendimg me an email? My address is in the readme.txt (plugin docu).
    Since I cannot reproduce your error, I rely on your feedback.
    I have changed the plugin and would like to send you an updated file. I'd prefer not to publish this new version for now as I don't know if it will solve the problem.

    Thank you,
    tereska

    ReplyDelete