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

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

Re: Passing DB-Link name in a Loop

From: Babu Nagarajan <orclbabu_at_hotmail.com>
Date: Fri, 21 Mar 2003 07:28:52 -0500
Message-Id: <24738.322692@fatcity.com>


Hemant

I faced the same problem when setting up a centralized monitoring utility (very much similar to what you are trying to accomplish here, I think)

For the Ora-2020 - either increase the distributed_transactions parameter or use dbms_sql instead of execute immediate where you can explicitly close the connection by dbms_sql.close(cursor)

Babu
----- Original Message -----
From: "Hemant K Chitale" <hkchital_at_singnet.com.sg> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, March 21, 2003 4:49 AM
Subject: 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
> > subscribing).
> >
>
>
>
> 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 - 06:28:52 CST

Original text of this message

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