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

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.
> .... SNIP ....
> 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.
> ...... SNIP ....
> 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

You are another victim of Oracle's misleding error messages!

Your true problem is that you have only been granted access to V_$SESSION via role (probably DBA role) and not explicitly. Let SYS grant you select on V_$SESSION explicitly and your function will compile without errors.

With the above said it is obvious why anonymous PL/SQL block executed without complains (access granted via role was enough in this case) and why implementing the same code as stored function was unsuccesfully (access must be granted explicitly).

And, just as a precaution: declare your variable "my_prog" as varchar2(64) instead of varchar2(48) - at least in my version 7.2 v$session.program is declared as varchar(64). Or even better, declare it as "my_prog v$session.program%TYPE".

Othervise you might get "ORA-06502: PL/SQL: numeric or value error" when executing your function.

Regards, Jure

-- 
 ===============================================================
 ! Jurij Modic                            Republic of Slovenia !
 !  tel: +386 61 178 55 14                Ministry of Finance  !
 !  fax: +386 61  21 45 84                Zupanciceva 3        !
 !  e-mail: jurij.modic_at_mf.sigov.mail.si  Ljubljana 1000       !
 ===============================================================