RE: Passing DB-Link name in a Loop

From: Tim Onions <tim.onions_at_speechmachines.com>
Date: Fri, 21 Mar 2003 08:15:43 -0000
Message-Id: <24726.322610@fatcity.com>

Hemant

In case nobody has yet replied I believe your error is that you cannot use a bind variable in an execute immediate for the db link name as you are trying to do with this statement:

      exec_string := 'select sum(bytes)/1048576 from dba_data_files@:b1';

Changing it to this might work (I've not checked it myself so no guarantees)

      exec_string := 'select sum(bytes)/1048576 from dba_data_files@'||remote_db;

        execute immediate exec_string into db_size;

T¬

-----Original Message-----
From: Hemant K Chitale [mailto:hkchital_at_singnet.com.sg] Sent: 21 March 2003 01:34
To: Multiple recipients of list ORACLE-L Subject: Passing DB-Link name in a Loop

Guys,
help me here.
This SQL [below] returns the error :
connecting to AM3C01
declare
*
ERROR at line 1:
ORA-01729: database link name expected
ORA-06512: at line 16

[AM3C01] is the first db_link fetched.

tti 'Database Sizes (excluding TEMPFILEs) ' center

spool DB_Sizes

set serveroutput on size 50000;

declare

   cursor c1 is select db_link from user_db_links;

   remote_DB varchar2(128);
   db_size number;
   exec_string varchar2(255);

begin

   open c1;
   loop

     fetch  c1 into remote_DB;
     exit when c1%NOTFOUND;
      dbms_output.put_line('connecting to '||remote_DB);


--     select  sum(bytes)/1048576 into db_size from

dba_data_files_at_remote_DB;
      exec_string := 'select  sum(bytes)/1048576 from dba_data_files@:b1';
execute immediate exec_string into db_size using remote_DB;
     dbms_output.put_line('DB :  '||remote_DB||':    '||db_size);

   end loop;
   close c1;
 commit;
end;
/

spool off

Hemant K Chitale
http://hkchital.tripod.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Fri Mar 21 2003 - 02:15:43 CST