12 Replies Latest reply: Feb 5, 2004 12:59 PM by 413760 RSS

    Using UPDATE...RETURNING...INTO...

    413760
      I am trying to use the UPDATE statement with the RETURNING clause:

      string sSql = "UPDATE ... RETURNING x, y INTO :ix, sy";

      ExecuteNonQuery(); // No use for me :(

      // I want to do this:
      OracleDataReader oReader = ExecuteReader();

      Will this work?

      TIA
      Raj
        • 1. Re: Using UPDATE...RETURNING...INTO...
          182042
          It will work as long as you bind the parameters correctly and look for the :ix and :sy values in the output parameters that you bind.
          • 2. Re: Using UPDATE...RETURNING...INTO...
            340203
            ExecuteNonQuery should work.
            I'm assuming that you've bound the :ix and :sy out parameters properly. You should be able to get back the result from the parameters after the ExecuteNonQuery().
            OracleParameter.Parameters[":ix"] and OracleParameter.Parameters[":iy"] will contain the result after the ExecuteNonQuery().

            Thanks
            Martha
            • 3. Re: Using UPDATE...RETURNING...INTO...
              413760
              Thank you both.

              Now here comes the catch. The UPDATE will be updating several 100 rows. So I dont want to use the :ix, :sy variables, but get the result set into an OracleDataReader object.

              ALSO: The :sy is a LONG column!

              Please help!

              TIA
              Raj
              • 4. Re: Using UPDATE...RETURNING...INTO...
                182042
                If you mean to say that you want to retrieve :ix and :sy values as columns in OracleDataReader, that is not possible.
                • 5. Re: Using UPDATE...RETURNING...INTO...
                  413760
                  Hi.. Sorry for the confusions.

                  Lets just say that I want to get all the :ix's out first(CHAR-40 column) after the update. Lets not worry about the LONG :sy for now.

                  i.e., the UPDATE affected 100 rows. Now I want all the 100 :ix values. May be of type OracleDbType.Char[100] - an array.

                  Can I do this?:
                  OracleCommand oUpdateCommand = "UPDATE...RETURNING ix INTO :1";
                  OracleParameter oParameter = oUpdateCommand.Parameters.Add("ix", OracleDbType.Char[]);
                  oParameter.Direction = ParameterDirection.Output;

                  // Execute;

                  string ix[] = (string[]) ( oUpdateCommand.Parameters["ix"].Value )

                  Then I go get the LONG using the OracleDataReader:
                  SELECT ix, sy FROM...WHERE ix IN ( <ix[]> );

                  TIA
                  Raj
                  • 6. Re: Using UPDATE...RETURNING...INTO...
                    413760
                    Actually, I cant pass the array to the Parameters.Add() method. So as of now I've tested with oUpdateCommand.Parameters.Add("ix", OracleDbType.Char).

                    When the call to ExecuteNonQuery() is made, it seems to hang indefinitely. I had to kill it.

                    What is this BULK BINDING option in the RETURNING clause and how I can use it in this scenario?

                    Please Help!

                    TIA
                    Raj
                    • 7. Re: Using UPDATE...RETURNING...INTO...
                      87139
                      Ok, Bulk binding is a red herring here.
                      There are a couple of different, but confusingly similar things going on here. Bulk Binding might be useful if you were using PL/SQL tables to pass values back and forth.

                      You actually want to use a different mechanism for passing collecions to Oracle: Array Binding. This lets you send a single SQL statement, and an Array of parameter values. Internally that statement will be executed once for each value in your parameter array.

                      Anyway here's a sample using Array Binding and "returning into". Here we're giving a list of employees an 8% raise, and returning their new salaries.

                      David


                            string constr = "data source=oracle;user id=scott;password=tiger";

                            OracleConnection con = new OracleConnection(constr);
                            con.Open();

                          
                            string sql = "update emp set sal = sal * 1.08 where ename = :ename returning sal into :newsal";
                           
                            OracleCommand cmd = new OracleCommand(sql,con);
                            string[] emps = {"SCOTT","CLARK","TURNER"};
                            cmd.ArrayBindCount=emps.Length;
                            OracleParameter ename = cmd.Parameters.Add("ename",OracleDbType.Varchar2,10);
                            OracleParameter newSal = cmd.Parameters.Add("newSal",OracleDbType.Decimal);
                            newSal.Direction = ParameterDirection.Output;
                            newSal.Precision  = 7;
                            newSal.Scale = 2;

                            ename.Value = emps;
                            cmd.ExecuteNonQuery();
                            OracleDecimal[] newSalary = (OracleDecimal[])newSal.Value;
                      • 8. Re: Using UPDATE...RETURNING...INTO...
                        413760
                        OK. I tried, and here is the story:

                        ========== CODE ==========
                        sSql =     "UPDATE MY_EXPORT " +
                        "SET STATUS = '" + __BEING_LOADED + "' " +
                        "WHERE ENTERPRISE_KEY IN ('ABC','XYZ') " +
                        "AND SYSTEM_NAME = 'PUBLISH' " +
                        "AND STATUS = '" + __READY_FOR_LOAD + "' " +
                        "AND ROWNUM < " + (iDocumentsPerSession + 1) + " " +
                        "RETURNING EXPORT_KEY INTO :ExportKey";

                        oUpdateCommand = new OracleCommand(sSql, oOracleConnection);

                        OracleParameter oExportKey = oUpdateCommand.Parameters.Add("ExportKey", OracleDbType.Char, 40);
                        oExportKey.Direction = ParameterDirection.Output;

                        oTransaction = oOracleConnection.BeginTransaction();

                        // It fails here:
                        int iRowsAffected = oUpdateCommand.ExecuteNonQuery();

                        // Commit/Rollback...

                        // Then
                        string[] ss = (string[]) oExportKey.Value;

                        ========== END CODE ==========

                        When it does the Execute it fails with the following Exception. Is there something I am still not getting?

                        {"ORA-03106: fatal two-task communication protocol error" }
                        [Oracle.DataAccess.Client.OracleException]: {Oracle.DataAccess.Client.OracleException}
                        System.Object: {Oracle.DataAccess.Client.OracleException}
                        _className: null
                        _COMPlusExceptionCode: -532459699
                        _exceptionMethod: <undefined value>
                        _exceptionMethodString: null
                        _helpURL: null
                        _HResult: -2146233087
                        _innerException: { }
                        _message: "System error."
                        _remoteStackIndex: 0
                        _remoteStackTraceString: null
                        _source: null
                        _stackTrace: {System.Array}
                        _stackTraceString: null
                        _xcode: -532459699
                        _xptrs: 0
                        HelpLink: null
                        HResult: -2146233087
                        InnerException: { }
                        Message: "ORA-03106: fatal two-task communication protocol error"
                        Source: "Oracle Data Provider for .NET"
                        StackTrace: " at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)\r\n at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)\r\n at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()\r\n at ***.Load.DataProvider.Get***(Boolean bReloadFailedOrders) in c:\\documents and settings\\***\\my documents\\visual studio projects\\***\\dataprovider.cs:line 385"
                        TargetSite: {System.Reflection.RuntimeMethodInfo}
                        • 9. Re: Using UPDATE...RETURNING...INTO...
                          87139
                          There are too many potential issues with your code to figure out without DDL and insert statements with sample data to reproduce the problem. And some explanation of what you are trying to do.

                          One obvious problem is that you are never setting the OracleCommand.ArrayBindCount.

                          Another is that your update statement is just wierd. You apparently don't care witch rows get updated.


                          David
                          • 10. Re: Using UPDATE...RETURNING...INTO...
                            413760
                            Hi David,

                            Let me try to explain.

                            I have an application that does some batch processing. I need to look for rows in the MY_EXPORT table matching this criteria:

                            WHERE ENTERPRISE_KEY IN ('ABC','XYZ')
                            AND SYSTEM_NAME = 'PUBLISH'
                            AND STATUS = '__READY_FOR_LOAD'
                            AND ROWNUM < (iDocumentsPerSession + 1)
                            /*RETURNING EXPORT_KEY INTO :ExportKey*/

                            Assume these are the columns in the table:
                            ENTERPRISE_KEY, SYSTEM_NAME, STATUS and EXPORT_KEY(PK)

                            For testing: I set iDocumentsPerSession=50. So, I set the ArrayBindCount also to 50. I know there are 50 rows in the table that WILL get updated (ROWNUM < 51). When the update is done without the RETURNING clause it does update exactly 50 rows.

                            In real-time: One session of this app will process only 50 or less rows. I pick the rows in __READY_FOR_LOAD status and update them to __BEING_LOADED status. Then I process those rows - which is why I need to get PK of the rows that just got updated. So, in real-time, I wont know exactly how many rows will get updated (it could be 50 or less or none) - so I cant set the ArrayBindCount to an exact value - I can set it to the maximum.

                            When I dont set ArrayBindCount I get: "ORA-03106: fatal two-task communication protocol error". If I set ArrayBindCount=50 (for testing, I know 50 rows will get updated) I get this:

                            {"OracleParameter.ArrayBindSize is invalid" }
                            [System.InvalidOperationException]: {System.InvalidOperationException}
                            System.Object: {System.InvalidOperationException}
                            _className: null
                            _COMPlusExceptionCode: -532459699
                            _exceptionMethod: <undefined value>
                            _exceptionMethodString: null
                            _helpURL: null
                            _HResult: -2146233079
                            _innerException: { }
                            _message: "OracleParameter.ArrayBindSize is invalid"
                            _remoteStackIndex: 0
                            _remoteStackTraceString: null
                            _source: null
                            _stackTrace: {System.Array}
                            _stackTraceString: null
                            _xcode: -532459699
                            _xptrs: 0
                            HelpLink: null
                            HResult: -2146233079
                            InnerException: { }
                            Message: "OracleParameter.ArrayBindSize is invalid"
                            Source: "Oracle.DataAccess"
                            StackTrace: " at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()\r\n at ....Load.DataProvider.Get...(Boolean bReloadFailedOrders) in c:\\documents and settings\\...\\my documents\\visual studio projects\\...\\dataprovider.cs:line 385"
                            TargetSite: {System.Reflection.RuntimeMethodInfo}


                            TIA
                            Raj

                            • 11. Re: Using UPDATE...RETURNING...INTO...
                              87139
                              Ok. You're trying to do too many things at once.

                              Change your process to:
                              1. Begin Transaction.
                              2. Select for update.
                              3. Update status columns.
                              4. Do the work.
                              5. Commit

                              step 2 would look like:

                              SELECT EXPORT_KEY
                              WHERE ENTERPRISE_KEY IN ('ABC','XYZ')
                              AND SYSTEM_NAME = 'PUBLISH'
                              AND STATUS = '__READY_FOR_LOAD'
                              AND ROWNUM < (iDocumentsPerSession + 1)
                              FOR UPDATE

                              This will lock the rows and return the EXPORT_KEY's.
                              Read off the EXPORT_KEY's into an array, and they you can do an array-bound update to set the status of each row to __BEING_LOADED. And later to __LOADED or whatever.


                              If you want to you can commit after step 3. This will allow other sessions to work concurrently, but you have to manually account for failures because rows will be commited in __BEING_LOADED status.

                              If you need greater concurrency, and are using 8i Enterprise Edition, or 9iR2 Standard or Enterprise Edition, Oracle Advanced Queueing is available to provide multi-consumer concurrerent work queues.

                              David
                              • 12. Re: Using UPDATE...RETURNING...INTO...
                                413760
                                Wow! That worked out fine for me. I am so dumb, I did not know I can use SELECT..FOR UPDATE like that. I always thought it was meant just for cursors - so you can do the WHERE CURRENT OF thingy. Hmmm! Great!

                                Its a bit slower than doing the UPDATE...RETURNING. But, as you said, in this scenario that is not the way to do it.

                                Thank you!
                                Raj