Re: Querying v$session from within a Stored Procedure

Rick Rimmer wrote:

> 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

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 Received on Fri Apr 18 2003 - 03:05:42 CEST