How to Evaluate Oracle APEX Authorization Scheme using Execute Immediate

Here is something new and interesting work-around I found out while working on validating apex authorization scheme using my custom pl/sql function.

So, initially I used APEX_AUTHORIZATION.IS_AUTHORIZED function to evaluate my apex authorization scheme. But, I found a limitation with the use of is_authorized function. When my custom function refereed in SQL query it will end up with giving following error message.

ORA-14551: cannot perform a DML operation inside a query

Now, I still needs to evaluate apex authorization scheme but on my own way, without using is_authorized. Following is an self explanatory function I ended up writing for.

Please note that following block will only work for authorization type = "PL/SQL Function Returning Boolean", but it can be tweak around for other authorization type.

function validate_apex_auth(p_app_id in number, p_auth_scheme_name in varchar2) return number as
    l_return number := 0;
    l_result boolean;    
    l_plsql varchar2(4000) := '
declare
  function x return boolean is 
  begin 
    #APEX_AUTH_SCHEME#
  end; 
begin 
  :out := x;
end;';
  begin
    l_return := 0;
    for i in(select * from apex_application_authorization 
              where application_id = p_app_id
                and lower(trim(authorization_scheme_name)) = lower(trim(p_auth_scheme_name))
                and scheme_type_code = 'NATIVE_FUNCTION_BODY') loop
      
      l_expression := i.attribute_01;
      
      execute immediate replace(l_plsql, '#APEX_AUTH_SCHEME#', l_expression) using out l_result;
      
      if l_result then
        l_return := 1;
      else
        l_return := 0;
      end if;
    end loop;
    return l_return;
exception when others then
    l_return := 0;
    return l_return;

end validate_apex_auth;

Hope this helps!

Regards,
Jaydip Bosamiya