Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Passing DB-Link name in a Loop

Passing DB-Link name in a Loop

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Fri, 21 Mar 2003 09:32:18 +0800 (SGT)
Message-Id: <24726.322603@fatcity.com>


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 Received on Thu Mar 20 2003 - 19:32:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US