| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Creating s stored function that can access v$session.
You got the privileges to select V_$ tables through a role. You cannot use privileges obtained through a role in your PL/SQL functions, procedures and also for creating views. As these tables are owned by SYS you must connect to the SYS userid and grant privileges on these these tables as follows
GRANT SELECT ON V_$SESSION TO your_oracle_id ;
Remember that V$SESSION is a synonym for V_$SESSION.
-- Vijaya Kumar Vardhineni Certified Oracle DBA, EDS Plano, TX David Woakes <david.woakes_at_dial.pipex.com> wrote in article <32e4d45b.5137091_at_news.dial.pipex.com>...Received on Mon Jan 20 1997 - 00:00:00 CST
> 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
>
 |
 |