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

Home -> Community -> Usenet -> c.d.o.misc -> Re: v$database

Re: v$database

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 08 Aug 2002 01:09:30 +0200
Message-ID: <5r93lu4dbjgogfmkdfbs0cfl3elg0ljgeo@4ax.com>


On Wed, 07 Aug 2002 17:35:31 -0500, jhking <jhking_at_airmail.net> wrote:

>I need to know which instance I'm running in:
>from sql*plus
>select name from v$database ;
>NAME
>---------
>HTD1
>
>-- good, just want I want, put it in a function so I don't have the
>select all over the place.
>
>SQL> create or replace function whereami
> 2 return varchar2
> 3 is
> 4 inst v$database.name%type ;
> 5 begin
> 6 select name
> 7 into inst
> 8 from v$database ;
> 9 return inst ;
> 10 end ;
> 11 /
>
>Warning: Function created with compilation errors.
>
>SQL> show errors
>Errors for FUNCTION WHEREAMI:
>
>LINE/COL ERROR
>-------- -----------------------------------------------------------------
>4/9 PLS-00201: identifier 'SYS.V_$DATABASE' must be declared
>4/9 PL/SQL: Item ignored
>6/5 PL/SQL: SQL Statement ignored
>8/12 PLS-00201: identifier 'SYS.V_$DATABASE' must be declared
>9/5 PL/SQL: Statement ignored
>9/12 PLS-00320: the declaration of the type of this expression is
> incomplete or malformed
>
>How can I get this information from inside a function/procedure/package?
>
>
>8.1.7.0.0 database on Solaris.

This is the most asked question about pl/sql. One could question why this group keeps archives: you only need to search on PLS-201 and you will get the answer. Nothing more, nothing less, you have answered your own question and saved bandwith and avoided wasting someones spare free time.
Anyway: the hint for today is that roles are disabled during compilation of stored procedures. You have access through the select_catalog_role. You need direct access (8.0 and before) or create procedure .... authid invoker is
etc (8i and higher)
This is the last time I answer this question. I will leave the burden of answering it over and over again to someone else now.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Aug 07 2002 - 18:09:30 CDT

Original text of this message

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