Re: PLS-00201 identifier 'SYS.V_$SESSION' must be declared

On Fri, 11 Jan 2002 16:53:00 -0500, "Joan M. Wohleber" <joanwohl_at_hotmail.com> wrote:

>I cannot compile a procedure that selects from v$session. I am getting
>error PLS-00201 identifier 'SYS.V_$SESSION' must be declared. V$SESSION is
>a valid synonym for SYS.V_$SESSION. I can select from V$SESSION in SQL
>Plus, but not from within a procedure. Supposedly, my role has select
>permission on ALL tables.
>1) How can I verify my table permissions?
>2) What else could be causing this error?
>
>If I use the EXECUTE IMMEDIATE statement to run dynamic sql (see below) that
>selects from SYS.V_$SESSION, the procedure compiles, but I receive error
>message ORA-00942: table or view does not exist.
>
>CREATE OR REPLACE PROCEDURE jsession (p_in IN NUMBER, p_out OUT VARCHAR2)
>IS
> v_sql VARCHAR2(2000);
>BEGIN
> v_sql := 'SELECT username FROM sys.v_$session WHERE sid = ' || p_in;
> EXECUTE IMMEDIATE v_sql INTO p_out;
>END;
>/
>Thanks for any assistance. JW
>

Supposedly, my *role* has select
permission on ALL tables.

The answer on this has been posted and nauseam. Here it is again

http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html

Evidently no one searches the archives at http://groups.google.com before posting.
Also in this particular case a
select sys_context('USERENV','CURRENT_USER') into p_out
from dual;

is completely sufficient and you won't need v$session at all.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Jan 11 2002 - 16:23:36 CST