Returning multiple record sets from Oracle

• Depending on your results set you need to use a number of out parameter cursor in your store procedure.
• In demo store procedure 2 out parameter cursor used.
• When you call store procedure, you will get the results set in the dataset object.

Step 1:- Create Store Procedure

CREATE OR REPLACE PROCEDURE USP_TEST(P_OUT1 OUT PKG_NONPINS.CUR_REF,

                                     P_OUT2 OUT PKG_NONPINS.CUR_REF) AS

  Query VARCHAR2(1000);

BEGIN

  Query := ' select trunc(sysdate) "sysDate" ,

             trunc(current_date) "curDate"

             from dual';

  OPEN P_OUT1 FOR Query;

  --OR

  OPEN P_OUT2 FOR ' select trunc(sysdate) "sysDate" ,

             trunc(current_date) "curDate"

             from dual';

END;

Step 2:- Call Store Procedure

OracleConnection con = new OracleConnection(“Connection String”);

con.Open();

OracleCommand cmd = new OracleCommand("USP_TEST", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("P_OUT1", OracleType.Cursor).Direction = ParameterDirection.Output;

cmd.Parameters.Add("P_OUT2", OracleType.Cursor).Direction = ParameterDirection.Output;

OracleDataAdapter da = new OracleDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

con.Close();