Re: PLS-00201 identifier 'SYS.V_$SESSION' must be declared

To be fair, I was struck recently by just how bad the error messages are.

For instance, in this case, it would appear to be trivial to modify the message to something like:

PLS-00201 identifier 'SYS.V_$SESSION' must be declared and must be accessible directly, not via a role.
I suspect the explanation given in the error message manual is not clear.

This same poor choice of error message seems to apply throughout. For instance, "table or view does not exist" - what the hell is wrong with "table or view <table name> does not exist".

I suppose sometime in the next century, we'll get a reasonable set of error messages. After all, it took only 20 years to get the column name in "mandatory column is null or missing".

What makes it worse is that if you have an exception handler in pl/sql that logs an error and then re-raises the error, the error is reported as being in the exception handler, rather than where it actually occurred.

"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 Sat Jan 12 2002 - 06:39:50 CST