Re: procedure creation problem

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 8 Jun 2001 22:07:43 +0200
Message-ID: <ti2c0j2dn38b7@beta-news.demon.nl>

"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 are 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 the procedure.

Regards,

Sybrand Bakker, Oracle DBA Received on Fri Jun 08 2001 - 15:07:43 CDT