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

On Sat, 12 Jan 2002 12:39:50 -0000, "Keith Boulton" <kboulton_at_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

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Jan 12 2002 - 08:49:38 CST