I tried this solution too... I did everything you said but it doesn't
work. the package and the procedure compile but and I execute, I have
always an error message. The messages change depend of the tool I
use. It is either cursor not defined or wrong number of argument with
Crystal Reports. I'm driving myself crazy!
In article <892ubf$fkq$1_at_trinitech.demon.co.uk>,
"Hugh Pendry" <hpendry_at_trinitech.nospam.co.uk> wrote:
> 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)
> as
>
> 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.
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Feb 24 2000 - 11:04:30 CST
