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

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. Received on Tue Jan 11 2000 - 05:16:07 CST