Xref: alice comp.databases.oracle.server:48925
Path: alice!news-feed.fnsi.net!cyclone.i1.net!newsfeed.enteract.com!newsfeed.tli.de!newsfeed.nacamar.de!f.de.uu.net!news01.roka.net!news.space.net!not-for-mail
From: Jens Mayer <jmayer@ratundtat.com>
Newsgroups: comp.databases.oracle.server
Subject: joining tables through db-link
Date: Mon, 10 May 1999 17:26:18 +0200
Organization: Rat & Tat Beratungsgesellschaft mbH
Lines: 56
Message-ID: <3736FA9A.18539A84@ratundtat.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: Mozilla 4.51 [de]C-QXW0310J  (WinNT; I)
X-Accept-Language: de, en

Hi there,

I have two tables, Table A resides on my own database, table B on a
remote database which I access through a db-link. I made up a cursor
joining both tables via primary key, (Works fine, resultset is correct.)

Now I am using this cursor in a package function with a FOR ... LOOP
construct, structured like this:

FUNCTION myFUNC 

Cursor myCur IS 
select a.column1, b.column2
from mytableA a,
     mytableB b
where a.column1 = b.column2(+);

BEGIN
  ...
  FOR myRec IN myCur LOOP
      ...
  END LOOP;
  RETURN 1;

EXCEPTION
   WHEN....
   RETURN -1;
END;

I am using many of these constructs, committing after each function.
Sometimes, Oracle creates an error: ORA-01000: maximum open cursors
exceeded, ORA-02063 preceding line of <remote_db>. I don't understand
this, because in my opinion there's only one cursor at a time open.
Oracle closes the cursor by leaving the loop. 

May I have a problem like this: if an error occurs inside the LOOP,
Oracle throws the exception and terminates the function. Will Oracle
close the cursor automatically ? 
If I move the exception inside the LOOP:
  ...
  FOR myRec IN myCur LOOP
    BEGIN
      ...
      RETURN 1;
    EXCEPTION
      WHEN....
        RETURN -1;
    END;
END LOOP;

Will that make any difference ? Will Oracle close the cursor as well by
leaving the Loop via RETURN-Statement ?

Any suggestions ?

Jens