Call Oracle stored procedure which returns ref cursor in C#

In this post you will see how to use the .NET Managed Provider for Oracle to pass an input parameter to retrieve a Ref Cursor from an Oracle stored procedure

(Donwload Oracle Data Access Components) http://www.oracle.com/technetwork/topics/dotnet/downloads/net-downloads-160392.html

Procedure


CREATE OR REPLACE Procedure refSP(p_orders OUT sys_refcursor)
AS
BEGIN
  open p_orders for select * from orders;
END

CREATE OR REPLACE Procedure refSP(p_orders OUT sys_refcursor)

BEGIN

open p_orders for select * from orders;

END


        static void Main(string[] args)
        {

            try
            {
                String connString = "User Id=yourId;Password=yourPass;Data Source=oracle";
                OracleConnection conn = new OracleConnection(connString);
                conn.Open();


                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.CommandText = "refSP";
                OracleParameter oraP = new OracleParameter();
                oraP.OracleDbType = OracleDbType.RefCursor;
                oraP.Direction = System.Data.ParameterDirection.Output;

                cmd.Parameters.Add(oraP);


                OracleDataReader reader = cmd.ExecuteReader(); ;
                while (reader.Read())
                {
                    Console.WriteLine(reader.GetValue(0).ToString());
                    Console.WriteLine(reader.GetValue(1).ToString());
                }
                reader.Close();
            }
            catch (OracleException ex)
            {

            }

            Console.ReadLine();

        }

static void Main(string[] args)

{

try

{

String connString = "User Id=yourId;Password=yourPass;Data Source=oracle";

OracleConnection conn = new OracleConnection(connString);

conn.Open();

OracleCommand cmd = new OracleCommand();

cmd.Connection = conn;

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.CommandText = "refSP";

OracleParameter oraP = new OracleParameter();

oraP.OracleDbType = OracleDbType.RefCursor;

oraP.Direction = System.Data.ParameterDirection.Output;

cmd.Parameters.Add(oraP);

OracleDataReader reader = cmd.ExecuteReader(); ;

while (reader.Read())

{

Console.WriteLine(reader.GetValue(0).ToString());

Console.WriteLine(reader.GetValue(1).ToString());

}

reader.Close();

}

catch (OracleException ex)

{

}

Console.ReadLine();

}

Call Oracle stored procedure which returns ref cursor in C#

Like this:

Leave a Comment X

Like this:

You may also like

Leave a Comment X