PLS-00201 identifier 'SYS.V_$SESSION' must be declared
Joan M. Wohleber
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
Sybrand Bakker
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
Peter Groen
"Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote in message
news:h2pu3uo76vgso1f48...@4ax.com...
Keith Boulton
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" <pos...@sybrandb.demon.nl> wrote in message
news:h2pu3uo76vgso1f48...@4ax.com...
Sybrand Bakker
<kbou...@ntlunspam-world.com> wrote:
>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".
>
Most people in the Oracle related newsgroup appear not to look up the
error message at all, but just post them.
Your changes to the PLS-00201 message are simply incorrect, as there
are various methods to avoid this problem, and the remark 'accessible
directly' may well not apply at all. Quite often it is far more
sensible to make sure the account owning the tables also own the
stored procedures, the notably exception being SYS. Typically you
would see PLS-00201 if you are working with a not well though out
Gordian myriad of tables and schemas or even databases. Then again,
the resolution 'accessible directly' is only valid in 8.0 and before,
and not valid anymore in 8i and above.
I don't agree with a general remark 'poor choice of error message
seems to apply throughout' Ever looked at Microsux error messages like
'The system is unstable'?
In the particular example you mention you can copy and paste the
affected statement to a sql*plus window and get the *exact* location
of the error.
I agree there are people who think that is asked too much of them. Of
course, there is always the option of addressing your concerns with
Oracle, or alternatively choose a database product which doesn't have
this problem.
Regards
Keith Boulton
> Most people in the Oracle related newsgroup appear not to look up the
> error message at all, but just post them.
On a different subject, my impression is that the general quality of
requests and responses to the oracle newgroups has risen consistently over
the years - thanks to all of you.
> Your changes to the PLS-00201 message are simply incorrect, as there
> are various methods to avoid this problem, and the remark 'accessible
> directly' may well not apply at all.
But what is the commonest cause? Although to be fair, I did look up the
error message and it does give the possible reason.
> Ever looked at Microsux error messages like
> 'The system is unstable'?
The fact that other vendors are as bad or worse is not really relevant. In
my experience, there is a general issue with error messages that they are
not based on the needs of the person who will receive the message, but
rather on the context of the programmer who coded the error message when it
was added.
> I don't agree with a general remark 'poor choice of error message
> seems to apply throughout'
But it does. In general, the error message is a simple text which doesn't
give the context required e.g.
ORA-00001 unique constraint (string.string) violated
At least we now get the constraint name, but why not give the columns and
values involved:
Unique constraint <constraint name> - <tablename>( <column>, <column>
... ) violated with values( <value>, <value>...)
ORA-00054 resource busy and acquire with NOWAIT specified
Which resource?
ORA-00210 cannot open the specified controlfile
which?
ORA-00902 invalid datatype
what?
ORA-00903 invalid table name
what invalid table name
ORA-00904 invalid column name
ORA-00918 column ambiguously defined
which one?
ORA-00932 inconsistent datatypes
which columns or literal values are involved? Especially given two other
reasons why this error may occur:
1. An attempt was made to perform an operation on a database object (such as
a table or view) that is not intended for normal use. For example, system
tables cannot be modified by a user. Note that on rare occasions this error
occurs because a misspelled object name matched a restricted object's name.
2 An attempt was made to use an undocumented view.
I could go on.
> In the particular example you mention you can copy and paste the
> affected statement to a sql*plus window and get the *exact* location
> of the error.
Which isn't helpful if the error has been trapped as an exception in a
pl/sql block containing more than one statement, and in any case, given a
reasonable error text I should not need to cut and paste code into other
environments.
> I agree there are people who think that is asked too much of them.
I agree, after all a simple "error occurred" is all that is really required.
People can work out the error from that.
> Of course, there is always the option of addressing your concerns with
> Oracle,
I would if I could.
> or alternatively choose a database product which doesn't have
> this problem.
Unfortunately, like most people, I have as much freedom to choose my
database as I do the desktop operating system I must work with.
And I suspect the others would be the same if only that it is clear that
quality doesn't sell.
Daniel A. Morgan
useful in the business. And given the on-line resources such as technet and
metalink easily understood and resolved in almost all cases. The last thing
Oracle needs to do is rewrite its error messages. The first thing it needs to do
is stop making its products available to people that don't or can't RTFM.
Daniel Morgan
Keith Boulton
value violoated a unique constraint.
"Daniel A. Morgan" <damo...@exesolutions.com> wrote in message
news:3C402732...@exesolutions.com...
Daniel A. Morgan
finding out which one takes only a second. Just wrap your insert statement into
a block with an exception handler. The RTFM was with respect to knowing what
went wrong.
BEGIN
Your Insert Statement Here
EXCEPTION
WHEN OTHERS THEN
INSERT into an error handling table that contains no constraints
END;
COMMIT;
What makes it into the error handling table is the records that caused a
problem. Also grab SQLERRM and put it into the table and you can be absolutely
sure why each record caused an error.
The concept here is defensive programming. Never assume that errors won't
happen. And always assume you will want to trap them. I would never write an
insert statement any other way.