Re: PRO*C Advice needed for dynamic SQL to avoid reOPENing cursor

Scottfreemail a écrit :
>
> Hi
>
> If I have a cursor :
>
> CURSOR mycursur IS
> SELECT x,y,z from MYTABLE
> WHERE x = :bind1 AND y = :bind2;
>
> can anyone advice me of the PRO*C required to make this fully dynamic, so from
> a loop I can set the bind variable each iteration, and array fetch the results,
> without having to reopen the cursor each time? I'm having performance problems
> with the time its taking to do an OPEN CURSOR. Array fetching is not a problem.
>
> Advice could correspond to writing dynmic SQL using "Method 4" in the online
> Oracle documentation. I'm having terrible problems.
>
> Thanks in advance
> Scott

If you close your cursor, try to suppress the EXEC SQL CLOSE. If you don't and it's still so long, I'm not sure that dynamic SQL is a solution, because at one time or another you must open the cursor to take new values. Try to see if your query is really optimized ...

Good luck !

-- 
 Pierre CHARPENAY