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@theleme.com>

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 invalid

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

Please Help !!!!!

Yann. Received on Wed Nov 03 1999 - 07:50:30 CST