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) := '
  function x return boolean is 
  :out := x;
    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;
        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!

Jaydip Bosamiya


My photo
Jaydip Bosamiya
I am Oracle APEX Consultant, Blogger and Integrator. All-rounder in building small, medium and enterprise applications. Extensive knowledge in various area of web-driven applications in Back-end (PL/SQL, SQL, Java), Front-end (Oracle APEX, HTML, JavaScript, CSS, jQuery, OracleJET, ReactJS), RESTful APIs, Third-party library integrations (Apex Office Print (AOP), Payment Gateways, Syncfusion, HighCharts) and APEX Plugins (HighChart, StarRating)

Popular posts from this blog

Oracle APEX - Interactive Report - Scrollbars on Top

How to create your own customized nested report regions using jQuery

Oracle APEX - Warn unsaved changes for custom fields