Re: Problems With Using V$SESSION Procedure

In article <77439c33.0208120948.6e44589f_at_posting.google.com>, yitbsal_at_yahoo.com wrote ...
> Hi,
>

<SNIP>
>
> 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;
>

<SNIP>

You are hitting a very often hit 'problem'.

ROLES are not used in PL/SQL but they are in SQL and/or annonymous blocks. So your anonnymous block worked fine because the dba role has privs to access V$Session etc.

When you compiled it into a proc, you are using PL/SQL and roles are turned off (effectively).

So, login as SYS qand grant select (or whatever) on v_$SESSION to the dba user. Then try again.

Check out the Oracle Co-operative FAQ at http://www.jlcomp.demon.co.uk/faq/plsql_privs.html for more details.

regards,
Norman (at home)

-- 

Remove MSSQL to reply :o)