Orphan REF cursor returned to VB.NET from Oracle PL/SQL stored procedure?

  • Thread starter Thread starter JM
  • Start date Start date
J

JM

Hello,

A concern came up here that when one returns a REF cursor from a
PL/SQL procedure to VB.NET, and VB loops through the cursor, etc.,
that the cursor may be left open after connection.close() (or with an
exception, etc.). Is this possible? Is there a way to ensure REF
cursors get closed? Is the use of REF cursors even advisable over
straight SQL statements in place of a stored procedure?

Thanks.
 
JM said:
Hello,

A concern came up here that when one returns a REF cursor from a
PL/SQL procedure to VB.NET, and VB loops through the cursor, etc.,
that the cursor may be left open after connection.close() (or with an
exception, etc.). Is this possible? Is there a way to ensure REF
cursors get closed? Is the use of REF cursors even advisable over
straight SQL statements in place of a stored procedure?
Click to expand...

How do you loop through the cursor? By binding it to a datareader? Did you
try to set the behaviour flag for the datareader? (so it will close the
source when done) ?

I don't think a cursor can be left open when the connection is closed, as
the cursor uses the connection to retrieve new data when you move the cursor.

The debate of what's best: straight sql or a proc is to hot to do it all
over again here I think. On Oracle, developers are often faced with a big
load of procedures they have to deal with, so cursors is all they got.

Frans.
 
Frans Bouma said:
How do you loop through the cursor? By binding it to a datareader? Did you
try to set the behaviour flag for the datareader? (so it will close the
source when done) ?

I don't think a cursor can be left open when the connection is closed, as
the cursor uses the connection to retrieve new data when you move the cursor.
Click to expand...

Not true - the cursor is but a pointer to a memory location in the PGA (for
dedicated servers). When the conection closes, PMON releases all of that
memory - destroying all cursor infor, and invalidating all REF CURSOR
variables.

There is no need to close the REF CURSOR - when all references to it have
gone from scope, the resoruces are recovered - this is PGA memory, and there
are Oralce packages to programatically recover the memory.
The debate of what's best: straight sql or a proc is to hot to do it all
over again here I think.
Click to expand...

Not true - use REF CURSOR when you can not express the resultset in a
predefined select statement - consider a select statment that returns a ref
cursor, consider a using REF CURSOR with PIPE ROWS, consider a REF CURSOR
where the source of result set is not known until runtime.

On Oracle, developers are often faced with a big
load of procedures they have to deal with, so cursors is all they got.
Click to expand...
that makes no sense!



regards
roy fine
 
Frans Bouma said:
How do you loop through the cursor? By binding it to a datareader? Did you
try to set the behaviour flag for the datareader? (so it will close the
source when done) ?

I don't think a cursor can be left open when the connection is closed, as
the cursor uses the connection to retrieve new data when you move the cursor.

The debate of what's best: straight sql or a proc is to hot to do it all
over again here I think. On Oracle, developers are often faced with a big
load of procedures they have to deal with, so cursors is all they got.

Frans.
Click to expand...


Frans,

Thanks for the reply. Yes, I bind it like this:
rdrEst = cmdOra.Parameters("csrRESULTS_OUT").Value

I will investigate the connection behavior flag. Thanks again.
 
Roy said:
Not true - the cursor is but a pointer to a memory location in the PGA (for
dedicated servers). When the conection closes, PMON releases all of that
memory - destroying all cursor infor, and invalidating all REF CURSOR
variables.
Click to expand...

Err, that's what I said.
There is no need to close the REF CURSOR - when all references to it have
gone from scope, the resoruces are recovered - this is PGA memory, and there
are Oralce packages to programatically recover the memory.
Click to expand...

Isn't a REF CURSOR a server side cursor? -> when the cursor points to 1
million rows, you can move the cursor through the rows without fetching them
first into client mem? (I don't know what PGA memory is)
Not true - use REF CURSOR when you can not express the resultset in a
predefined select statement - consider a select statment that returns a ref
cursor, consider a using REF CURSOR with PIPE ROWS, consider a REF CURSOR
where the source of result set is not known until runtime.
Click to expand...

you didn't understand what I said, he meant: dyn. sql executed to grab a
datareader or using a proc which returns a REF CURSOR. Those are two
different things.
that makes no sense!
Click to expand...

???

FB
 
Back
Top