Executing an Oracle Stored Procedure


As an example
Consider an Oracle Stored procedure with the following parameters.
















































Procedure Name StoredProc1

Argument Name

Type

In/Out Default?
 

param1

VARCHAR2

IN
 

param2

VARCHAR2

IN
 

param3

VARCHAR2

IN
 

param4

VARCHAR2

IN
 

param5

VARCHAR2

IN
 

param6

REF CURSOR

OUT
 

param7

REF CURSOR

OUT


Right click on our website and select add reference. We have to add System.Data.OracleClient.
 
Add the following in the code
usingSystem.Collections.Generic;
usingSystem.Data.OracleClient;
privateDataSet GetData()
{
    DataSet ds = null;
    OracleConnection conn = null;
    try
    {
stringConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;         
            conn = new OracleConnection(ConnectionString);
            conn.Open();
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = "StoredProc1";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();
               
            List InputParams = new List();
            InputParams.Add("param1");
            InputParams.Add("param2");
            InputParams.Add("param3");
            InputParams.Add("param4");
            InputParams.Add("param5");
 
            cmd.Parameters.Add(InputParams[0],
            OracleType.VarChar).Direction = ParameterDirection.Input;
            cmd.Parameters[InputParams[0]].Value = "007";
            //all other parameters set to null
            for (int i = 1; i < InputParams.Count; i++)
            {
                OracleParameter op = new OracleParameter();
                op.ParameterName = InputParams[i];
                op.Direction = ParameterDirection.Input;
                op.Value = DBNull.Value;
                cmd.Parameters.Add(op);
            }             
            //output parameter which is a cursor
            List outputParms = new List();
            outputParms.Add("param6");
            outputParms.Add("param7");              
            for (int c = 0; c < outputParms.Count; c++)
                        cmd.Parameters.Add(outputParms[c],
                                    OracleType.Cursor).Direction =
                                    ParameterDirection.Output;
            ds = new DataSet();
            OracleDataAdapter oda = new OracleDataAdapter(cmd);
            oda.Fill(ds);
            conn.Close();
    }
    catch (OracleException se)
    {
                //if an error occurs we redirect to error page where we
            display the error message
          string errMessage = se.Message;
          errMessage = errMessage.Replace("\n", " ");
          Response.Redirect("../Error.aspx?errorCode=" +
          se.ErrorCode + "&details=" + errMessage);
     }
     finally
     {
          if (conn != null)
              conn.Close();
      }     
      return ds;
}