Re: Selecting from dynamic performance views in PL/SQL

Andreas Sheriff wrote:
> Database Version: 8.1.5.0.0
>
> Granted SELECT_CATALOG_ROLE to user.
> 07_DICTIONARY_ACCESSIBLITY 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 Received on Wed Jul 27 2005 - 17:12:31 CDT