Re: procedure creation problem

 So when I see a PLS-00201 error I need to be thinking of grants, right?

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 6/8/01, 4:07:43 PM, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrot e
regarding Re: procedure creation problem:

> "Peter Shankey" <shankeyp_at_its.charlestoncounty.org> wrote in message
> news:20010608.18402900_at_wando.charlestoncounty.org...
> When I try to create a procedure
 

> set serveroutput on
 

> create or replace procedure AddCrrnt AS
> v username varchar2(30);
> v sid number;
> v serial# number;
> CURSOR c vsession IS
> select USERNAME,SID,SERIAL# from v$session
> where USERNAME is NOT NULL;
> BEGIN
> OPEN c vsession;
> LOOP
> FETCH c vsession INTO v username, v sid, v serial#;
> EXIT WHEN c vsession%NOTFOUND;
> END LOOP;
> END AddCrrnt;
> /

> the show errors gives me:
 

> 11/12 PLS-00320: the declaration of the type of this expression is
> incomplete or malformed
 

> 11/38 PLS-00201: identifier 'SYS.V $SESSION' must be declared

> How can I declare a cursor on v$session and how do I declare it.
 

> thanks
> pete

> This question must have been answered at least a 1000 of times.
> You have access to v$ session (v$session is a synonym) by means of a
 role.
> Roles are ignored during compilation of stored procedures, as roles ar
 e
> volatile.
> Either you need to create the procedure with authid invoker (8i and
 higher)
> or you need a direct grant select on v$ session to the user creating t
 he
> procedure.
 

> Regards,
 

> Sybrand Bakker, Oracle DBA
Received on Sun Jun 10 2001 - 15:47:17 CDT