Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle.ManagedDataAccess 12.2.1100 OracleDataAdapter using 127 connections

user12141239May 24 2018 — edited May 25 2018

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:
pastedImage_7.png

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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 22 2018
Added on May 24 2018
3 comments
1,147 views