Re: Access V$ Tables from stored procedure
Date: 1996/09/18
Message-ID: <51ppld$s0v_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <323D560A.3129_at_comdisco.com>, Roderick Prince <rrprince_at_comdisco.com> writes:
|> Attempting to define access the V$ dynamic tables like V$_SESSION table
|> from within a stored procedure leads an error when I attempt to store
|> the procedure. The error returned is PLS_00201:
|> identifier 'SYS.V$_SESSION' must be declared.
|>
|> The error message and resolution notes led me to modify the sql
|> statement to referenct V$Session, V$_SESSION, SYS.V$Session, and
|> SYS.V$_SESSION. Same message is generated.
|>
|> Any ideas on how I can access these tables?
I bet you have been granted access to these tables via a role (such as the DBA role). Roles are disabled inside stored procedures, thus the error. Try connecting as SYS, and GRANTing SELECT on v$session to the owner of the procedure directly. Or GRANT SELECT ANY TABLE to the user (assuming you want to give that user the system privilege of selecting from any database table).
|>
|> Thanks,
|> Roderick...
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Wed Sep 18 1996 - 00:00:00 CEST