Calling an Oracle Stored Procedure using C#

Bufnita Ciumec

Sep 9 2008 11:22 AM

5.8k

Hi, All

I am trying to call an Oracle 8i Stored Proc, and return a value which I store in a TextBox. My problem is that I keep getting this error when I try to execute the SP:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'OAT_ASSIGNMENTS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

This is my C# code:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
using System.IO;
using System.Net;
using System.Web.SessionState;
using System.Web.UI.HtmlControls;

public partial class CRN_XML_Input : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        OracleConnection oraConn;
        HPBSOraConn oConn;
        HPBSOraConn oConnSP;
        oConnSP = new HPBSOraConn();
        OracleConnection MyConn = new OracleConnection(oConnSP.GetConnString("MSHPIDb").ToString());
        OracleCommand SPcmd = new OracleCommand("RULES_ENGINE.OAT_Assignments", MyConn);
        SPcmd.CommandType = CommandType.StoredProcedure;
        SPcmd.Parameters.Clear();

        OracleParameter OUTsiebel_login_id = new OracleParameter();
        SPcmd.Parameters.Add("OUTsiebel_login_id", OracleType.VarChar, 75).Direction = ParameterDirection.ReturnValue;

        OracleParameter Transaction_Num = new OracleParameter("INtransaction_num",iRequestID.ToString());
        OracleParameter State = new OracleParameter("INstate",sState.Trim().ToString());
        OracleParameter Notification_Group = new OracleParameter("INnotification_group",sNotificationGroup.Trim().ToString());

        SPcmd.Parameters.Add("INtransaction_num", OracleType.VarChar,25).Direction = ParameterDirection.Input;
        SPcmd.Parameters.Add("INstate", OracleType.VarChar,50).Direction = ParameterDirection.Input;
        SPcmd.Parameters.Add("INnotification_group", OracleType.VarChar,75).Direction = ParameterDirection.Input;

        MyConn.Open();
        SPcmd.ExecuteNonQuery();
             if (OUTsiebel_login_id.Value.ToString() != null)
             {
              TextBox6.Text = "Employee Assigned Claim is: " + SPcmd.ExecuteNonQuery().ToString(); //OUTsiebel_login_id.Value.ToString();
             }

            MyConn.Close();
            MyConn.Dispose();
        }
    }
}

And this is my Stored Proc:

CREATE OR REPLACE PROCEDURE R_E.Assignments
(INtransaction_num   IN VARCHAR,
   INstate IN VARCHAR,
   INnotification_group   IN VARCHAR)
IS
   RETURN VARCHAR(75);
   OUTsiebel_login_id VARCHAR(75);
BEGIN

    SELECT DISTINCT V.SIEBLELOGINID
    INTO OUTsiebel_login_id
    FROM RULES_ENGINE.V_DUAL_INSURE_EMP_INFO V
    WHERE V.RANK = (SELECT MIN(V.RANK)
                    FROM RULES_ENGINE.V_DUAL_INSURE_EMP_INFO V
                    WHERE V.STATE = INstate);
RETURN;
END;
/