Calling Oracle function which returns Ref Cursor in ado.net

Calling Oracle function which returns Ref Cursor in ado.net

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

Creating a Function which returns Ref cursors.

CREATE OR REPLACE function reff1
return sys_refcursor
is
v_t sys_refcursor;
begin
open v_t for select * from pearson.orders;
return v_t;
end;
 

 

Step 2)   Calling  Oracle Function with Return Type as Ref Cursor in ADO.NET

               Using   ExecuteReader.

        protected void  CallingOracleFunction()

        {
            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.reff1";
                OracleParameter oraP = new OracleParameter();
                oraP.OracleDbType = OracleDbType.RefCursor;
                oraP.Direction = System.Data.ParameterDirection.ReturnValue;
                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 Function with Return Type as Ref Cursor in ADO.NET

               Using   OracleDataAdapter.

        protected void CallingOracleFunction()
        {
            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.reff1";

                OracleParameter oraP = new OracleParameter();
                oraP.OracleDbType = OracleDbType.RefCursor;
                oraP.Direction = System.Data.ParameterDirection.ReturnValue;
                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 function which returns Ref Cursor in ado.net,Calling Oracle function which returns Ref Cursor in ado.net using ExecuteReader,Calling Oracle function which returns Ref Cursor in ado.net using OracleDataAdapter, Calling Oracle functions in ADO.NET,Executing Oracle functions in ado.net.