Re: Creating s stored function that can access v$session.

You need to explicitly (not via role!!!)

    GRANT SELECT ON SYS.V_$SESSION TO function-owner;

Solomon.Yakobson_at_entex.com

In article <32e4d45b.5137091_at_news.dial.pipex.com>,   david.woakes_at_dial.pipex.com (David Woakes) wrote:
>
> 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

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet