Creating s stored function that can access v$session.

From: David Woakes <david.woakes_at_dial.pipex.com>
Date: 1997/01/20
Message-ID: <32e4d45b.5137091@news.dial.pipex.com>#1/1

I've been trying to create a stored function which accesses the v$session table and failing. I attach a sample of what I've been trying to achieve.

First a piece of PL/SQL that reads from the table happily enough.

declare

   cursor prg is

      select program
         from v$session
         where audsid = userenv('SESSIONID');

   my_prog varchar2(48);
begin

   open prg;
   fetch prg into my_prog;
   dbms_output.put_line(my_prog);
end;

and produces the output:

sqlplus_at_geuks250 (TNS interface)

Next an attempt to create a function to do the same:

create or replace function get_prog return varchar2 is

   cursor prg is

      select program
         from v$session
         where audsid = userenv('SESSIONID');

   my_prog varchar2(48);
begin

   open prg;
   fetch prg into my_prog;
   return my_prog;
 end;
 /

When run this gives the following:

Warning: Function created with compilation errors.

Which were:

     LINE POSITION
--------- ---------
TEXT

---

        4 15
PLS-00201: identifier 'SYS.V_$SESSION' must be declared

        3 7
PL/SQL: SQL Statement ignored

        3 14
PLS-00320: the declaration of the type of this expression is incomplete
or malformed

        9 4
PL/SQL: SQL Statement ignored

Am I missing something basic ? Obviously both bits of PL/SQL were executed in the same user.

The database version is 7.2.3 and the PL/SQL version is 2.2.3

David Woakes Received on Mon Jan 20 1997 - 00:00:00 CST