Cursors: "Open and Close" or "Use IsOpen"?

[Petemush]

Hello,

I was just trying to figure what was the best thing to do with the following scenario.

I have a cursor for loop.

Inside the loop I need to access a couple of other cursors which will only ever have one row but that row changes each time round the cursor for loop.

Should I open and close the cursors inside the loop or should I open the cursor inside the loop and then close after the loop has finished. Then I would just use the IsOpen function to make sure the cursor is only opened once.

See below for examples. Which way is best?

Cheers,

Pete

Using the IsOpen function:

FOR lc_ex_1 IN cur_ex_1
LOOP

IF NOT cur_cursor2%ISOPEN THEN
OPEN cur_cursor2;
END IF;
FETCH cur_cursor2 INTO ln_cursor2;

IF NOT cur_cursor3%ISOPEN THEN
OPEN cur_cursor3;
END IF;
FETCH cur_cursor3 INTO lc_cursor3;

... Do some stuff with lc_cursor3 and ln_cursor2.
END LOOP;
CLOSE cur_cursor2;
CLOSE cur_cursor3;


Opening and closing within the loop:

FOR lc_ex_1 IN cur_ex_1
LOOP

OPEN cur_cursor2;
FETCH cur_cursor2 INTO ln_cursor2;

OPEN[/color] cur_cursor3;
FETCH cur_cursor3 INTO lc_cursor3;

... Do some stuff with lc_cursor3 and ln_cursor2.
CLOSE cur_cursor2;
CLOSE cur_cursor3;
END LOOP;

 

[sem]

If you open your cursor and don't close it you may guarantee that it would not be pushed out from shared pool.
So it's up to you to decide whether you need to hold it opened and probably save some resources (at the expense of others) or just to let the things go.

Regards, Dima

http://seminihin.narod.ru