Re: Problems With Using V$SESSION Procedure

Salaam Yitbarek wrote:
> Hi,
>
> The following anonymous compiles:
>
> declare
> a v$session.SID%type;
> begin
> select min(SID) into a from v$session;
> dbms_output.put_line(a);
> end;
>
> However, take the above and put it in a stored procedure, and it does
> not compile, giving the error "PLS-00201: identifier 'V$SESSION' must
> be declared":
>
> create or replace procedure foo is
> a v$session.SID%type;
> begin
> select min(SID) into a from v$session;
> dbms_output.put_line(a);
> end;
>
> Now this problem occurs only with the V$ views, but not with other
> system tables or views such as ALL_ARGUMENTS, ALL_TABLES, etc.
>
> The above code was compiled under an account that has been assigned
> the DBA.
>
> Help?!
>
> Thanks,
> Salaam

Hi, your problem is because when you execute a stored procedure, the owner of the procedure must have DIRECTLY granted privileges on the objects referenced in the procedure. Privileges granted via a role e.g. select on v$session is granted via the SELECT_CATALOG_ROLE which is granted to the DBA role which is granted to your user will work for SQL but not for stored procedures. Grant select on the relevant tables directly to the user concerned an the procedures will be fine.

Have a look at the "PL/SQL User's Guide and Reference" in the Oracle manuals.

Regards

John S Findlay Received on Mon Aug 12 2002 - 13:14:56 CDT