7 Replies Latest reply: Aug 28, 2013 12:25 PM by Alex.Keh .Product.Manager-Oracle RSS

    Bug in fully managed ODP.NET: output parameter in RETURNING clause


      Assume there is a following test data in Oracle:


          X INT PRIMARY KEY,
          Y INT
      INSERT INTO FOO (X, Y) VALUES (1, 11);


      The ExecuteNonQuery in the code below throws an exception when fully managed ODP.NET is used (the file vrersion of my Oracle.ManagedDataAccess.dll is, but passes without throuble with the old ODP.NET:


      using System.Data;
      using System.Diagnostics;
      // NOTE: Using Oracle.DataAccess.Client instead would let the code below pass...
      using Oracle.ManagedDataAccess.Client;
      using Oracle.ManagedDataAccess.Types;
      namespace OdpExperiment {
          class Program {
              static void Main(string[] args) {
                  const string host = "...";
                  const ushort port = 1521;
                  const string sid = "...";
                  const string user = "...";
                  const string password = "...";
                  using (
                      var conn = new OracleConnection(
                              "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})));User Id={3};Password={4};",
                  ) {
                      // We try to delete a nonexistent row and return its content.
                      var sql_text = "DELETE FROM FOO WHERE X = 2 RETURNING Y INTO :y";
                      // NOTE: Doing it like this would work, but then the returned number of affected rows would always be -1 (even when the row is actually deleted):
                      //var sql_text = @"
                      //    DECLARE
                      //        tmp INT;
                      //    BEGIN
                      //        DELETE FROM FOO WHERE X = 2 RETURNING Y INTO tmp;
                      //        :y := tmp;
                      //    END;
                      var cmd = new OracleCommand(sql_text, conn);
                      var y = cmd.Parameters.Add("y", OracleDbType.Decimal, ParameterDirection.Output);
                      using (conn.BeginTransaction()) {
                          var row_count = cmd.ExecuteNonQuery();
                          Trace.Assert(row_count == 0);
                          Trace.Assert(y.Value is OracleDecimal);


      The exception thrown from the ExecuteNonQuery above is:


            System.ArgumentOutOfRangeException was unhandled


              Message=Index was out of range. Must be non-negative and less than the size of the collection.

            Parameter name: index




                   at System.ThrowHelper.ThrowArgumentOutOfRangeException()

                   at System.Collections.Generic.List`1.get_Item(Int32 index)

                   at OracleInternal.TTC.Accessors.TTCNumberAccessor.GetByteRepresentation(Int32 currentRow)

                   at OracleInternal.ServiceObjects.OracleParameterImpl.ExtractDecimalFromAccessor(Accessor accessor, PrmEnumType enumType, Int32 currentRow)

                   at OracleInternal.ServiceObjects.OracleParameterImpl.GetDecimalFromBytes(Accessor accessor, PrmEnumType enumType)

                   at Oracle.ManagedDataAccess.Client.OracleParameter.PostBind_Decimal(Accessor bindAccessor)

                   at OracleInternal.ServiceObjects.OracleCommandImpl.ExtractAccessorValuesIntoParam(OracleParameterCollection paramColl, OracleConnection connection, String commandText, Int64 longFetchSize, Int64[] scnFromExecution)

                   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()

                   at OdpExperiment.Program.Main(String[] args) in d:\Playground\OdpExperiment\OdpExperiment\Program.cs:line 65

                   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)

                   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

                   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

                   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

                   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

                   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

                   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

                   at System.Threading.ThreadHelper.ThreadStart()




      Is there a workaround for this bug? Are there plans to fix it?