Report based on Stored Procedure thru Ref Cursor

[iceicebaby]

I am using CR 8.5 and Oracle 8. My Report is based on a stored procedure and it doesn't get the fields when I try to create it. Earlier, I've created many Reports based on Stored Procedures and they all work fine. Can't figure out the problem.Any Help is highly appreciated.
I'm just giving the relevant section of my Stored Proc below:

Create Or Replace procedure prod_mgmt5(Test_Cursor IN OUT Test_Package.Test_Type,Monthno IN Number,YearNo IN Number,prod_type_gp IN Varchar2) AS  
Cursor Ban_cursor(MBU1 in Varchar2,MBU2 in Varchar2,MBU3 in Varchar2,MBU4 in Varchar2,MBU5 in Varchar2,MBU6 in Varchar2) is
	Select ban
	From Cust_Data_Crossref 
	Where Data_value IN (MBU1,MBU2,MBU3,MBU4,MBU5,MBU6)
	and Item_type = 'MBU';
	Ban_Val Ban_cursor%RowType;
Begin
For Ban_Val in Ban_cursor(MBU1_val,MBU2_val,MBU3_val,MBU4_val,MBU5_val,MBU6_val)
Loop
 --Assign values
End Loop
For i in 1..22 loop
		Insert Into pmr5_Tab Values(Unitst(i),Revenuet(i));
End Loop;
Commit;
Open Test_Cursor For
	Select * From pmr5_Tab;
End;

 

[synapsevampire]

The insert might be a problem, but the thing that seems odd to me is the:

Open Test_Cursor For
Select * From pmr5_Tab;

This doesn't select rows, it opens a cursor.

Try just:

Select * From pmr5_Tab;

Here's the CR whitepaper on Oracle SPs:

http://support.businessobjects.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf.asp

-k

 

[iceicebaby]

This is what we have to do. We have to open a cursor which holds the result set from the procedure and pass this cursor to Crystal. This has worked in the past for other Stored Procedures that I've created. The problem seems to be somewhere else.

 

[lyanch]

First question is.. is there too many "end loops"? Asside from that...

What does your output look like if you run this from just a SQL window. Are there any other odd returns coming back? I would check to see what it looks like vs other oracle sp's you use.

I *think* the problem might be from the creation of the first cursor (not your output cursor). You may need to create it outside the SP..

Last but not least, please post the answer if you find it. I too do many oracle SP's and would like to add this to my reference.


Lisa