RE: Passing DB-Link name in a Loop

Thanks Tim !
That works !

now I just have to resolve the "ORA-02020: too many database links in use" error !

Thanks again.
Hemant

• Tim Onions <tim.onions_at_speechmachines.com> wrote:

> 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
> also send the HELP command for other information (like
Received on Fri Mar 21 2003 - 03:48:03 CST