Selecting from dynamic performance views in PL/SQL

From: Andreas Sheriff <spamcontrol_at_iion.com>
Date: Wed, 27 Jul 2005 14:53:12 -0700
Message-ID: <8nTFe.26973$bp.7870@fed1read03>

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 

Received on Wed Jul 27 2005 - 16:53:12 CDT