dblink in PL/SQL block..

From: Onkar N Tiwary <onkarnath.tiwary_at_gmail.com>
Date: Mon, 23 Jan 2006 12:52:10 +0530
Message-ID: <a144e5210601222322m31b32a0an9df7e63524f02ab4@mail.gmail.com>

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_at_tmoldb_cochin* <v$session_at_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_at_tmoldb_cochin*<v$sqltext_with_newlines_at_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_at_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_at_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_at_TMOLDB_COCHIN.ONEAPPS.COM
         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_at_yahoo.com
       onkarnath.tiwary_at_gmail.com

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

Received on Mon Jan 23 2006 - 01:22:10 CST