joining tables through db-link

From: Jens Mayer <jmayer_at_ratundtat.com>
Date: Mon, 10 May 1999 17:26:18 +0200
Message-ID: <3736FA9A.18539A84@ratundtat.com>

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 Received on Mon May 10 1999 - 10:26:18 CDT