2

PL/SQL function returning a SQL Query


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.

  1. Patrick Wolf says:

    Hi,

    that’s actually a feature which hasn’t been documented yet. We thought it’s very likely that someone might want to returned the SQL statement based on a function call.

    To simplify your code just do the following and APEX will take care of the rest:


    l_sql varchar2(32767) := ltrim(p_region.region_source);
    begin
    — if it’s not a SQL statement execute the PL/SQL function to get the SQL
    if upper(l_sql) not like ‘SELECT%’ and
    upper(l_sql) not like ‘WITH%’
    then
    l_sql := apex_plugin_util.get_plsql_function_result (
    p_plsql_function => l_sql );
    end if;

    l_row_set := apex_plugin_util.get_data2 (
    p_sql_statement => l_sql,

    BTW, I would suggest not to use the apex_application_util.get_sql_handler and subsequent APIs which are using the p_sql_handler. In most cases that makes the code just more complicated and you also have to take care of exception handling to close the cursor, …

    Regards
    Patrick

    • mnolan says:

      Thanks Patrick, for your advice, yet again :) I’m seem to be doing all my learning the hard way lately. I’ll update the post with your suggestion.