Re: Crystal Report - Oracle Stored Procedure

Sorry, incomplete msg last time... here's the full text:

Using Oracle stored procs w/ Crystal involves multiple steps: First, you need to create a package containing your resultset record layout and a cursor variable. An example would be:

CREATE OR REPLACE PACKAGE CRpkg AS
  TYPE t_rec IS RECORD ( fname VARCHAR2(50) );   TYPE cur_Var IS REF CURSOR RETURN t_rec; END CRpkg;

Then, in your stored proc you pass the cursor var as IN/OUT, as follows:

CREATE OR REPLACE PROCEDURE myProc
  (p_CurVar IN OUT CRpkg.cur_Var ) <-- need this if your proc is external AS
BEGIN
  OPEN p_CurVar FOR
    SELECT firstname FROM emp;
END myProc;

Crystal apparently fetches the resultset itself, which is why p_Cur needs to be defined as IN/OUT... The final twist is that you must use the Crystal native driver for Oracle in order for all of this to work. Now you should be able to point to the proc in report designer and work with the result fields just like you were retrieving directly from a table or query...

I know that the above works well with CR 6, but haven't tried it w/ version 5. If it doesn't work and you're absolutely stuck with version 5, then perhaps you'd be better off using a parameterized query instead of a stored procedure.

Good luck!!
azl

Alan Laya wrote in message ...
>In your stored proc you pass the cursor var as IN/OUT, as follows:
>
>CREATE OR REPLACE PROCEDURE myProc
> (p_CurVar IN OUT cur_Var )
>AS
>BEGIN
> OPEN p_CurVar FOR
> SELECT firstname FROM emp;
>END myProc;
>
>Crystal apparently fetches the resultset itself, which is why p_Cur needs
 to
>be defined as IN/OUT... The final twist is that you must use the Crystal
>native driver for Oracle in order for all of this to work. Now you should
>be able to point to the proc in report designer and work with the result
>fields just like you were retrieving directly from a table or query...
>
>I know that the above works well with CR 6, but haven't tried it w/ version
 

>5. If it doesn't work and you're absolutely stuck with version 5, then
>perhaps you'd be better off using a parameterized query instead of a stored
>procedure.
>
>Good luck!!
>azl
>
>mateev_at_geocities.com wrote in message <6snu24$g9k$1_at_nnrp1.dejanews.com>...
>>I work with Crystal Report 5 and Oracle 7.3.
>>
>>Can link I Crystal Report with Oracle Stored Procedure (Function)?
>>I can't receive data from Oracle Stored Procedure and Function.
>>
>>Can anyone help me?
>>
>>Georgy Mateev
>>mateev_at_geocities.com
>>
>>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>>
>
>
Received on Sat Sep 12 1998 - 00:00:00 CDT