Roles are not granted in PL/SQL ???

From: Yann Chevriaux <chevriaux_at_theleme.com>
Date: Wed, 03 Nov 1999 14:50:30 +0100
Message-ID: <38203DA6.A961EA28_at_theleme.com>

Hi.
I've got a problem with roles.
[Quoted] [Quoted] I first develop an application with Forms 4.5 on Personnal Oracle 7.3.2.2.0
[Quoted] [Quoted] It works and there are several roles defined and dispatched over some users.

[Quoted] 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.

[Quoted] 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 invalid

SQL> [Quoted] [Quoted] Does it mean that privileges are not viewable trough roles when using in procedures, functions or packages ?
[Quoted] As it works with PO7, is there some parameter I could change ?

[Quoted] Please Help !!!!!

Yann. Received on Wed Nov 03 1999 - 14:50:30 CET