Re: dblink in PL/SQL block..

Thanx Edgar,

Tht worked for me. Thanx a lot.

On 1/23/06, Edgar Chupit <chupit_at_gmail.com> wrote:
>
> 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_at_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_at_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_at_gmail.com> 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_at_yahoo.com
> > onkarnath.tiwary_at_gmail.com
> >
>
>
> --
> Best regards,
> Edgar Chupit
> callto://edgar.chupit
>

--
Thanks & Regards,
T. Onkar Nath
OneAPPS Enterprise Technology Pvt. Ltd.
       to_onkar_at_yahoo.com
       onkarnath.tiwary_at_gmail.com

--
http://www.freelists.org/webpage/oracle-l