Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Roles are not granted in PL/SQL ???
Hi.
I've got a problem with roles.
I first develop an application with Forms 4.5 on Personnal Oracle
7.3.2.2.0
It works and there are several roles defined and dispatched over some
users.
Some days ago, this application has been transferred on our Oracle
Server:
(NT 4, Oracle 7.3.2.3.1).
And ... it doesn't work anymore.
In fact I discover that procedures, functions and packages can't be compilated !
The error occurs when I try to access objects for which I got privileges trough a role.
i.e.:
Assume I've been granted role DBA:
THAT WORKS:
SQL> select granted_role from dba_role_privs where grantee='DBA';
GRANTED_ROLE
THAT DOESN'T WORK:
SQL> create or replace procedure foo
2 is
3 begin
4 for c in (select granted_role from dba_role_privs where
grantee='TEST')
5 loop
6 dbms_output.put_line(c.granted_role);
7 end loop;
8 end;
9 /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE FOO:
LINE/COL ERROR
4/12 PL/SQL: SQL Statement ignored 4/37 PLS-00201: identifier 'SYS.DBA_ROLE_PRIVS' must be declared 6/4 PL/SQL: Statement ignored 6/25 PLS-00364: loop index variable 'C' use is invalidSQL> Does it mean that privileges are not viewable trough roles when using in procedures, functions or packages ?
Please Help !!!!!
Yann. Received on Wed Nov 03 1999 - 07:50:30 CST