Oracle Server - Selecting from dynamic performance views in PL/SQL

Author

Selecting from dynamic performance views in PL/SQL

Andreas Sheriff

2005-07-27, 8:23 pm

Database Version: 8.1.5.0.0

Granted SELECT_CATALOG_ROLE to user.
07_DICTIONARY_ACCESS
IBLITY is default

SQL> select * from v$instance;

Works.

SQL> DECLARE
2 v_startup_time DATE;
3 BEGIN
4 /* First, see if the database was restarted and store the new startup
value
5 if it were */
6 select startup_time into v_startup_time from v$instance;
7 END;
8 /

PL/SQL procedure successfully completed.

Works.

SQL> create or replace procedure os_collectstats
2 AS
3 v_startup_time DATE;
4 BEGIN
5 /* First, see if the database was restarted and store the new startup
value
6 if it were */
7 select startup_time into v_startup_time from v$instance;
8 END;
9 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE OS_COLLECTSTATS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/5 PL/SQL: SQL Statement ignored
7/50 PLS-00201: identifier 'SYS.V_$INSTANCE' must be declared

Yields said errors.

I've tried putting v$instance in quotes ("V$INSTANCE"), but got the same
error.

Anyone know why this CREATE PROCEDURE didn't work?
Did I forget something?

I don't have access to metalink, but am actively searching the web for an
answer. Nothing so far.

--
Andreas
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer

"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding if you don't eat your meat?!?!"
---

WARNING:
DO NOT REPLY TO THIS EMAIL
Reply to me only on this newsgroup

Maxim Demenko

2005-07-27, 8:23 pm

Andreas Sheriff schrieb:
> Database Version: 8.1.5.0.0
>
> Granted SELECT_CATALOG_ROLE to user.
> 07_DICTIONARY_ACCESS
IBLITY is default
>
> SQL> select * from v$instance;
>
> Works.
>
> SQL> DECLARE
> 2 v_startup_time DATE;
> 3 BEGIN
> 4 /* First, see if the database was restarted and store the new startup
> value
> 5 if it were */
> 6 select startup_time into v_startup_time from v$instance;
> 7 END;
> 8 /
>
> PL/SQL procedure successfully completed.
>
> Works.
>
> SQL> create or replace procedure os_collectstats
> 2 AS
> 3 v_startup_time DATE;
> 4 BEGIN
> 5 /* First, see if the database was restarted and store the new startup
> value
> 6 if it were */
> 7 select startup_time into v_startup_time from v$instance;
> 8 END;
> 9 /
>
> Warning: Procedure created with compilation errors.
>
> SQL> show errors
> Errors for PROCEDURE OS_COLLECTSTATS:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 7/5 PL/SQL: SQL Statement ignored
> 7/50 PLS-00201: identifier 'SYS.V_$INSTANCE' must be declared
>
> Yields said errors.
>
> I've tried putting v$instance in quotes ("V$INSTANCE"), but got the same
> error.
>
> Anyone know why this CREATE PROCEDURE didn't work?
> Did I forget something?
>
> I don't have access to metalink, but am actively searching the web for an
> answer. Nothing so far.
>

Requirement (for objects accessed) to be granted not via role applies
only to *named* plsql blocks ( also procedures,packages,
functions).
For anonymous block role is sufficient.

Best regards

Maxim

fitzjarrell@cox.net

2005-07-27, 8:23 pm

Andreas Sheriff wrote:
> Database Version: 8.1.5.0.0
>
> Granted SELECT_CATALOG_ROLE to user.
> 07_DICTIONARY_ACCESS
IBLITY is default
>
> SQL> select * from v$instance;
>
> Works.
>
> SQL> DECLARE
> 2 v_startup_time DATE;
> 3 BEGIN
> 4 /* First, see if the database was restarted and store the new startup
> value
> 5 if it were */
> 6 select startup_time into v_startup_time from v$instance;
> 7 END;
> 8 /
>
> PL/SQL procedure successfully completed.
>
> Works.
>
> SQL> create or replace procedure os_collectstats
> 2 AS
> 3 v_startup_time DATE;
> 4 BEGIN
> 5 /* First, see if the database was restarted and store the new startup
> value
> 6 if it were */
> 7 select startup_time into v_startup_time from v$instance;
> 8 END;
> 9 /
>
> Warning: Procedure created with compilation errors.
>
> SQL> show errors
> Errors for PROCEDURE OS_COLLECTSTATS:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 7/5 PL/SQL: SQL Statement ignored
> 7/50 PLS-00201: identifier 'SYS.V_$INSTANCE' must be declared
>
> Yields said errors.
>
> I've tried putting v$instance in quotes ("V$INSTANCE"), but got the same
> error.
>
> Anyone know why this CREATE PROCEDURE didn't work?
> Did I forget something?
>
> I don't have access to metalink, but am actively searching the web for an
> answer. Nothing so far.
>
> --
> Andreas
> Oracle 9i Certified Professional
> Oracle 10g Certified Professional
> Oracle 9i Certified PL/SQL Developer
>
>
> "If you don't eat your meat, you cannot have any pudding.
> "How can you have any pudding if you don't eat your meat?!?!"
> ---
>
> WARNING:
> DO NOT REPLY TO THIS EMAIL
> Reply to me only on this newsgroup

Anonymous PL/SQL blocks can access permissions granted through roles;
procedures, triggers and functions cannot. When you converted your
anonymous block to a procedure you effectively removed all permissions
granted to the user through roles.

Grant select on sys.v_$instance directly to the user, not through a
role. You'll find your procedure compiles without error.

David Fitzjarrell

Sponsored Links