return ref cursor from oracle procedure

return ref cursor from oracle procedure or return table using ref cursor from oracle procedure

//First u will create the package for return is REF Cursor

create or replace

package testPkg as type empCur is REF Cursor;

end testPkg;

//then create procedure

create or replace

PROCEDURE CAMPAIGNSALESANALYSIS (selectcolunm in varchar2,salestablename in varchar2,groupbycolunmname in varchar2,CircleCode in varchar2,Datecondition in varchar2,productCategory in varchar2,c1 out testPkg.empCur)

is

 stmt  VARCHAR2(2000);

BEGIN

      stmt :=  'SELECT '|| selectcolunm ||' FROM (Select cpsk.CIRCLE_CODE,dtr.circle_name,dtr.zone_name,dtr.dtr_code,dtr.pos_code,cpsk.product_category,cpsk.product_name,cpsk.prim_date,cpsk.ordered_qty

               from  gj_dtr DTR  inner join '|| salestablename ||'CPSK on dtr.dtr_code=cpsk.dtr_code) where UPPER(circle_code)='|| CircleCode ||'

               and UPPER(circle_name)=''GUJARAT''  and PRODUCT_CATEGORY IN ('||productCategory||') AND '||Datecondition||' '|| groupbycolunmname ||' order by Quantity Desc';

     

              OPEN c1 FOR stmt;

           

END CAMPAIGNSALESANALYSIS ;

//Call this procedure from c#

OracleParameter param1 = new OracleParameter();

            param1.ParameterName = "c1";

            param1.OracleType = OracleType.Cursor;

            param1.Direction = ParameterDirection.Output;

            OracleParameter param2 = new OracleParameter();

            param2.ParameterName = "selectcolunm";

            param2.OracleType = OracleType.VarChar;

            param2.Direction = ParameterDirection.Input;

            param2.Value = "circle_name, zone_name,Sum(ordered_qty) Quantity ";

            OracleParameter param3 = new OracleParameter();

            param3.ParameterName = "salestablename";

            param3.OracleType = OracleType.VarChar;          

            param3.Direction = ParameterDirection.Input;

            param3.Value = "chl_prim_sales_kpi ";

            OracleParameter param4 = new OracleParameter();

            param4.ParameterName = "groupbycolunmname";

            param4.OracleType = OracleType.VarChar;    

            param4.Direction = ParameterDirection.Input;

            param4.Value = "group by circle_name,zone_name ";

            OracleParameter param5 = new OracleParameter();

            param5.ParameterName = "CircleCode";

            param5.OracleType = OracleType.VarChar;       

            param5.Direction = ParameterDirection.Input;

            param5.Value = "'GJ'";

            OracleParameter param6 = new OracleParameter();

            param6.ParameterName = "Datecondition";      

            param6.OracleType = OracleType.VarChar;

            param6.Direction = ParameterDirection.Input;

            param6.Value = "PRIM_DATE BETWEEN TO_DATE('09/01/2011',  'MM/DD/YYYY') AND TO_DATE('10/01/2011', 'MM/DD/YYYY') ";

            OracleParameter param7 = new OracleParameter();

            param7.ParameterName = "productCategory";

            param7.OracleType = OracleType.VarChar;         

            param7.Direction = ParameterDirection.Input;

            param7.Value = "'RCV','SUK'";

            string strconn = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

            OracleConnection conn = new OracleConnection(strconn);

            OracleParameter[] oraParams = new OracleParameter[] { param1,param2,param3,param4,param5,param6,param7 };

            OracleCommand cmd = new OracleCommand("CAMPAIGNSALESANALYSIS", conn);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddRange(oraParams);

            conn.Open();

            OracleDataAdapter da = new OracleDataAdapter(cmd);

            DataTable dt = new DataTable();

            da.Fill(dt);

Read more ...