If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > Crystal Reports > Using Oracle Package as Data Source for Report

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2008
Posts: 3
Red face 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 ?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Oct 2008
Posts: 2
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??
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2008
Posts: 3
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2008
Posts: 3
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".

Last edited by Robert Coffield; 01-06-09 at 19:11.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On