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

    user1529905

      Assume there is a following test data in Oracle:

       

      CREATE TABLE FOO (
          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 4.121.1.0), 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(
                          string.Format(
                              "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})));User Id={3};Password={4};",
                              host,
                              port,
                              sid,
                              user,
                              password
                          )
                      )
                  ) {
      
      
                      conn.Open();
      
      
                      // 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);
                          Trace.Assert(((OracleDecimal)y.Value).IsNull);
                      }
      
      
                  }
      
      
              }
      
      
          }
      
      
      }
      
      

       

      The exception thrown from the ExecuteNonQuery above is:

       

            System.ArgumentOutOfRangeException was unhandled

              HResult=-2146233086

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

            Parameter name: index

              Source=mscorlib

              ParamName=index

              StackTrace:

                   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()

              InnerException:

       

       

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