Oracle, ODP.NET, RefCursors, Enterprise Library and Readers

· November 25, 2008

We have been chasing a nasty bug for a few days now. It actually has to do with all the technologies above.

The thing is that we have a Oracle stored procedure that returns a RefCursor. The stored procedures is actually just doing a simple SELECT from our system value tables. We are using Enterprise Library to call the stored procedure. We are using the command ExecuteReader. And here the funny business starts.

For starters the stored procedure declare an OUT RefCursor and so we are adding a RefCursor-parameter to our Oracle store procedure command object. But when our ExecuteReader is ran the command is NULL (DBNull).

Furthermore we ran into some connection problems since we had loads of connections hanging in the database, after running our unit tests (that made many calls to said stored procedure).

After a lot of researching we found out the following actions to solve the problems:

  • You firstly always need to dispose readers from Enterprise Library, after finishing using them. My bad here - just forget about it. Here is a description on how to do it with the beautiful using-construct.
  • Then you need to explicitly call Dispose on all your RefCursor-parameters. This is quite strange - especially since our code only adds the parameter to the stored procedure command and doesn’t use it any more. I can only understand it as ODP.NET is converting the OUT RefCursor to an IDataReader.

After doing this we didn’t have any problems with that the connections wasn’t released as they should.

Twitter, Facebook