Re: Closing cursors

<jba_lixx_at_my-deja.com> wrote in message news:8l9p0a$f0c$1_at_nnrp1.deja.com...
> Hi,
>
> usually I close all my cursors in PL/SQL procs as soon as I can,
> now however I have a case where a procedure will work just fine if I
> don't close the cursor, but will exit with an ORA-01001: invalid cursor
> if I try to close it anywhere.
>
> Now I winder if it's okay to leave the code like that or if I'll get
> into trouble later on for leaving the cursor open.
>
> Here's the code:
>
> CREATE OR REPLACE PROCEDURE Add_To_Depothistory IS BEGIN
> DECLARE
> CURSOR accountCursor IS
> SELECT a.pk_id, a.money, b.rank, Depotsum(a.pk_id) AS
> depotvalue, Countshares(a.pk_id) AS sharescount FROM ACCOUNT a, RANKING
> b WHERE b.fk_account_id = a.pk_id;
> BEGIN
> FOR account_rec IN accountCursor LOOP
> INSERT INTO DEPOTHISTORY (fk_account_id, money, rank,
> depotvalue, sharescount)
> VALUES(account_rec.pk_id, account_rec.money,
> account_rec.rank, account_rec.depotvalue, account_rec.sharescount);
>
> END LOOP;
>
> /*CLOSE accountCursor;*/
> COMMIT;
> END;
> END Add_To_Depothistory;
> /
>
> This works, as long as I don't uncomment the CLOSE.
>
> It doesn't matter whether I put the close before or after the COMMIT.
>
> Any ideas anyone?
>
> Thanks.
>
> Jens
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Take out your close. When you do a for-loop with a cursor, it opens, fetches, and closes for you. You basically don't need it.

What you can do (not needed here but in other situations), should you want to manually close a cursor at any time but it's in doubt if the cursor is open or not at the time, evaluate the ISOPEN attribute:

IF mycursorname%ISOPEN THEN

   close mycursorname;
END IF; This way it'll close if it's open, but if it's already closed, you won't get the invalid cursor error.

-Matt Received on Fri Jul 21 2000 - 00:00:00 CEST