Unanswered: Using Oracle Package as Data Source for Report
(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 ?
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.
Last edited by Robert Coffield; 01-06-09 at 18:16.
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".
Last edited by Robert Coffield; 01-06-09 at 19:11.