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

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

Post by jm_newsgro » Tue, 24 Aug 2004 06:47:06


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.
 
 
 

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

Post by Frans Boum » Tue, 24 Aug 2004 18:54:47


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.

--
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.yqcomputer.com/
My .NET Blog: http://www.yqcomputer.com/
Microsoft C# MVP

 
 
 

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

Post by Roy Fin » Tue, 24 Aug 2004 21:21:35


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.


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.


that makes no sense!



regards
roy fine
 
 
 

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

Post by jm_newsgro » Tue, 24 Aug 2004 23:10:41


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.
 
 
 

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

Post by Frans Boum » Wed, 25 Aug 2004 17:45:54


Err, that's what I said.


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)


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.


???

FB

--
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.yqcomputer.com/
My .NET Blog: http://www.yqcomputer.com/
Microsoft C# MVP