oracle 连接关闭时，ODP.net 是否关闭引用游标？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/358135/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does ODP.net close a ref cursor when the connection closes?
提问by Dave Baghdanov
I haven't been able to find this explicitly stated anywhere yet, but a bunch of examples I've found online follow what I've been doing.
我还没有在任何地方找到明确说明的这一点，但是我在网上找到的一堆例子都遵循我一直在做的事情。
I have a C# class which uses ODP.net to connect to an Oracle DB and run a procedure that's in a package.
我有一个 C# 类，它使用 ODP.net 连接到 Oracle DB 并运行包中的过程。
My package has stored procedures which take a ref cursor output parameter. All the procedure does is open up the cursor for a particular select statement.
我的包具有采用引用游标输出参数的存储过程。该过程所做的就是为特定的选择语句打开游标。
If I execute this procedure directly on the oracle db, then eventually I'll hit a max number of open cursors error.
如果我直接在 oracle db 上执行这个过程，那么最终我会遇到最大数量的打开游标错误。
So I was wondering if ODP.net does indeed close this cursor that was opened in my procedure?
所以我想知道 ODP.net 是否确实关闭了在我的程序中打开的这个游标？
I'm using the OracleDataApaper.Fill(DataSet) method.
我正在使用 OracleDataApaper.Fill(DataSet) 方法。
eg.
例如。
DataSet ds = new DataSet();
OracleConnection conn = new OracleConnection(this.connStr);
OracleCommand com = new OracleCommand("MYPKG.MYQUERY", conn);
OracleDataAdapter adapter = new OracleDataAdapter(com);
conn.Open();
com.Parameters.Add("searchParam", OracleDbType.Varchar2).Value = "myName";
com.Parameters.Add("outCursor", OracleDbType.RefCursor, ParameterDirection.Output);
com.CommandType = CommandType.StoredProcedure;
adapter.Fill(ds);
conn.Close();
PROCEDURE GETALLEMAILS(searchParamIN VARCHAR2, outCursor OUT sys_refcursor) AS
BEGIN
open outCursor
select
EAEMAL as Email
from
EmailTable
where
EmailName = searchParam;
END GETALLEMAILS;
I'm just afraid of leaving open cursors behind on the DB is all. If anyone can provide links to official documentation, that'd be great!
我只是害怕在数据库上留下打开的游标就是全部。如果有人可以提供官方文档的链接，那就太好了！
Updates:
更新：
Thanks for the input. I was calling
感谢您的投入。我在打电话
com.Dispose();
conn.Close();
conn.Dispose();
but left them out of my example.
但将它们排除在我的示例之外。
I found this forum post, which states that the OracleDataAdapter.Fill(Dataset) method does release the ref cursor after the Fill() method has been executed.
http://www.frontoracle.com/oracle-archive/140/386140-close-ref-cursor.html
我找到了这个论坛帖子，其中指出 OracleDataAdapter.Fill(Dataset) 方法在执行 Fill() 方法后确实释放了引用游标。
http://www.frontoracle.com/oracle-archive/140/386140-close-ref-cursor.html
I wish the Oracle documentation was more explicit in describing this process though.
不过，我希望 Oracle 文档在描述此过程时更加明确。
回答by Frans Bouma
ODP.NET requires you to clean up things. So you:
ODP.NET 需要你清理东西。那么你：
- have to dispose OracleParameter instances, as they contain unmanaged resources (!) and Odp.net doesn't do this
- have to dispose OracleCommand objects, as they too contain unmanaged resources and closing a connection doesn't close these
- open cursors can't live without an open connection, though in odp.net nothing gets cleaned up after a connection closes (or gets disposed), so you have to clean up these too (and before the connection closes of course).
- 必须处理 OracleParameter 实例，因为它们包含非托管资源 (!) 而 Odp.net 不这样做
- 必须处理 OracleCommand 对象，因为它们也包含非托管资源并且关闭连接不会关闭这些
- 如果没有打开的连接，打开的游标就无法生存，尽管在 odp.net 中，在连接关闭（或处理）后没有任何东西被清理，所以你也必须清理它们（当然在连接关闭之前）。
I.o.w.: clean up what you create.
Iow：清理你创造的东西。
It can be the OracleDataAdapter already does this for you, but that's unclear (and the odp.net docs don't say this, so you've to check the (unreadable) code with reflector to make sure. Though rule of thumb with odp.net: to avoid memory leaks, always call dispose, on everything in the order: parameter, cursor, command, transaction, connection.
可能是 OracleDataAdapter 已经为您完成了此操作，但尚不清楚（并且 odp.net 文档没有说明这一点，因此您必须使用反射器检查（不可读）代码以确保。虽然 odp 的经验法则.net：为避免内存泄漏，始终调用 dispose，按顺序对所有内容进行处理：参数、游标、命令、事务、连接。
回答by AJ.
I'm not sure if you've stumbled onto thisarticle, and it doesn't apply directly to your question, but it illustrates something I learned when working with ODP.Net: when in doubt, always close (connections) and dispose. Every method I write that uses an instance of ODP connections, commands, and/or cursors has a finally clause disposing everything.
我不确定您是否偶然发现了这篇文章，它并不直接适用于您的问题，但它说明了我在使用 ODP.Net 时学到的一些东西：如有疑问，请始终关闭（连接）并处理。我编写的每个使用 ODP 连接、命令和/或游标实例的方法都有一个 finally 子句来处理所有内容。
