Re: Passing DB-Link name in a Loop

Babu,

Closing the cursor doesn't close the connection AFAIK.

You can can use dbms_session.close_database_link or ALTER SESSION CLOSE DATABASE LINK <name> to do that.

Jared

On Friday 21 March 2003 04:28, Babu Nagarajan wrote:
> 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 -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Friday, March 21, 2003 4:49 AM
>
> > 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 - 09:51:25 CST