ODP.NET

Hi,

Before posting, I searched for around an hour or more to see if anyone else ran into a similar issue, but was unable to find a related problem online.

I have a legacy application that I upgraded to use Oracle.ManagedDataAccess 12.2.1100 from the older ODP .NET

Locally, I am reproducing an issue. When filling a DataTable, I end up with 127 connections. If I pause the application during the Fill, I get a consistent 127 connections held for my user/module in v$sessions that last until I allow the Fill to finish by resuming.

This spike is causing headaches with the DBA as it uses up the max connection pool.

To troubleshoot, I checked my connection string to see if it was incorrect. During debugging, I tried this:

"Data Source=mytnsentryhere;User ID=myusernamehere;Password=mypasswordhere;Pooling=true;Min Pool Size=1;Max Pool Size=2;Incr Pool Size=1; Decr Pool Size=1;Persist Security Info=true"

Next, I tried checking my package function to see if that's the problem. It looks like this:

create or replace package mypackage is

type dataset is ref cursor;
function get_data(n_id in number)return mypackage.dataset;

end mypackage;

create or replace package body mypackage is

function get_data(n_id in number) return mypackage.dataset is

ds mypackage.dataset;

begin

open ds for

select f.\*  

from mytable f
where f.id = n_id;

return ds;

end;

end mypackage;

My .NET code looks like this:

           var cmd = new OracleCommand("mypackage.get\_data", myconnectionObject)

            {

                CommandType = CommandType.StoredProcedure

            };

            cmd.Parameters.Add("return\_value", OracleDbType.RefCursor, ParameterDirection.ReturnValue);

            cmd.Parameters.Add("n\_id", OracleDbType.Int64, sampleSet\_ID, ParameterDirection.Input);

            var oracleDataAdapter = new OracleDataAdapter(cmd);

            var dataTable = new DataTable();

            oracleDataAdapter.Fill(dataTable); // \<-- this line causes 127 v$session entries

I verified this issue using a SQL Developer session running a query that looks like this w/ this result:

To verify this was not a recent issue, I tried two other nuget package versions. One less than this version and the oldest available version on nuget. To be sure I got the new packages, I deleted my bin folder, after upgrading, then rebuilt the project. Neither solved the issue. I did not try reverting to ODP .NET, but I don't consider that a desirable solution.

Any thoughts on what might be going on?

Thanks,

John Goodwin