Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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)Received on Mon Aug 12 2002 - 13:34:25 CDT