Procedures with cursors

From: Bastian <btrompetter_at_googlemail.com>
Date: 27 Jun 2006 06:10:51 -0700
Message-ID: <1151413851.517139.260860@i40g2000cwc.googlegroups.com>

Hello,

I want to run a statement wich calls a procedure. The procedure should return a resultset.
I know that I have to use a cursor as a parameter for the procedure. But how do I have to define the cursor in the statement?

My Procedure looks like this:

PROCEDURE SP_SIM_SERVICES_REPORT (

                  D_DATEFROM IN date,
                  D_DATEUNTIL IN date,
                  N_CUSTOMERID IN number,
                  C_TVSTATION IN varchar2,
                  P_CURSOR IN OUT T_CURSOR)

IS
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   OPEN P_CURSOR FOR
   SELECT TRUNC (dom.d_closed, 'DD') AS d_day,

         TRUNC (dom.d_closed, 'MM') AS d_month, dom.c_shortnumber,
         dom.c_keyword, dom.c_service, dom.c_mandant_id,
         NVL (dom.c_group, 'noch keine Zuordnung möglich') AS c_group,
         dom.c_title, dom.f_price, dom.n_unitprice, dom.c_currency,
         SUM (dom.n_count) AS n_count,


         (dom.f_price * SUM (dom.n_count)) AS n_totalturnover,

         (dom.N_UNITPRICE * SUM (dom.n_count)) AS n_totalshare, NULL AS

dummy

    FROM (SELECT * FROM vw_report_sim_content simcont

           WHERE simcont.d_closed >= D_DATEFROM AND simcont.d_closed < D_DATEUNTIL

          UNION ALL
          SELECT * FROM vw_report_sim_content_web simcontweb
           WHERE simcontweb.d_closed >= D_DATEFROM AND
simcontweb.d_closed < D_DATEUNTIL
          UNION ALL
          SELECT * FROM vw_report_sim_subscription simsubscription
           WHERE simsubscription.d_closed >= D_DATEFROM AND
simsubscription.d_closed < D_DATEUNTIL) dom,
             statistic.vw_mandant mandant
WHERE mandant.C_MANDANT_ID = dom.C_MANDANT_ID
          AND mandant.C_PARENT_MANDANT_ID LIKE C_TVSTATION
GROUP BY TRUNC (dom.d_closed, 'DD'),
         TRUNC (dom.d_closed, 'MM'),
         dom.c_shortnumber,
         dom.c_keyword,
         dom.c_service,
         dom.c_mandant_id,
         dom.c_group,
         dom.c_title,
         dom.F_PRICE,
         dom.N_UNITPRICE,
         dom.c_currency
ORDER BY d_day,
         d_month,
         dom.c_shortnumber,
         dom.c_keyword,
         dom.c_service,
         dom.c_mandant_id,
         dom.c_group,
         dom.c_title,
         dom.f_price,
         dom.n_unitprice,
         dom.c_currency;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;

END SP_SIM_SERVICES_REPORT; And my script is this:

BEGIN
REPORTS.SP_SIM_SERVICES_REPORT ('01.05.2006', '01.06.2006', 61, 'PRO7', P_CURSOR);
END; But I dont't know how to define the cursor in the script.

Thanks for help.

Regards

Bastian Received on Tue Jun 27 2006 - 08:10:51 CDT