Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Selecting from dynamic performance views in PL/SQL

Re: Selecting from dynamic performance views in PL/SQL

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 28 Jul 2005 00:07:57 +0200
Message-ID: <dc90jl$9gs$03$1@news.t-online.com>


Andreas Sheriff schrieb:
> 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.
>

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 Received on Wed Jul 27 2005 - 17:07:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US