v_$session in 9.2

From: Boris Nikolaev <ierdna_at_freenet.de>
Date: Thu, 9 Dec 2004 14:17:53 +0100
Message-ID: <31r1k2F3djsd1U1@individual.net>

Hello, All!

I have a problem on using v_$session and other synonims in ORACLE 9.2. This procedure works in 8i:

create or replace function current_process return varchar2 is   Result varchar2(64);
begin
 declare

    my_sid number;
 begin
  result := NULL;
  SELECT SID into my_sid from v_$mystat where rownum=1;   SELECT PROGRAM INTO result from V_$SESSION where SID=my_sid;   if result is NULL then
   SELECT MODULE INTO result from V_$SESSION where SID=my_sid;   end if;
  return(Result);
 exception
   when others then NULL;
 end;
end current_process;
/

for v_$mystat and v_$session a have granted select as user SYS:

grant select on v_$mystat to public;
grant select on v_$session to public;

The procedure current_process is used in any triggers and it works in 8i. Now I want to migrate to 9.2.

Step 1: I can't compile the procedure ??? Step 2: grant select on v_$mystat to <user>;

            grant select on v_$session to <user>;
            grant select any dictionary to <user>; -> the same result

Step 3: I Change v_$mystat to v$mystat , v_$session to v$session -> and I can compile the procedure!!!

But it nevertheless does not works in triggers :-(((. Can anybody help me?

Thanks in advance
Boris Received on Thu Dec 09 2004 - 07:17:53 CST