This is a sneak peek at a new Ext JS tree plugin which I’ve developed. I’m in the final stages of packaging it and producing the demo’s and documentation so it will be available for download on apex-plugin.com very soon.
So what features does the plugin offer?
- AJAX enabled with the choice of loading the entire tree in one call or on node by node expanding
- Search: Dynamic filtering of the tree contents
- Expand & Collapse buttons
- Drag & Drop Reordering Support
- Tree Label Editing
- Checkbox Nodes with state saving using an existing APEX item
- Context Menu Support (from SQL Query)
- Cookie based state saving for expanded nodes.
- Order by “Display Sequence” support
- Fully integrated into dynamic actions. Bind click, drag & drop, edit complete, expand, collapse, context menu selection etc. events
- In it’s basic form opens URL targets
- Tree config is fully customizable
- Support for node by node custom config, e.g. enableDrag = false
- Full support for replacing substitution strings (label, help text, url, etc)
- Custom icon support
- Help text support
- Supports unlimited tree’s on a single page
The plugin takes the following list of 10 parameters:
With the event support you can create dynamic actions to process tree events. This means that it’s simple to refresh reports, perform AJAX callbacks to process the reordering of nodes, or label edits, or filtering of reports based on check box selections.
The hidden gem is the context menu. The context menu can be generated just like the tree in an object hierarchy from a single SQL statement. This means that you can have multi navigational capabilities, or filtering. Here’s one example of using a date picker within the context menu….
and if you’re using 11g and you need a complex context menu with datepicker, radio items, combo support, you can define your context menu query based on a table/view which has a parameter and value column like this…
SELECT id menu_id , pid menu_pid , display_seq display_seq , LISTAGG('<'||parameter||'>'||parameter_value||'</'||parameter||'>', ' ') WITHIN GROUP (ORDER BY parameter) xmlconfig FROM af#tree_context_menu GROUP BY id , pid , display_seq
Your parameter values can be (sub)JSON objects I’ll take care of turning the entire resultset into a JSON object hieararchy with the aid of “dbms_xmlgen.newcontextfromhierarchy” and XSLT.
P.S. This is just the first Ext plugin in the series, with more coming soon.
Do region plugins support defining the plugin SQL source as a “PL/SQL function returning a SQL Query”? You might be wondering why I’m asking the question. The reason is: you can define a function body returning a string and not receive a parsing error, however if you try and execute it using the following you will get an error.
l_sql_handler := apex_plugin_util.get_sql_handler ( p_sql_statement => l_source_sql , p_min_columns => 2 , p_max_columns => 100 , p_component_name => NULL ); -- -- Now prepare the query. This is where all the binding is done. -- apex_plugin_util.prepare_query ( p_sql_handler => l_sql_handler ) ; -- -- We're use get_data2 to ensure the data comes back in their natural data types -- l_row_set := apex_plugin_util.get_data2 ( p_sql_handler => l_sql_handler ) ;
The package code apex_plugin_util does not recognize that your region source is a PLSQL function and treats it as a normal query. I’m pretty sure this has occurred because the plugin wizard reuses the same SQL validation routine used for the creation of report regions. I’m guesstimating purely because report regions allow you to define queries via a “PL/SQL function returning a SQL Query”.
Now am I complaining about this? No way, I’m over the moon about this
possible oversight/unimplemented feature. This is because I’m going to take full advantage as sometimes I need to dynamically build my SQL queries for complex solutions. So in order to support this in my plugin code all I need to do is execute the function before I call the plugin API handlers to execute my returned query string.
Which brings me to the actual reason for writing this post (I’m hoping to get some feedback on this). How does one actually use EXECUTE IMMEDATE to call an anonymous function since only anonymous procedures are supported? I did a bit of Googling and found a few interesting posts but nothing concrete that would guide me with an exact example. Since I’m paid by the hour, it’s hard to justify spending half a day trying to find the best solution, so I just had to come up with something on the spot. So here’s my attempt (which does work) but I’m more than happy for anyone to point out a better solution. UPDATE: Patrick Wolf has enlightened me with a much simpler method, read the comments on this post.
Note: the following assumes you define your “PL/SQL function returning a SQL Query” within a BEGIN and END block.
-- -- Replace binds with V('ITEM') thanks to Andy Tulley -- l_source_sql := regexp_replace(l_source_sql,':([a-zA-Z0-9_]*)','v(''\1'')'); -- -- We'll remove the DECLARE reserved word if it's defined and -- create an inline function within an anonymous procedure which -- we can then call -- EXECUTE IMMEDIATE 'DECLARE FUNCTION myfunc RETURN VARCHAR2 AS '|| regexp_replace(l_source_sql,'DECLARE','',1,0,'i')|| ' BEGIN :b1 := myfunc; END;' USING OUT l_source_sql;
Here’s an improved version of the above (but more complicated and harder to read) which uses binds rather than replacing them with v(‘ITEM’) in the query. This is for performance reasons and in case you have a colon in your query which is not a bind variable e.g. APEX URL. You can use either or…
IF regexp_instr(l_source_sql,'(declare)?.*begin.*end;',1,1,1,'in') > 0 THEN ---------------------------------------------------------------------------- -- Get bind variables. ---------------------------------------------------------------------------- l_binds := wwv_flow_utilities.get_using_clause(l_source_sql); IF l_binds IS NOT NULL THEN EXECUTE IMMEDIATE 'DECLARE l_sql VARCHAR2(32767); BEGIN' || ' EXECUTE IMMEDIATE ''DECLARE FUNCTION myfunc RETURN VARCHAR2 AS ' || REPLACE ( regexp_replace ( l_source_sql , '('||CHR(13)||'|'||CHR(10)||'|DECLARE)' ,' ',1,0,'in' ) , '''' , '''''' ) ||' BEGIN :b1 := myfunc; END;'''|| ' ' || l_binds || ', OUT l_sql;'|| ' :b1 := l_sql; END;' USING OUT l_source_sql; ELSE EXECUTE IMMEDIATE 'DECLARE FUNCTION myfunc RETURN VARCHAR2 AS '|| regexp_replace(l_source_sql,'DECLARE','',1,0,'i')|| ' BEGIN :b1 := myfunc; END;' USING OUT l_source_sql; END IF; END IF;
Now I am hoping that on the odd chance the APEX development team stumble across this blog they don’t remove this behaviour in a future release. On the other hand I’d be more than happy if they support this feature within the native plugin API which will mean one less step that we need to do. The bright side is that there is some extra flexibility in the plugin design , intentional or not.