Using Oracle 8 stored procedures from Crystal Reports 6

Okay, okay, this is probably in a FAQ somewhere. But for the life of me, I can't find it. Please help by answering the question or pointing me to the correct FAQ.

(And I have RTFM but unfortunately Seagates' FM is not too F helpful.)

Thanks....!

Specs:
Crystal Reports ver 6.0.1.135
Oracle 8.01.05
Oracle ODBC driver SQORA32.DLL dated 2/2/99

I've built an Oracle procedure to populate a cursor variable. This is straight out of
http://community.seagatesoftware.com/communityCS/TechnicalPapers/scr_ora cle_storproc_accessreq.htm

The procedure is built with no errors. I've included the code and DESC of it below.

When I pull up the SP in Crystal, I see the two parms. I can successfully update both parms with data, but when I exit the definition, I get the dreaded 'Parameter number 2 is invalid." message. Then I get the "There are no fields in the file..." and "File integrity error" messageboxes.

According to the info in Seagates' tech paper, I can enter any value for the cursor variable parameter. But I see that this was written for Oracle 7.3...

<<<proc code starts here>>>

/* This statement will create a package object that will contain the defined cursor type. */
CREATE OR REPLACE PACKAGE cursor_types AS TYPE XRCur IS REF CURSOR RETURN dstg_stin_currency_exch_rates%ROWTYPE ; END;
/

CREATE OR REPLACE PROCEDURE sp_jvle_02 ( /* A parameter that you want to be passed to the function from Crystal. */
FromCD IN dstg_stin_currency_exch_rates.from_currency_cd%TYPE

/* The parameter is declared to have the packages type and is in IN OUT mode */
,exchcursor IN OUT cursor_types.XRCur

) AS
BEGIN /* Open the cursor that Crystal passes and assign it the query. This query's result is what Crystal will access. */ OPEN exchcursor FOR
SELECT *
FROM dstg_stin_currency_exch_rates
WHERE dstg_stin_currency_exch_rates.from_currency_cd = FromCD ;
END; /
<<<proc code ends here>>>

<<<proc desc starts here>>>

SQL> desc sp_jvle_02
PROCEDURE sp_jvle_02

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FROMCD                         VARCHAR2(3)             IN
 EXCHCURSOR                     REF CURSOR              IN/OUT
                                RECORD                  IN/OUT
     FROM_CURRENCY_CD           VARCHAR2(3)             IN/OUT
     TO_CURRENCY_CD             VARCHAR2(3)             IN/OUT
     MODIFIED_BY                VARCHAR2(30)            IN/OUT
     CURRRENCY_EXCH_EFF_DTM     DATE                    IN/OUT
     MODIFIED_DTM               DATE                    IN/OUT
     UPDATED_FLG                CHAR(1)                 IN/OUT
     CONVERSION_FACTOR_NBR      NUMBER(16,7)            IN/OUT
     ERROR_CD                   VARCHAR2(8)             IN/OUT
     ERROR_DES                  VARCHAR2(256)           IN/OUT

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 17 1999 - 00:00:00 CET