Reference Cursors

[devtha]

Is anyone familiar with Reference Cursors.?
I need use it with Crystal reports

 

[Guest_imported]

I am using Oracle 8i and Crystal ver 8.0.

Assuming you are linking Crystal Report to a Stor Proc
You need to create a generic PACKAGE as follows...
*******************************************
CREATE OR REPLACE PACKAGE mySchema.SP_GENERIC_REFCURSOR_PKG
AS
TYPE RT1 IS REF CURSOR;
END;
*******************************************
Then your Stor Proc which will be linked to Crystal
should be written as follows...
*******************************************
CREATE OR REPLACE PROCEDURE mySchema.sp_SPNAME
(inp_One number,
inp_Two varchar2,
...any other params you may have...,
RC1 OUT SP_GENERIC_REFCURSOR_PKG.RT1)
AS
BEGIN
OPEN RC1 FOR
Select f1, f2, f3,... From t1,t2,...
Where ....{joins & conditions}...;
End;
*******************************************
Alternatively, you can link to a view or table directly without any need for a REF CURSOR. But may need a stor proc
if you have to feed in parameter values for your WHERE
clause selection criteria.
********************** end **********************