1

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 cmd.ExecuteNonQuery(). 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.

Stored Procedure in an Oracle Package: 

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;

C# 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(); //<=== 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.");
        }
    }

When I copy the exception detail to the clipboard this is what I get

Exception Details From The Clipboard 

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& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& 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:
sstan
  • 35,425
  • 6
  • 48
  • 66
MeGreeny
  • 49
  • 5
  • Well I go to it working. I took sstan's advise and switch ReturnValue to Output. I also added a length of the field to the Varchars and the Return. So it looks like this cmd.Parameters.Add("var_ArcNum", OracleDbType.Varchar2, 12).Value = txtArc; cmd.Parameters.Add("var_Return", OracleDbType.Varchar2, 4); With a coma after the Varchar and the length of the field. – MeGreeny Aug 09 '16 at 20:05

3 Answers3

1

Technically, var_Return is not a return value from a function, but an output parameter.

For this reason, I would try changing:

cmd.Parameters["var_Return"].Direction = ParameterDirection.ReturnValue;

to:

cmd.Parameters["var_Return"].Direction = ParameterDirection.Output;

There may be other problems, but I would start with that.

sstan
  • 35,425
  • 6
  • 48
  • 66
  • Thanks sstan I tried that and I still cannot get past cmd.ExecuteNonQuery() – MeGreeny Aug 09 '16 at 13:57
  • In addition to sstan's answer, which is correct, perhaps convert those strings to numbers. Not sure of the C# equivalent, but in VB I'd have `cmd.Parameters.Add("var_PaidAmt", OracleDbType.Decimal).Value = CDec(txtPaidAmtNum)`. I normally passed them into my routine as the proper type to begin with. I'd also make var_Trans `Int32` type unless you know it will never exceed a `Short` – topshot Aug 09 '16 at 14:32
1
  1. Change the direction on "var_Return" to ParameterDirection.Output
  2. Specify a length for "var_Return". I believe the default for varchar2 is 32k (but I can't recall ever setting a length so large so consider 4k to start).
  3. Let ODP.net determine the type on your input values (not a requirement but less prone to errors IMO).
  4. Confirm your input value types match the parameter types

ie:

cmd.Parameters.Add("var_ArcNum",  txtArc);
cmd.Parameters.Add("var_Trans", Int32.Parse(txtTranIDNum));//if this is a string, you have to convert it in order to pass it as an int, even if you specify the parameter type
cmd.Parameters.Add("var_TransStatus", vStatus);
cmd.Parameters.Add("var_PaidAmt", Decimal.Parse(txtPaidAmtNum));
cmd.Parameters.Add("var_Return", OracleDbType.Varchar2, 4000);
cmd.Parameters["var_Return"].Direction = ParameterDirection.Output;
b_levitt
  • 7,059
  • 2
  • 41
  • 56
0

Well I go to it working. I took sstan's advise and switch ReturnValue to Output. I also added a length of the field to the Varchars and the Return. So it looks like this

//                   Added the 12 after the Varchar, ===\/
cmd.Parameters.Add("var_ArcNum", OracleDbType.Varchar2, 12).Value = txtArc; 

//                   Added the 4 after the Varchar, ===\/
cmd.Parameters.Add("var_Return", OracleDbType.Varchar2, 4);

With a coma after the Varchar and the length of the field.

MeGreeny
  • 49
  • 5