Re: Help urgent! Cursor prblem

From: Hugh Pendry <hpendry_at_trinitech.nospam.co.uk>
Date: Fri, 25 Feb 2000 11:30:16 -0000
Message-ID: <895p49$v86$1@trinitech.demon.co.uk>

I don't know about Crystal reports but this should definately work, you could test it by using SQL worksheet with something similar to the following...

set serveroutput on
declare

    retData pkCursorTypes.rcurGeneric; begin

    p_rpt103p1(retData, sytsdate, sysdate, 'test', 'test'); end;

to view the data returned I would not use a generic cursor but one with predefined fields which could be selected for output.

e.g.

type recEmployee is record (

    employee emp.empDesc%rowtype,
    boss boss.bossD esc%rowtype );
type rcurEmployees is ref cursor return recEmployee;

then declare the procedure with this ref cursor type and use the following in SQL worksheet....

set serveroutput on

declare

    retData pkCursorTypes.rcurEmployees;     emp pkCursorTypes.recEmployee;

begin

    p_rpt103p1(retData, sytsdate, sysdate, 'test', 'test');     dbms_output.put_line( 'Employees:' );

    loop

        fetch retData into emp;
        exit when retData%notfound;
        dbms_output.put_line( 'Employee: ' || emp.employee || '    Boss: '

emp.boss );

    end loop;

    dbms_output.put_line( 'Done' );

    if retData%is;Open then

        close retData
    end if;
end;

larchevv_at_my-deja.com wrote in message <893oaq$vbn$1_at_nnrp1.deja.com>...
>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 Fri Feb 25 2000 - 05:30:16 CST