Calling an Oracle Stored Procedure using C#

Bufnita Ciumec

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;
/

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

Thanks for any and all help y'all provide.

B.C.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bufnita Ciumec18y ago
Accepted answer

Actually, it's the same SP, I just forgot to abbreviate one of the instances.

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’s input.

B.C.
+1
Ryan Alford18y ago
also, looking at your code, you are referencing "RULES_ENGINE.OAT_Assignments" in code, but you posted "R_E.Assignments" as the stored procedure.....two different stored procedures.
+1
Ryan Alford18y ago
using System;
using System.Data;
using System.Web;
using System.Collections;
using System.ComponentModel;
using System.Reflection;
using System.Threading;
using System.Diagnostics;
using System.Data.OracleClient;

                OracleConnection conn = new OracleConnection(connectionstring);

                conn.Open();
                OracleCommand cmd = new OracleCommand("agy_label_print_prc", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter prm1 = new OracleParameter("v_template", OracleType.VarChar);
                prm1.Direction = ParameterDirection.Input;
                prm1.Value = template;
                cmd.Parameters.Add(prm1);

                OracleParameter prm2 = new OracleParameter("v_printer_name", OracleType.VarChar);
                prm2.Direction = ParameterDirection.Input;
                prm2.Value = printer;
                cmd.Parameters.Add(prm2);

                OracleParameter prm3 = new OracleParameter("v_copies", OracleType.Number);
                prm3.Direction = ParameterDirection.Input;
                prm3.Value = copies;
                cmd.Parameters.Add(prm3);

                OracleParameter prm4 = new OracleParameter("v_data", OracleType.VarChar);
                prm4.Direction = ParameterDirection.Input;
                prm4.Value = data;
                cmd.Parameters.Add(prm4);

                OracleParameter prm5 = new OracleParameter("v_product_code", OracleType.VarChar);
                prm5.Direction = ParameterDirection.Input;
                prm5.Value = product_code;
                cmd.Parameters.Add(prm5);

                OracleParameter prm6 = new OracleParameter("v_gross_weight", OracleType.Number);
                prm6.Direction = ParameterDirection.Input;
                prm6.Value = gross_weight;
                cmd.Parameters.Add(prm6);

                OracleParameter prm7 = new OracleParameter("v_badge_id", OracleType.VarChar);
                prm7.Direction = ParameterDirection.Input;
                prm7.Value = badge_id;
                cmd.Parameters.Add(prm7);

                OracleParameter prm8 = new OracleParameter("v_plant_code", OracleType.VarChar);
                prm8.Direction = ParameterDirection.Input;
                prm8.Value = plant_code;
                cmd.Parameters.Add(prm8);

                cmd.ExecuteNonQuery();
                conn.Close();

this is used in a web service so the using statements maybe a little different than yours. as long as you have the System.Data.OracleClient, then you should be ok.
+1