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.
( 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
                  ' 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);  

        'DECLARE l_sql VARCHAR2(32767); BEGIN' ||
          ( 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;
                       ' BEGIN :b1 := myfunc; END;'
     USING OUT l_source_sql;


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.


Combining item validations into one, without losing inline display

Page item validations, whilst very useful under normal circumstances can become a management nightmare if you have a large number of items defined on the page that require several validations per item. This is especially evident when you use repeating fields on the same page as per the below form design which I’m currently working on for a customer (in APEX 4.0).

Add to this: catering for conditional display of the items and conditional firing of the validations you’ve got yourself a big headache on your hands. Now I’m sure some of you are shaking your heads going, “No you can use a page level validation a write a single validation” instead of defining 3 or 4 validations per item for all items. The trade off though is not being able to associate your page level validation with an item. Now when you have 40+ items on a page and no highlighting of the actual item which raised the error, rest assured your end user is going to explode in a rage after submitting the page for the tenth time still wondering what the problem field is.

Now I’m not the first to complain about validation support, to be honest in their current form they are still quite usable, but as in life there is always room for improvement. Many have previously requested validation groups, dependencies etc. etc. I’d second those and perhaps also suggest an array page item, similar to how a tabular form field operates, we would then only need one item definition and one validation for a repeating field such as a number of dates as in the above screenshot.

Now for those of you, like myself, who can’t wait for enhancements… we can work around this issue with a little PLSQL and using an internal undocumented procedure ADD_VALIDATION_ERROR in APEX_APPLICATION (synonym translated to WWV_FLOW).

-- ---------------------------------------------------------------------------
-- Procedure      : log_validation_failure
-- Author         : Matt Nolan
-- Private/Public : Public
-- Description    : Wrapper for logging validation failure in APEX engine
-- ---------------------------------------------------------------------------
-- Revision History
-- Date            Author       Reason for Change
-- ---------------------------------------------------------------------------
-- 02 FEB 2011     M.Nolan      Created.
-- ---------------------------------------------------------------------------
PROCEDURE log_validation_failure
( p_item      IN VARCHAR2
, p_message   IN VARCHAR2


  ( p_message                => p_message
  , p_error_display_location => p_location
  , p_page_item_name         => p_item

  IF apex_application.g_debug THEN
    apex_debug_message.log_message('AF Custom Item Validation');
    apex_debug_message.log_message('...Item: '||p_item);
    apex_debug_message.log_message('...Message: '||p_message);
    apex_debug_message.log_message('...Location: '||p_location);

    --logger.log_error('log_validation_failure','Item: '||p_item||' '||SQLERRM);
END log_validation_failure;

What the above will do is add a validation error the “stack”, so it can be combined with existing standard item validations. Also when calling the above procedure I normally have my items in an array of records which I loop through and perform the custom validations which I have designed. Here’s an idea of the concept e.g.

FOR i IN 1.. page_items.count LOOP
  IF i < page_items.count THEN
    IF NOT validate_dates(v(page_items(i).item_name),v(page_items(i+1).item_name)) THEN
      ( p_item      => l_item_name
      , p_message   => 'must be < Day '||to_number(page_items(i).seq+1)
    END IF;

  IF NOT is_asset_available
        ( p_course_id         => p_course_id
        , p_date              => p_date
        , p_start             => p_date
        , p_finish            => p_date
        , p_no_students       => p_no_students
        , p_ins_server        => p_ins_server
        , p_no_students_srvr  => p_no_students_srvr
        , p_virt_classroom    => p_virtual_classroom
        , p_out_message       => l_msg
    ( p_item      => l_item_name
    , p_message   => l_msg

I then create a single validation on the page with the type “Function returning Boolean” that has my custom validation function call and always returns “TRUE”. It is a very key point that our actual validation never fails as we actually populate the errors ourselves within the function call. The validation we define on the APEX page is merely a wrapper around the group of real validations which we’ve created in our custom PLSQL.

In addition to the above you may also want to check out an older post I wrote about updating the REQUEST value within your custom PLSQL. This can give you more control as to what validations actually fire when using the built-in condition in combination with the above technique and standard validations.

The end result is a single group validation with inline item error display which translates into reduced maintenance, more control, and de-cluttering of the page in the IDE (well in the old view). The point to note is that using this method may mean that the validations you define may potentially break when upgrading to future releases of APEX. That is if the development team change the current validation design. For me this is something I can live with as the benefits outweigh the risks. However it is something for you to note if you intend to follow this post. Note: if you read the comment posted by Patrick Wolf, you will see that using this method will be fine at least for the 4.1 release. The original code I posted populated the validation arrays directly, however I have ammended it to use the procedure “add_validation_error” after Patricks suggestion.

On the topic of validations I must thank Guido Zaleen for the Form Validator plugin which he posted on APEX-PLUGIN.com. This works extremely well and is very thorough in design. I love the fact that it even takes jQuery UI tabs on the page into account and will automatically tab-switch if the error is on a hidden tab at page render. The code is also easy enough to follow and add additional custom validations as well. If you’re looking for client side and AJAX validations don’t write your own, this plugin is perfect. Great work Guido.


APEX Plugin – jQuery Menu

I’m releasing my first publicly available region plugin. It’s based on the jQuery Menu produced by the Filament Group. You can create a menu based on existing APEX lists, attach a menu to a breadcrumb or your page tabs, or create a menu based on the contents of a custom table.

It comes with 10 configurable options and requires the definition of a single SQL query for the menu contents in the form of:

,      pid
,      text
,      url
,      display_seq

Here’s an example based on an existing APEX list (my recommendation) with authorisation and condition support.

SELECT list_entry_id         id
,      list_entry_parent_id  pid
,      entry_text            text
,      entry_target          url
,      display_sequence      display_seq
FROM   apex_application_list_entries
WHERE  application_id       = :APP_ID
AND    list_name IN ('[List Name(s)]')
AND    auth_condition_check
       ( p_condition_type        => condition_type
       , p_condition_expression1 => condition_expression1
       , p_condition_expression2 => condition_expression2
       , p_authorization_scheme  => authorization_scheme
       ) = 0

Here’s the link to the demo, documentation, and download.

And here’s a YouTube demo of it in action….

Pages ... 1 2 3 4 5 6 7 8 9