Re: Help urgent! Cursor prblem

This may not be the only way but....

You need to declare a type for your ref cursor in a package
e.g.

CREATE OR REPLACE
 PACKAGE pk_cursorTypes
is
type rcurGeneric is ref cursor;
type rcurEmployees is ref cursor return employee%rowtype; end pk_cursorTypes;

Then declare the ref cursor as the required ref cursor type

e.g.

create or replace procedure p_rpt103p1
(

    pc_rowset in out pk_cursorTypes.rcurGeneric,
    pd_startdate in date ,
    pd_enddate in date,
    ps_system varchar,
    ps_locale varchar)

vlarchev_at_eyewire.com wrote in message <891g8o$cfq$1_at_nnrp1.deja.com>...
>Hi,
>
>I just want to create a procedure with in out parameter ref cursor.
>The problem that the cursor identifier is not recognize. What is wrong?:
>
>Thank you
>
>(1):PLS-00201: identifier 'CURSOR' must be declared
>
>here is the code
>
>
>create or replace procedure p_rpt103p1 (pc_rowset in out ref cursor,
>pd_startdate in date , pd_enddate in date, ps_system varchar, ps_locale
>varchar)
>as
>
>begin
>
>open pc_rowset for SELECT
> SALES_FACT."QUANTITY_SOLD", SALES_FACT."US_UNIT_LIST_PRICE",
>SALES_FACT."US_DISCOUNTS",
> DATE_DIM."DATE_DESC",
> PRODUCT_DIM."NAME", PRODUCT_DIM."PRODUCT_ID",
>PRODUCT_DIM."VOLUME_NAME",
> SALES_SYSTEM_DIM."SYSTEM_TYPE", SALES_SYSTEM_DIM."LOCALE_NAME"
>FROM
> DW.SALES_SUMMARY SALES_FACT,
> DW.PRODUCT_DIM PRODUCT_DIM,
> DW.SALES_SYSTEM_DIM SALES_SYSTEM_DIM,
> DW.DATE_DIM DATE_DIM
>WHERE
> SALES_FACT."DATE_KEY" = DATE_DIM."DATE_KEY" AND
> SALES_FACT."PRODUCT_KEY" = PRODUCT_DIM."PRODUCT_KEY" AND
> SALES_FACT."SALES_SYSTEM_KEY" = SALES_SYSTEM_DIM."SALES_SYSTEM_KEY"
>and
> date_dim <= pd_startdate and
> date_dim >= pd_enddate and
> sales_system.system_type = ps_system and
> sales_system.locale_name = ps_locale;
>
> end;
>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Thu Feb 24 2000 - 03:41:02 CST