How to execute Oracle stored procedure which has Input and Output parameter as CommandText in C#.net

User_U2UBU

Dear Team,

I have a oracle stored procedure as below. This Oracle stored procedure accepts Input parameter and output parameters.

create or replace NONEDITIONABLE PROCEDURE GetDetailsWithOutputParams_Multiple(id IN INTEGER,valuepath OUT VARCHAR2,valueNumeric OUT NUMBER)

AS 

BEGIN

for c in (SELECT k.VALUEPATH as VALUEPATH,k.VALUENUMERIC as VALUENUMERIC INTO valuepath,valueNumeric FROM KPI k where k.ID=id)

loop

valuepath:=c.VALUEPATH;

valueNumeric:=c.VALUENUMERIC;

end loop;

END;

Now I am calling this procedure using C#.net as below:

using (OracleConnection conn = new OracleConnection(_connectionString))

      {

        await conn.OpenAsync();

        string str = @"DECLARE ID NUMBER;VALUEPATH VARCHAR2(200);VALUENUMERIC NUMBER;BEGIN GETDETAILSWITHOUTPUTPARAMS_MULTIPLE(ID, VALUEPATH, VALUENUMERIC); :VALUEPATH := VALUEPATH; :VALUENUMERIC := VALUENUMERIC;END;";

        OracleCommand objCmd = new OracleCommand(str, conn);

        objCmd.CommandType = CommandType.Text;

        OracleParameter parameter = null;

        parameter = new OracleParameter("ID", OracleDbType.Decimal, ParameterDirection.Input);

        parameter.Value = 2;

        objCmd.Parameters.Add(parameter);

        parameter = new OracleParameter("VALUEPATH", OracleDbType.Varchar2, ParameterDirection.Output);

        parameter.Size = 200;

        objCmd.Parameters.Add(parameter);

        parameter = new OracleParameter("VALUENUMERIC", OracleDbType.Decimal, ParameterDirection.Output);

        objCmd.Parameters.Add(parameter);

        string str1, str2;

        using (OracleDataReader objReader = objCmd.ExecuteReader())

        {

          str1 = objCmd.Parameters["VALUEPATH"].Value.ToString();

          str2 = objCmd.Parameters["VALUENUMERIC"].Value.ToString();

          objReader.Close();

        }

      }

Getting Error as below:

Please advise, how to execute oracle stored procedure as a command text .

Hpaiss

Hi,

You should executenosql instead of reader, and you also don't need the entire 'DECLARE....' block.

As a side comment, if you plan to get only one row of data in the stored procedure you should use 'select ... into ...' instead of a loop. There are some large discussions about that, but it is assumed better practice :)

Here is a little example I've found for you.

using (OracleConnection connection = new OracleConnection("ConnectionString"))
    using (OracleCommand command = new OracleCommand("ProcName", connection))             
    {
          command.CommandType = CommandType.StoredProcedure;
          command.Parameters.Add("ParameterName", OracleDbType.Varchar2).Value = "Your Data Here";
          command.Parameters.Add("SomeOutVar", OracleDbType.Varchar2, 120);
          command.Parameters["return_out"].Direction = ParameterDirection.Output;
          command.Parameters.Add("SomeOutVar1", OracleDbType.Varchar2, 120);
          command.Parameters["return_out2"].Direction = ParameterDirection.Output;
          connection.Open();
          command.ExecuteNonQuery();
          string SomeOutVar = command.Parameters["SomeOutVar"].Value.ToString();
          string SomeOutVar1 = command.Parameters["SomeOutVar1"].Value.ToString();
    }

Regards,

Hadar

User_U2UBU

Hello @Hpaiss,

Your code is very much useful.

Just one point to check with you is that, in your code block last lines your reading output data through output parameters.So my question, if output parameter is "refcursor" type then how to read the output data?

I have tried below format. But looks this always returns the empty/null ouput.

Var SomeOut = command.Parameters["refcursorParam"].Value;

Hpaiss

Hi,

Here is an example for handling refcursor:

 using (OracleConnection cn = new OracleConnection(DatabaseHelper.GetConnectionString()))
    {
        OracleDataAdapter da = new OracleDataAdapter();
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = cn;
        cmd.InitialLONGFetchSize = 1000;
        cmd.CommandText = DatabaseHelper.GetDBOwner() + "PKG_COLLECTION.CSP_COLLECTION_HDR_SELECT";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("PUNIT", OracleDbType.Char).Value = unit;
        cmd.Parameters.Add("POFFICE", OracleDbType.Char).Value = office;
        cmd.Parameters.Add("PRECEIPT_NBR", OracleDbType.Int32).Value = receiptno;
        cmd.Parameters.Add("T_CURSOR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

        da.SelectCommand = cmd;
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

Regards,

Hadar