In .NET, How to execute this Oracle PL/SQL Procedure?

My procedure is declare here :

create or replace
PACKAGE MYPKG
IS
PROCEDURE MYPROCEDURE( sNom IN VARCHAR2, sValeur OUT VARCHAR2, sCommentaire OUT VARCHAR2, sRetour OUT VARCHAR2, sMsgRetour OUT VARCHAR2);
END;

The execution is Ok with SQL Developer.

I try to execute this procedure in C# :

OracleCommand cmd = new OracleCommand("MYPKG.MYPROCEDURE", OraCon);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter name = new OracleParameter("sNom", OracleType.VarChar);
name.Value = "CG";
cmd.Parameters.Add(name);
OracleParameter valeur = new OracleParameter("sValeur", OracleType.VarChar);
cmd.Parameters.Add(valeur);
OracleParameter commentaire = new OracleParameter("sCommentaire", OracleType.VarChar);
cmd.Parameters.Add(commentaire);
OracleParameter retour = new OracleParameter("sRetour", OracleType.VarChar);
cmd.Parameters.Add(retour);
OracleParameter msgRetour = new OracleParameter("sMsgRetour", OracleType.VarChar);
cmd.Parameters.Add(msgRetour);
using (OracleDataReader row = cmd.ExecuteReader())
{ while (row.Read()) { Console.WriteLine(row.GetValue(0)); }
}

I have the error "Wrong Parameter". How execute the PL/SQL procedure ?

3 Answers

This is how I would approach it.
NOTE: I have compiled this code, but I have not tested it against a database, although I have lots of similar code that works fine.

Also note: you are not getting back a collection, so you should really be using OraCmd.ExecuteNonQuery rather than OraCmd.ExecuteReader

using System.Configuration;
using System.Data;
using Oracle.DataAccess.Client; namespace Testing { public class OracleCommandTest { string m_strConnectionsString = string.Empty; //Constructor public OracleCommandTest() { //Get the connection string from the app.config m_strConnectionsString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString; //if it is a web app, then use this call instead //m_strConnectionsString = WebConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString; } public void getData(string sNom, out string sValeur, out string sCommentaire, out string sRetour, out string sMsgRetour) { OracleConnection ora_conn = new OracleConnection(m_strConnectionsString); try { ora_conn.Open(); /*********************Oracle Command**********************************************************************/ OracleCommand ora_cmd = new OracleCommand("MYPKG.MYPROCEDURE", ora_conn); ora_cmd.BindByName = true; ora_cmd.CommandType = CommandType.StoredProcedure; ora_cmd.Parameters.Add("sNom", OracleDbType.Varchar2, sNom, ParameterDirection.Input); ora_cmd.Parameters.Add("sValeur", OracleDbType.Varchar2, ParameterDirection.Output); ora_cmd.Parameters.Add("sCommentaire", OracleDbType.Varchar2, ParameterDirection.Output); ora_cmd.Parameters.Add("sRetour", OracleDbType.Varchar2, ParameterDirection.Output); ora_cmd.Parameters.Add("sMsgRetour", OracleDbType.Varchar2, ParameterDirection.Output); /*********************Oracle Command**********************************************************************/ ora_cmd.ExecuteNonQuery(); //Now get the values output by the stored procedure sValeur = ora_cmd.Parameters["sValeur"].Value.ToString(); sCommentaire = ora_cmd.Parameters["sCommentaire"].Value.ToString(); sRetour = ora_cmd.Parameters["sRetour"].Value.ToString(); sMsgRetour = ora_cmd.Parameters["sMsgRetour"].Value.ToString(); } //catch (Exception ex) //{ // WebErrorHandling.WriteToEventLog("PRVEquipment_DAL.getPRVEquipment", ex); //} finally { if (ora_conn.State == ConnectionState.Open) { ora_conn.Close(); } } } } }

Hope this helps
Harvey Sather

You forgot to set the Direction for the parameters.

You have to set ParameterDirection.Output since you are expecting values to be returned from the stored procedure else its by default considered as input parameter and so the error

Use OracleType enumeration for the parameters types. More info here
Also you should set the Direction of the parameters.
Example