Selecting from dynamic performance views in PL/SQL

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


.

Relevant Pages