Re: Help urgent! Cursor prblem

This should work :

CREATE OR REPLACE PACKAGE TestCursor IS

   TYPE typePenSal IS REF CURSOR;
   FUNCTION PenSal(cClnt VARCHAR2 , cMkey VARCHAR2 ) RETURN typePenSal; END;
/
CREATE OR REPLACE PACKAGE BODY TestCursor IS FUNCTION PenSal(cClnt VARCHAR2 , cMkey VARCHAR2 ) RETURN typePenSal AS

   curRtn typePenSal;
   BEGIN
      OPEN curRtn FOR SELECT * FROM MEMBER_SALARY WHERE CLNT=cClnt AND MKEY=cMkey;

• Change cursor definition here RETURN curRtn; END; END; /

SQL> var curTest REFCURSOR;
SQL> Begin
  2 :curTest := TestCursor.PenSal('0001','125ACK314');   3 End;
  4 /

PL/SQL procedure successfully completed.

SQL> print curTest;

As for Crystal: make sure that ODBC driver you want to use supports REF CURSOR... Thomas

<vlarchev_at_eyewire.com> wrote in message news: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 - 15:13:35 CST