Re: Why can't I close a cursor in a LOOP?

It is quite possible to serially parse and execute different statements in the same cursor. Since you (should?) open your insertCursor outside of the loop, you can close it outside of the loop too. If you dont open insertCursor, you should consider doing it :-)

john_galt29_at_my-deja.com wrote:

> Hello. I have the following code which gets a list of all tables then
> goes through each table and looks for certain data, like this:
>
> OPEN allTablesCursor;
> FETCH allTablesCursor INTO curTable;
> LOOP
> EXIT WHEN allTablesCursor%NOTFOUND;
> sqlStmt := 'INSERT INTO tmpTable SELECT col1 FROM ' || curTable || '
> WHERE id=x';
> DBMS_SQL.PARSE(insertCursor, sqlStmt, DBMS_SQL.NATIVE);
> l_rowsProcessed := DBMS_SQL.EXECUTE(insertCursor);
> --DBMS_SQL.CLOSE_CURSOR(insertCursor);
> FETCH allTablesCursor INTO curTable;
> END LOOP;
>
> If I un-comment the DBMS_SQL.CLOSE_CURSOR line I get an Invalid Cursor
> exception. This didn't worry me in the past but as the number of tables
> grows, this could leave a bunch of OPEN cursors, and I could get a 'Too
> many open cursors' error. Does anyone know why I can't close these
> cursors in this way, and HOW I CAN close them each them one is created?
>
> Thanks in advance!
> -John
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

--

+-------------------------------------+----------------------------+
| Fred Petillot                       |     fpetillo_at_fr.oracle.com |
| Oracle France - Support Technique   |            +33 1 4762 8201 |
| 65, rue des Trois Fontanot          |            +33 6 8089 5135 |
| 92732 Nanterre Cedex                |                            |
+-------------------------------------+----------------------------+