(a) I am trying to use an Oracle Package (Stored Procedure) with a weak cursor as the data source for a Crystal Report.

Weak Cursor: Letting "Dynamic SQL" determines which "Fiscal Year Table" is used to create the cursor.

(My stored procedure works with a "strong cursor" where the the tables are hard coded in the "Dynamic SQL" and I feed parameters into the Dynamic SQL "Where Clause".)

(b) My Problem:
I can select the package/procedure as my data source, but the Crystal Reports "Field Explore" does not display any Database Fields. When I try to run "Verify Database" in Crystal Reports - Crystal locks-up and runs forever.

Has anyone successfully used an Oracle Package as input to a Crystal Report ? Has anyone tried using a "weak cursor" or Oracle's SYS_REFCURSOR ?

Hi Robert,

i have used a procedure inside a package as a data source for a report . the procedure's OUT cursor is SYS_REFCURSOR.

But i have not tried using a weak cursor u have mentioned. can u eloborate on that??

SYS_REFCURSOR is a "weak cursor" because it does not specify the columns within the "out" cursor. It gives you great flexibility unless you are trying to use the "out" cursor with Crystal Report 11. Crystal Reports want to know exactly how many columns and of what type (and please specify the column names).

The problem I have is the column names will remain the same, but the table names will change depending on which fiscal year I am reporting on. Unfortunately, changing the table names is treated the same as changing the structure of the "out" cursor which
Oracle does not permit.

I found a solution to my problem by redefining the problem.

I create an Oracle "Global Temporary Table" and then modified my stored procedure (package) to use the "Temporary Table".

(a) MY store procedure now uses dynamic sql to populate the temporary table using an "insert into table" statement with a subquery.

EXECUTE IMMEDIATE 'INSERT INTO TEMP_TABLE(...)
SELECT *
FROM ' || LV_GL001M_TBL || 'GL,
' || LV_GL101T_TBL || 'TRANS

The real (current) table names are in the local variables (LV_GL001M_TBL and LV_GL101T_TBL). The contents of the local variables
change depending on the input parameter "Fiscal_Year".

(B) After populating the temporary table, my stored procedure finishes by using a "strong" cursor to extract the contents of the temporary table and returns the record set to Crystal Reports

Specification of a strong cursor based on the Global Temporary Table
TYPE TEMP_TABLE_TYPE IS REF CURSOR RETURN TEMP_TABLE%ROWTYPE;

The Procedure's Input/Output Parameters include
CURSOR_OUT IN OUT TEMP_TABLE_TYPE,
FISCAL_YEAR IN VARCHAR2,

Extract the contents of the Global Temporary Table with a simple query and a dynamic cursor.
BEGIN

OPEN CURSOR_OUT FOR
SELECT *
FROM TEMP_TABLE

END

---------------------------------------------------------------

Crystal Reports is happy because it has a strong cursor (with a consistent record structure) to work with and the stored procedure is able to change the tables used to populate the temporary table based on the input parameter "Fiscal_Year".