Re: joining tables through db-link

Hi Jens,

I'm not sure how to resolve this, but it is quite clear what has happened. The statement of course is split in two parts, the select on table B runs on the remote database and of course in a separate implicit cursor, the data is sent to database A, where the join is being executed. The error message is telling you you have exceeded the maximum number of cursors on the remote db.
I have a strange feeling that whenever your program 'crashes' the local cursor will be cleaned automatically. Obviously this doesn't apply for the remote cursor. You need at least to try to close the cursor in your code explicitly.

Hth,
Sybrand Bakker, Oracle DBA

Jens Mayer wrote in message <3736FA9A.18539A84_at_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 - 11:57:45 CDT