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

[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.

 

[Frans Bouma [C# MVP]]

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?

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.

 

[Roy Fine]

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.

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.

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.

that makes no sense!



regards
roy fine

 

[JM]

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,

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.

 

[Frans Bouma [C# MVP]]

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.

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.

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.

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!

???

FB