Parse once execute many

[steeve123]

We have about 15 packages with queries done in the form below.

open curs;
fetch curs into l_var;
close curs;


if the procedure is called more than once.(and the init parameter session_cache_cursor = 0).

Then we get x parse and x execute .
I want of course 1 parse and execute many.

If i change the cursor with the following

if cursor not open then
open;
end if;
fetch curs into var;

1. i have to put the init parameter open_cursor = high value then right?
2.Since i dont close the cursor, will i get in trouble later???

[DaPi]

0) since you intend to reuse the cursors they must always be the same, so x-1 of your parses are "soft". Could be worse - in fact, the anticipatd gain might not justify recoding.

1) open_cursors should only need to be increased by 15.

2) you should only be using (max) 15 of them for your procedures - my guess is that won't cause you any pain at all.

(all these are per session values).