Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.
Course Of The Month
9 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
OraOLEDB and Ref Cursors
Posted on 2006-11-27
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!
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!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
7 Comments
Active today
I am not an Oracle user but from what I have seen Crystal uses Oracle stored procedures differently than other query types.
A technical brief on how to report off Oracle stored procedures.
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_oracle_stored_procedures.pdf.asp
Requirements for ORACLE Stored Procedure Access
http://support.businessobjects.com/library/kbase/articles/c2004898.asp
Check this article - Using Oracle Stored Procedures
http://support.businessobjects.com/library/kbase/articles/c2001157.asp
mlmcc
A technical brief on how to report off Oracle stored procedures.
http://support.businessobjects.com/communityCS/TechnicalPapers/cr_oracle_stored_procedures.pdf.asp
Requirements for ORACLE Stored Procedure Access
http://support.businessobjects.com/library/kbase/articles/c2004898.asp
Check this article - Using Oracle Stored Procedures
http://support.businessobjects.com/library/kbase/articles/c2001157.asp
mlmcc
Thank you for looking at this. I've read those kb articles. OraOLEDB will work but only if there is no ref cursor involved. I need to figure out how to get crystal to 1 - execute a stored procedure that does NOT use a ref cursor, but writes to a table instead 2 - report off those table fields. Is that possible? Kind of like getting crystal to report off 2 sources, but unfortunately not 2 tables.
Hi jjmgb,
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
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
Thanks frodoman. Here's a little more detail that complicates it a bit further. I am already using a similar scenario where I have a main report and a sub-report. The difference in my scenario is both are based on the sp. I needed to have a main for the reason that two of the input params to the sp are lov's and you need to perform a join calculation to format the lovs into comma delimited strings. Then you need to link the main and sub report parameters together to pass the reformatted params. But I don't see in your scenario how I can create the main report linked to a sp that returns a ref cursor when my problem is just that, with oraoledb anyway, which unfortunately is a must in my case because of the vendor interface. So if I understand what you're saying, the report will not execute a stored procedure that merely writes to a table without returning a value. I was afraid that would be the answer. Bummer. But I do thank you for your input!
>>> I understand what you're saying, the report will not execute a stored procedure that merely writes to a table without returning a value
That is unfortunately correct, the SP must return a ref cursor. I won't go so far as to say that you can't use oraoledb, just that I personally haven't had any luck getting it to work.
That is unfortunately correct, the SP must return a ref cursor. I won't go so far as to say that you can't use oraoledb, just that I personally haven't had any luck getting it to work.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I hate sub reports and always consider them the last resort in any reporting solution. The negative effect on performance and maintainability is just not worth the easy ride they give the report writer. Nine times out of ten reporting requirements…
Hello everyone,
Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8.
We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 6 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.