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

<john_galt29_at_my-deja.com> wrote in message news:85f3df$se2$1_at_nnrp1.deja.com...
> 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?
>

You didn't OPEN_CURSOR so you don't need to CLOSE_CURSOR.

Phil. Received on Tue Jan 11 2000 - 05:52:12 CST