Today I’m releasing the RC1 (Release Candidate 1) version of my commercial File Upload Item Plugin. The plugin supports the multiple selection and uploading of files from a single file browse with the addition of drag & drop support for browsers that support HTML5. It also provides backwards compatibility for non HTML5 browsers by allowing multiple files to be uploaded one by one without submitting the page. The plugin is compatible with modplsql & the PLSQL gateway.
The plugin supports 4 events which can be used in combination with dynamic actions e.g. refreshing a report once a file upload is complete. Here’s a recorded demonstration of the plugin in action.
Finally I’m making a obfuscated RC1 version available for download and testing from apex-plugin.com as I’m hoping to gather some useful feedback before the final product release to accommodate any tweaks or enhancement requests. So please download and test it out for me if it’s something you think you might use in the future.
Please Note: using the plugin in production will require the purchase of an appropriate license.
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.