Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Calling an Oracle Stored Procedure using C#

BufnitaCiumecBufnitaCiumec Member Posts: 2
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 '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;
/

Does anyone have any suggestions on what I'm missing?

Thanks for any and all help y'all provide.

B.C.

Comments

  • BufnitaCiumecBufnitaCiumec Member Posts: 2
    Further research showed an interesting anomaly. Although permissions were being granted to the user required to execute the SP when the SP was being created, the permissions were never actually set. I had to use the Toad GUI to force the permissions to be set. Once that was in place, my script worked fine.

    Thanks for everyone
Sign In or Register to comment.