Crystal Report with Oracle stored procedure (Ref Cursor)

[CrystalProgDev]

I have created a package with ref cursor for a crystal report development. I am able to run the package. but when I add that package to the Crystal Reports, it is not showing the fields. same thing is happening with the stored procedure. Can anybody suggest the solution for this problem?
My Stored procedure is something like this.
CREATE OR REPLACE PACKAGE GroupsPackage
AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE usp_SelectGroups (int intInputI IN ,Dresults_cursor IN OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY GroupsPackage
AS
PROCEDURE usp_SelectGroups (int intInputI IN ,results_cursor IN OUT CURSOR_TYPE)
AS
BEGIN
OPEN results_cursor FOR
SELECT GroupID, GroupName, GroupDescription, LastUpdateDate
FROM Groups
ORDER BY GroupName;
END;
END;
/

Thanks in advance

 

[Turkbear]

Hi,
I am not sure how well Crystal works with packages - try rewriting it as just a Stored Procedure ( not good Oracle programming technique, but it may help isolate where any issues are located)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CrystalProgDev]

Same problem with the stored procedure too. I tried both package and stored procedure.

 

[Turkbear]

Hi,
a couple of additional questions:
What version of CR and Oracle?
What connection type ( ODBC, ADO, native Oracle Server)
Have you had one of this type work before?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CrystalProgDev]

Crystal XI R2, Oracle 10g, OLEDB Connection. Yes I worked on stored procedure in SQL SERVER 2005 for few reports. The SQL server stored procedure that I have worked on are using temp tables with insert stmt. The Stored procedure that I am using now in oracle has ref cursor with select stmt.

 

[Turkbear]

Hi,
Just to see if it makes a difference, try it with the Native Oracle Server connection type.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
Also try defining the REF CURSOR type just as an OUT not an IN OUT ( I know that the docs specify IN OUT, but I have had problems in the past with that)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CrystalProgDev]

I tried both the ways as you mentioned. Still the same problem.
Any suggestions.

I really appreciate your help.

Thanks