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

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.