I wrote the following code to use Entity Framework 6 and Managed Oracle Providers to call an Oracle stored procedure that returns multiple cursors.

Would like some input about the code I used to open and close the database connection. Please note that the open and close code is a workaround for a possible bug in the Oracle library that occurs every time using is used. Doing that instead of the workaround would throw the following exception:

 System.ObjectDisposedException: 'Cannot access a disposed object.Object name: 'OracleConnection'.'

Would also like some input on the way I am getting the multiple cursors and whether or not there might be a better way.

Working Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.Data.Entity.Infrastructure;

namespace MyCompany
    public class MyClass
        private MyDbContext dbContext = new MyDbContext();

        public MyItems GetMyItems(string id)
            var sqlQuery = "";
            var oracleParameters = new List<OracleParameter>();
            var oneEntityList = new List<OneEntity>();
            var twoEntityList = new List<TwoEntity>();
            var threeEntityList = new List<ThreeEntity>();

            sqlQuery = @"


MY_PACKAGE.GetMyItems(:id, :p_cursor1, :p_cursor2, :p_cursor3);


            oracleParameters = new List<OracleParameter>
                new OracleParameter("p_id", id),
                new OracleParameter("p_cursor1", OracleDbType.RefCursor, ParameterDirection.Output),
                new OracleParameter("p_cursor2", OracleDbType.RefCursor, ParameterDirection.Output),
                new OracleParameter("p_cursor3", OracleDbType.RefCursor, ParameterDirection.Output)

            var connection = dbContext.Database.Connection;            
            var command = connection.CreateCommand();
            command.CommandText = sqlQuery;
            using (var reader = command.ExecuteReader())
                oneEntityList = ((IObjectContextAdapter)dbContext).ObjectContext

                twoEntityList = ((IObjectContextAdapter)dbContext).ObjectContext

                threeEntityList = ((IObjectContextAdapter)dbContext).ObjectContext

            return new MyItems { OneEntity = oneEntityList, TwoEntity = twoEntityList, ThreeEntity = threeEntityList };

  • \$\begingroup\$ In my experience EF and Oracle don't like each other. EF is great only for the sql server. With oracle it's easier to use Dapper and raw queries. \$\endgroup\$
    – t3chb0t
    Nov 24, 2018 at 15:07
  • 1
    \$\begingroup\$ It appears that you removed the errorSystem.ObjectDisposedException: 'Cannot access a disposed object.Object name: 'OracleConnection'.'” from the description, yet didn’t change the code. Does the error still occur? \$\endgroup\$ Nov 24, 2018 at 19:12
  • 1
    \$\begingroup\$ No, the error does not occur. There never was an error on the code I posted. I was mentioning that if I used a using statement an error occurred. This code review post is legit and should not be on hold. \$\endgroup\$
    – ADH
    Nov 24, 2018 at 20:30
  • \$\begingroup\$ This code review post is legit - I think it could be if you reframed it because 1000% the first suggestion will be to use the using statement to which you will replay that it doesn't work because an exception is thrown... this is a vicious circle. You've removed a very important fact from your question. Please clarify that this is a workaround for the apparent bug in the Oracle library that occurs everytime using is used and it throws the exception you've named in the first version. \$\endgroup\$
    – t3chb0t
    Nov 25, 2018 at 7:57
  • 1
    \$\begingroup\$ @t3chb0t Updated post. I did not realize the exception was a bug with Oracle. \$\endgroup\$
    – ADH
    Nov 25, 2018 at 13:38


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.