Oracle APEX authorization scheme withPL/SQL function returning boolean

Question

I want to create a control access procedure, but I'm new with PL/SQL and I not understand the error message. If I create the function the compilation it's ok, but if I put the code in authorization schema, I have an error. I use scheme type : PL/SQL function returning boolean.

ORA-06550: line 58, column 26: PLS-00103: Encountered the symbol "" when expecting one of the following: begin function pragma procedure The symbol "begin was inserted before "" to continue. ORA-06550: line 60, column 21: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with

declare function F_ACCES_AUX_PAGES 
   return BOOLEAN 
is 
-- Indicateurs pages 
v_admn_pages tbl_admn_pages%rowtype; 
v_employes tbl_employes%rowtype; 
BEGIN 
-- Lire les indicateurs de la page 
select * 
  into v_admn_pages 
  from TBL_ADMN_PAGES 
 where PK_NUMERO_PAGE = v('APP_PAGE_ID'); 

select E.* 
  into v_employes 
  from TBL_EMPLOYES E, 
       USERS U 
 where E.PK_ID_EMPLOYE = U.FK_ID_EMPLOYE 
   and USER_NAME = v('APP_USER'); 
-- Vérifier si indicateurs U permettent l'accès. indU = OUI et indP = OUI 
if v_admn_pages.IND_ACC_GESTIONNAIRE = 'OUI' and 
   v_employes.IND_ACC_GESTIONNAIRE = 'OUI' then 
   RETURN TRUE; 
elsif v_admn_pages.IND_ACC_CONSULTANT = 'OUI' and 
   v_employes.IND_ACC_CONSULTANT = 'OUI'         then 
   RETURN TRUE; 
elsif v_admn_pages.IND_ACC_CLIENT = 'OUI' and 
      v_employes.IND_ACC_CLIENT = 'OUI' then 
RETURN TRUE; 
elsif v_admn_pages.IND_ACC_RH = 'OUI' and 
      v_employes.IND_ACC_RH = 'OUI' then 
RETURN TRUE; 
elsif v_admn_pages.IND_ACC_ADMIN_GPS = 'OUI' and 
      v_employes.IND_ACC_ADMIN_GPS = 'OUI'         then 
RETURN TRUE; 
elsif v_admn_pages.IND_ACC_ADMIN_GST = 'OUI' and 
      v_employes.IND_ACC_ADMIN_GST = 'OUI' then 
RETURN TRUE; 
elsif v_admn_pages.IND_ACC_ADMIN_GRH = 'OUI' and 
      v_employes.IND_ACC_ADMIN_GRH = 'OUI' then 
    RETURN TRUE; 
elsif v_admn_pages.IND_ACC_ADMIN_GDP = 'OUI' and 
      v_employes.IND_ACC_ADMIN_GDP = 'OUI' then 
    RETURN TRUE; 
elsif v_admn_pages.IND_ACC_ADMIN_GAC = 'OUI' and 
      v_employes.IND_ACC_ADMIN_GAC = 'OUI' then 
    RETURN TRUE; 
elsif v_admn_pages.IND_ACC_ADMIN_GDA = 'OUI' and 
      v_employes.IND_ACC_ADMIN_GDA = 'OUI' then 
    RETURN TRUE; 
elsif v_admn_pages.IND_ACC_ADMIN_GPS = 'OUI' and 
      v_employes.IND_ACC_ADMIN_GPS = 'OUI' then 
    RETURN TRUE; 
elsif v_admn_pages.IND_ACC_ADMIN_RDD = 'OUI' and 
      v_employes.IND_ACC_ADMIN_RDD = 'OUI' then 
    RETURN TRUE; 
elsif v_admn_pages.IND_ACC_ADMIN_GDE = 'OUI' and 
      v_employes.IND_ACC_ADMIN_GDE = 'OUI' then 
    RETURN TRUE; 
elsif v_admn_pages.IND_ACC_ADMIN_GPR = 'OUI' and
      v_employes.IND_ACC_ADMIN_GPR = 'OUI' then 
    RETURN TRUE; 
elsif v_admn_pages.IND_ACC_ADMIN_GSC = 'OUI' and 
      v_employes.IND_ACC_ADMIN_GSC = 'OUI' then 
    RETURN TRUE; 
else 
    RETURN FALSE; 
end if; 
END F_ACCES_AUX_PAGES;

Alex Poole Alex Poole · Accepted Answer · 2014-07-17T17:50:24

Your function declaration is for a local function in an anonymous block; but you then don't complete the block, hence it looking for BEGIN.

You should create the function as:

create or replace function F_ACCES_AUX_PAGES 
   return BOOLEAN 
is
-- Indicateurs pages 
v_admn_pages tbl_admn_pages%rowtype; 
v_employes tbl_employes%rowtype; 
BEGIN 
...
END F_ACCES_AUX_PAGES;
/

See the documentation for how to create stored PL/SQL programs.

Oracle APEX authorization scheme withPL/SQL function returning boolean

1 Answers