I have a report created in crystal reports XI. It is based off a stored procedure with parameters. One of the parameters being a ref cursor. I can get the report to work fine with the native db provider. However, I need to make this report work through a vendor product that requires the use of OraOLEDB as the provider. When I switch to OraOLEDB, I get an error when setting the database location and filling in the parameters. The error is PLS-00306 Wrong number or types of arguments, etc. To determine the problematic parameters, I removed them one an a time. The date parameters were a problem, but I got around that by changing the data types in the stored procedure to varchar2 instead of date. However, the ref cursor parameter is another problem that yields the same PLS-00306 error. I've seen tons of documentation on ref cursors working fine with OraOLEDB in .net, asp, etc. So it seems a ref cursor will not work with a combination of OraOLEDB and Crystal. How can I get around this issue?
I'm experimenting with the stored proc and changed it to write to a table instead of a ref cursor. But how do you get crystal to run a stored procedure, then base the report fields off the table the sp wrote to? I'm at a loss here as to how to work around this issue. Thanks for any help you can provide!
First, Crystal will NOT report off a stored procedure that doesn't use a ref cursor - simply won't do it. Second, I've never had good luck using the OraOLEDB driver with Crystal (despite the fact that it use it all the time in .Net applications).
Depending on your exact needs, you might be able to workaround the ref cursor requirement with a bit of a hack. Create a report that calls a SP that returns a ref cursor - doesn't even have to be meaningful data, just the sysdate will do as long as there is at least one record coming back. Within the SP, write the tables that you want written. You can, of course, use any inbound parameters from the SP call to dictate the data that gets written, etc.
Now create a subreport inside your other report and use the written tables as the data source for your subreport - which will be the primary information you want to get. Because the main report will always execute before the subreport, the SP will be called and the other tables written before the subreport data is fetched.
I've used this approach succesfully (with the native driver though) and it works fine as long as you don't need additional subreports (because Crystal won't nest them more than 1 layer deep).
Hope that helps,
frodoman