Calling Oracle Procedure which returns Ref Cursor AS output PARAMETER in ado.net

Calling Oracle Procedure which returns Ref Cursor AS output PARAMETER in ado.net

Step 1)  Create a Oracle Procedure which returns Ref cursor.

Creating a Procedure which returns Ref cursors as Output Parameter.

   CREATE OR REPLACE Procedure REFSP(p_orders OUT sys_refcursor)
  as
  begin
  open p_orders for select * from pearson.orders;

 end;

 

Step 2)   Calling  Oracle Procedure with ouput param IS Ref Cursor in ADO.NET

               Using   ExecuteReader.

        protected void  CallingOracleProcedure ()

        {
            try{
                String connString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
                OracleConnection conn = new OracleConnection(connString);
                conn.Open();


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

                cmd.CommandText = "scott.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());
                       //repeat for remaining  fields.
                    }
                reader.Close();
}

            catch (OracleException ex)
            {

            }

        }

Method 2: Calling  Oracle Procedure with Return Type as Ref Cursor in ADO.NET

               Using   OracleDataAdapter.

        protected void CallingOracleProcedure ()
        {
            try{                String connString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
                OracleConnection conn = new OracleConnection(connString);
                conn.Open();


                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
              
                cmd.CommandText = "scott.refsp";
                OracleParameter oraP = new OracleParameter();
                oraP.OracleDbType = OracleDbType.RefCursor;
                oraP.Direction = System.Data.ParameterDirection.Output;
                cmd.Parameters.Add(oraP);

                 OracleDataAdapter ad = new OracleDataAdapter(cmd);
                OracleCommandBuilder cb = new OracleCommandBuilder(ad);
               
                System.Data.DataSet ds = new System.Data.DataSet();
                ad.Fill(ds);

            }
            catch (OracleException ex)
            {

            }

        }

Tags:Calling Oracle Procedure which returns Ref Cursor in ado.net,Calling Oracle Procedure which returns Ref Cursor in ado.net using ExecuteReader,Calling Oracle Procedure which returns Ref Cursor in ado.net using OracleDataAdapter, Calling Oracle Procedure s in ADO.NET,Executing Oracle Procedure s in ado.net.oracle 11g