title: C# issue with an Oracle package
body: <p>I am have an issue with a simple procedure in an Oracle package. There are other procedures that I am using in the package that work fine. The other packages us Cursors. This one uses a Return. I am not able to get the code to get past <code>cmd.ExecuteNonQuery()</code>. So my thinking is that I did not construct the command properly. I have try to modify the command, but it still does not seem to work. If someone could take a look at it I would appreciate it.</p>
  
  <p><strong>Stored Procedure in an Oracle Package:</strong> </p>
  
  <pre><code>Name             Data Types:
  ARC_NUMBER       VARCHAR2(12)
  TRANSACTION_ID   NUMBER
  TRANS_STATUS     VARCHAR2(1)
  PAID_AMT         NUMBER(8,2)
  
  PROCEDURE UPDATE_TRANS_BY_OUTPUT(
  var_ArcNum IN TRANSACTION.ARC_NUMBER%type,
  var_Trans IN TRANSACTION.TRANSACTION_ID%type,
  var_TransStatus IN TRANSACTION.TRANS_STATUS%type,
  var_PaidAmt IN TRANSACTION.PAID_AMT%type,
  var_Return OUT VARCHAR2)   
  IS BEGIN 
  BEGIN
  UPDATE TRANSACTION
  SET
    ARC_NUMBER = var_ArcNum,
    TRANS_STATUS = var_TransStatus,
    PAID_AMT = var_PaidAmt
  WHERE TRANSACTION_ID = var_Trans;
    var_Return := 'PASS';
  EXCEPTION
  WHEN OTHERS THEN
    var_Return := 'FAIL';
  END UPDATE_TRANS_BY_DEDUCT;
  </code></pre>
  
  <p><strong>C# Code</strong></p>
  
  <pre><code>    public void updateTransactionDeduct(string txtArc, string txtTransID, string vStatus, string txtPaidAmt)
      {
          string constr = WebConfigurationManager.ConnectionStrings["OracleDbConnection"].ConnectionString;
  
          OracleConnection con = new OracleConnection(constr);
  
          OracleCommand cmd = new OracleCommand();
          cmd.Connection = con;
  
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = "ucs.trans_data.UPDATE_TRANS_BY_OUTPUT";
  
          cmd.BindByName = true;
  
          //ASSIGN PARAMETERS TO BE PASSED
          cmd.Parameters.Add("var_ArcNum", OracleDbType.Varchar2).Value = txtArc;
          cmd.Parameters["var_ArcNum"].Direction = ParameterDirection.Input;
          cmd.Parameters.Add("var_Trans", OracleDbType.Int16).Value = txtTranIDNum;
          cmd.Parameters["var_Trans"].Direction = ParameterDirection.Input;
          cmd.Parameters.Add("var_TransStatus", OracleDbType.Varchar2).Value = vStatus;
          cmd.Parameters["var_TransStatus"].Direction = ParameterDirection.Input;
          cmd.Parameters.Add("var_PaidAmt", OracleDbType.Decimal).Value = txtPaidAmtNum;
          cmd.Parameters["var_PaidAmt"].Direction = ParameterDirection.Input;
  
          cmd.Parameters.Add("var_Return", OracleDbType.Varchar2);
          cmd.Parameters["var_Return"].Direction = ParameterDirection.ReturnValue;
  
          con.Open();
          cmd.ExecuteNonQuery(); //&lt;=== Errors Out Here
  
          string txtReturn = cmd.Parameters["var_Return"].Value.ToString();
  
          con.Close();
  
          if (txtReturn == "FAIL" | txtReturn == null)
          {
              MsgBox("Transaction record not updated.");
          }
          else
          {
              MsgBox("Transaction was updated.");
          }
      }
  </code></pre>
  
  <p>When I copy the exception detail to the clipboard this is what I get</p>
  
  <p><strong>Exception Details From The Clipboard</strong> </p>
  
  <pre><code>Oracle.ManagedDataAccess.Client.OracleException was unhandled by user code
    DataSource=""
    ErrorCode=-2147467259
    HResult=-2147467259
    IsRecoverable=false
    Message=ORA-06550: line 1, column 15:
  PLS-00306: wrong number or types of arguments in call to 'UPDATE_TRANS_BY_DEDUCT'
  ORA-06550: line 1, column 7:
  PL/SQL: Statement ignored
    Number=6550
    Procedure=""
    Source=Oracle Data Provider for .NET, Managed Driver
    StackTrace:
     at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32&amp; cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException&amp; exceptionForArrayBindDML, Boolean&amp; hasMoreRowsInDB, Boolean bFirstIterationDone)
     at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32&amp; cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException&amp; exceptionForArrayBindDML, Boolean bFirstIterationDone)
     at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]&amp; scnFromExecution, OracleParameterCollection&amp; bindByPositionParamColl, Boolean&amp; bBindParamPresent, OracleException&amp; exceptionForArrayBindDML, Boolean isFromEF)
     at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
     at Query_Test.Review_List.updateTransactionDeduct(String txtPolicy, String txtTransID, String vStatus, String txtPaidAmt) in C:\Working\Test\Query_Test\Query_Test\Review_List.aspx.cs:line 314
     at Query_Test.Review_List.GridViewListComp_RowUpdated(Object sender, GridViewUpdateEventArgs e) in C:\Working\Test\Query_Test\Query_Test\Review_List.aspx.cs:line 115
     at System.Web.UI.WebControls.GridView.OnRowUpdating(GridViewUpdateEventArgs e)
     at System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation)
     at System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup)
     at System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument)
     at System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
     at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
     at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
     at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
    InnerException: 
  </code></pre>