Querying v$session from within a Stored Procedure

[Rick Rimmer]

I am writing a stored procedure with the following code within it:

select v.osuser,
v.machine
into sOSUser,
sMachine
from v$session v
where v.audsid = userenv('sessionid');

and receive the following error when compiling:

PL/SQL: ORA-00942: table or view does not exist .

If I execute the SELECT statement in SQL*Plus (without the INTO clause) it
works fine. I also tried the code in a function and within a package with
the same result. Does anyone know what the problem could be?

I tried this on our Windows NT 8.1.7 and Windows NT 9.2 servers with the
same outcome.

Thanks in advance,
Rick Rimmer

[Daniel Morgan]

Rick Rimmer wrote:

The privilege you have to select from v_$session was granted through a role.
That works Ok in SQL*Plus but is not sufficient when the statement is in a
procedure or function where the privilege must be explicitly granted to the
schema.

Have your DBA grant SELECT to you directly.

Daniel Morgan