dblink in PL/SQL block..
- From: Onkar N Tiwary <onkarnath.tiwary@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 Jan 2006 12:52:10 +0530
hi all,
I have one small problem. Below is my query which is running on the sql
prompt:
*select username||'('||sid||','||serial#||') ospid = ' || process ||'
program = ' || program username,to_char(LOGON_TIME,' Day HH24:MI')
logon_time,to_char(sysdate,' Day HH24:MI') current_time,sql_address,
LAST_CALL_ET from **v$session@tmoldb_cochin* <v$session@tmoldb_cochin> *where
status = 'ACTIVE' and rawtohex(sql_address) <> '00' and username is not null
order by last_call_et;*
* select max(decode(piece,0,sql_text,null))
||max(decode(piece,1,sql_text,null)) ||max(decode(piece,2,sql_text,null))
||max(decode(piece,3,sql_text,null)) sql_text from **
v$sqltext_with_newlines@tmoldb_cochin*<v$sqltext_with_newlines@tmoldb_cochin>
*where address = x.sql_address and piece < 4;*
but when I am using the same sql in the PL/SQL block as below:
*create or replace procedure showsql as
-- x number;
cursor c1 is
select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session@tmoldb_cochin
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et;
cursor c2 is
select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines@tmoldb_cochin
where address = x.sql_address
and piece < 4;
begin
for x in c1 loop
for y in c2 loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/*
Its throwing the following error:
*
Warning: Procedure created with compilation errors.
SQL> sho err
Errors for PROCEDURE SHOWSQL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2 PL/SQL: ORA-04052: error occurred when looking up remote object
SYS.V_$SESSION@xxxxxxxxxxxxxxxxxxxxxxxxx
ORA-02030: can only select from fixed tables/views
4/2 PL/SQL: SQL Statement ignored
15/2 PL/SQL: SQL Statement ignored
21/37 PL/SQL: ORA-00904: "X"."SQL_ADDRESS": invalid identifier
26/13 PL/SQL: Statement ignored
26/18 PLS-00364: loop index variable 'Y' use is invalid
SQL>
*Any idea???????
--
Thanks & Regards,
T. Onkar Nath
OneAPPS Enterprise Technology Pvt. Ltd.
to_onkar@xxxxxxxxx
onkarnath.tiwary@xxxxxxxxx
Other related posts:
