Re: dblink in PL/SQL block..

Dear Onkar,

First of all to query dynamic performance views from remote
destination, you should grant your dblink user access to underlying
view/fixed table. In your case you should grant select on v_$session
and v_$sqltext_with_newlines to your dblink user. For example, suppose
that I have dblink trex_tt that will connect using user zulu
credentials:

SQL> grant select on v_$session to zulu;
Grant succeeded

SQL> grant select on v_$sqltext_with_newlines to zulu;
Grant succeeded

After that you can run your queries to
v$session/v$sqltext_with_newlines to query from dblink.

Than after small changed in your code, it compiles and executes just
fine (I've changed line 14 and 25):

SQL> create or replace procedure showsql as
  2  --    x number;
  3      cursor c1 is
  4   select username||'('||sid||','||serial#||
  5                  ') ospid = ' ||  process ||
  6                  ' program = ' || program username,
  7               to_char(LOGON_TIME,' Day HH24:MI') logon_time,
  8               to_char(sysdate,' Day HH24:MI') current_time,
  9               sql_address, LAST_CALL_ET
10          from v$session@trex_tt
11         where status = 'ACTIVE'
12           and rawtohex(sql_address) <> '00'
13           and username is not null order by last_call_et;
14      cursor c2(x c1%rowtype) is
15   select max(decode(piece,0,sql_text,null)) ||
16                            max(decode(piece,1,sql_text,null)) ||
17                            max(decode(piece,2,sql_text,null)) ||
18                            max(decode(piece,3,sql_text,null))
19                                 sql_text
20                       from v$sqltext_with_newlines@trex_tt
21                      where address = x.sql_address
22                        and piece < 4;
23  begin
24      for x in c1 loop
25          for y in c2(x)  loop
26              if ( y.sql_text not like '%listener.get_cmd%' and
27                   y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
28              then
29                  dbms_output.put_line( '--------------------' );
30                  dbms_output.put_line( x.username );
31                  dbms_output.put_line( x.logon_time || ' ' ||
32                                        x.current_time||
33                                        ' last et = ' ||
34                                        x.LAST_CALL_ET);
35                  dbms_output.put_line(
36                            substr( y.sql_text, 1, 250 ) );
37              end if;
38          end loop;
39      end loop;
40  end;
41
42
43  /

Procedure created.

SQL> sho err
No errors.


On 1/23/06, Onkar N Tiwary <onkarnath.tiwary@xxxxxxxxx> wrote:
> hi all,
>
> I have one small problem. Below is my query which is running on the sql
> prompt:
>
>
> Any idea???????
>
> --
> Thanks & Regards,
> T. Onkar Nath
> OneAPPS Enterprise Technology Pvt. Ltd.
>        to_onkar@xxxxxxxxx
>        onkarnath.tiwary@xxxxxxxxx
>


--
Best regards,
  Edgar Chupit
  callto://edgar.chupit
--
http://www.freelists.org/webpage/oracle-l

Other related posts: