Re: REF CURSOR

Mtek <mtek_at_mtekusa.com> wrote:

<snip>

> > If you are using dynamic SQL and need to loop through the results then you
> > probably do need to open an explicit cursor. And you will need to close and
> > re-open it when the statement changes in any way.
> >
> > If you were just changing the bind variable values, you could avoid some of
> > the overhead by using DBMS_SQL, but that will not help if the conditions actually
> > change.
> >
> > --
> > _______________________________________________________________________
> > Dan Blum t..._at_panix.com
> > "I wouldn't have believed it myself if I hadn't just made it up."

> Of course, the other option is to have a couple of cursors, and pass
> parameters so I do not have to close and open them.....

You could do that, but whether it's worth it or not depends on how often you will be opening and closing the same statements, and how many different statements you need. If you are going to be running the same statements (with different parameters) thousands of times, and performance is important, you can definitely save some overhead by using DBMS_SQL (it does much less parsing that way, which reduces latching). However, using DBMS_SQL requires more coding than just opening cursors.

-- 
_______________________________________________________________________
Dan Blum					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."