Oracle Stored Procedure and Crystal Reports - Need Help

[azaveri]

Hi guys,

I'm new to Oracle; but not Crystal Reports. In the past, we've used SQL Server to create stored procedures and then called them into Crystal Reports. No problem there. We are now transitioning to Oracle and I needed some help.

I have a simple SQL stored procedure which I'd like to convert to Oracle syntax to use in Crystal Reports. As you can see below, I have 2 parameters (@STARTDATE and @ENDDATE) which are entered by the user.

CREATE PROCEDURE CONTACT_REPORT
@STARTDATE AS DATE,
@ENDDATE AS DATE
AS
SELECT FNAME, LNAME, ISSUEDATE
FROM CONTACTS
WHERE ISSUEDATE BETWEEN @STARTDATE AND @ENDDATE

I have searched and searched all over the net to find a simple example of a concept of what I'm trying to accomplish; but everything I find seems to be quite complicated. I would like to have a package for each report with the associated stored procedures for the respective report in the package (so it's organized).

I also understand a ref cursor needs to be returned?

If you can help me out with the process of creating the package, and then the stored procedure within the package, that would be great. I have the connection already set up in Crystal Reports to point to the Oracle Database.

Thanks in advance for your help.

Anooj

 

[Turkbear]

Hi,
Learning more about Oracle SPs is a good first step:

http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php

You can then adapt this general knowledge to your needs.

For more on Oracle, SPs and Crystal, try:

http://www.scribd.com/doc/2069216/Crystal-Reports-oracle-stored-procedures

(TRY to Ignore the ads)

http://www.sdn.sap.com/irj/boc/go/p...a60745253?QuickLink=index&overridelayout=true

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