Grin ;-)
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:h2pu3uo76vgso1f48k58o3hc9aqsbiqe34_at_4ax.com...
> On Fri, 11 Jan 2002 16:53:00 -0500, "Joan M. Wohleber"
> <joanwohl_at_hotmail.com> wrote:
>
> >I cannot compile a procedure that selects from v$session. I am getting
> >error PLS-00201 identifier 'SYS.V_$SESSION' must be declared. V$SESSION
is
> >a valid synonym for SYS.V_$SESSION. I can select from V$SESSION in SQL
> >Plus, but not from within a procedure. Supposedly, my role has select
> >permission on ALL tables.
> >1) How can I verify my table permissions?
> >2) What else could be causing this error?
> >
> >If I use the EXECUTE IMMEDIATE statement to run dynamic sql (see below)
that
> >selects from SYS.V_$SESSION, the procedure compiles, but I receive error
> >message ORA-00942: table or view does not exist.
> >
> >CREATE OR REPLACE PROCEDURE jsession (p_in IN NUMBER, p_out OUT VARCHAR2)
> >IS
> > v_sql VARCHAR2(2000);
> >BEGIN
> > v_sql := 'SELECT username FROM sys.v_$session WHERE sid = ' || p_in;
> > EXECUTE IMMEDIATE v_sql INTO p_out;
> >END;
> >/
> >Thanks for any assistance. JW
> >
>
>
> Supposedly, my *role* has select
> permission on ALL tables.
>
> The answer on this has been posted and nauseam.
> Here it is again
>
> http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html
>
> Evidently no one searches the archives at http://groups.google.com
> before posting.
> Also in this particular case a
> select sys_context('USERENV','CURRENT_USER')
> into p_out
> from dual;
>
> is completely sufficient and you won't need v$session at all.
>
> Hth
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Fri Jan 11 2002 - 18:23:46 CST
