Re: procedure problem

Hi.

Try to use dba_role_privs instead of session_roles:

create or replace procedure count_it as  count_plus number;
begin
 select count(*) into count_plus
 from dba_role_privs
 where grantee=USER
 and granted_role = 'FAEMS_W_PMS21';
 IF count_plus = 0 THEN

      RAISE_APPLICATION_ERROR(-20999,'Faems user without authorization to perform this task over Usages '||count_plus);  END IF;
 end;
/

Roles are enabled (and you can use sesion_roles) in anonymous PL/SQL blocks, but not in a procedures. I suppose the reason is : when you execute procedure, oracle uses the system privileges of the owner and in that case: session privileges don't exist. For anonymous PL/SQL block, oracle is using privileges of the current user.
--
Tanya Injac
Oracle Developer/DBA
Unisys NZ Received on Tue May 12 1998 - 20:34:25 CDT