Re: Procedures with cursors

Bastian wrote:
> 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

Look up SYS_REFCURSOR in the Supplied PL/SQL Packages and Types (9.2) manual. The manual name is slightly different in 10g. Also see the PL?SQL manual. You will also find an entry on the variable descriptor in the SQL manual.

p_cursor sys_refcursor;

HTH -- Mark D Powell -- Received on Tue Jun 27 2006 - 09:01:54 CDT