Using stored procedures as a datasource

[JScannell]

Crystal version: 8.5
Database: Oracle 8i using native driver

All the documentation I have seen says that I can use a stored procedure that has a few input parameters and a ref. cursor as an output in my reports.

I have successfully created a stored procedure and produced output from using sqlplus and toad.

I can connect to the stored procedure in the design phase of crystal and it can find the sproc and its contents.

When I attempt to run the report, I am prompted for the input parameters and then I get an 'invalid ref cursor' message.

I have tried everything I can think of without any success.

Does anyone have any success doing this? Do I need to consider upgrading Crystal?

Thanks,
Jerry

Jerry Scannell

 

[synapsevampire]

Follow the whitepaper:

http://technicalsupport.businessobj...espdfasp&sliceId=&dialogID=11046198&stateId=1

0 11044871

-k

 

[JScannell]

Thanks for the link, but I already have a copy of that document and have tried implementing it ver batim into a Crystal Report and get the same "invalid ref cursor" error message.


Jerry Scannell

 

[thiagarr]

Jerry,

Recently, I had some success in getting Oracle Stored Procedures to work in CR XI. The document from BO is written sometime back though the methodology works in XI.

Also, what connection methods do you use? I got it to work with Native driver and also with the CR Oracle drivers 4.1, 5.0 or 5.1 that can be downloaded from BO website.

I tried the sample in their document got that to work to start with. I found that the date format needs to be modified for correct results, etc. I also got the method described in BO KB Articles: 2003 and 3205 to work.

If you still need some feedback, please post a sample SP, if possible.

Thank You

TR

 

[synapsevampire]

TR: It's very different for CR 8.5, CR XI is a snap.

It's very common to build SPs in Oracle for Crystal, and has been done successfully by thousands for a long time, so the issue is probably connectivity.

I would try using the Crystal supplied Oracle ODBC driver if the native isn't working, but prior to doing either make sure that your service packs are up to date.

Also start with something very simple, it may be your code.

-k

 

[JScannell]

TR,

If the sproc didn't run successfully in Toad and SQLPlus I would suspect the code. However, it seems to be following all the rules as set down by the documentation I have read.

Thanks for the idea of using the Oracle ODBC driver. I will see if I can change our environment.

Jerry

Jerry Scannell

 

[thiagarr]

K & Jerry,

Thank you. I am spoilt in the sense I use the easier version of the product.

Also, the way the date values are represented in the SP also made a difference. The SP worked fine in CR but returned different data compared to what it did in SQL Developer. Then I had to make changes to the date formatting in the SP before it brought the correct results in CR.

Thank you again.

TR