Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: URGENT - Rpt Connected to Oracle Store Proc Post Reply Post New Topic
Author Message
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Topic: URGENT - Rpt Connected to Oracle Store Proc
    Posted: 12 Jul 2011 at 2:02pm
For performance reasons, I've had to take a very complex command that I was using in a report and re-work it in a stored procedure.  Actually, it's complex enough with lots of dynamic SQL generation based on the parameters so I've had to put it into a package.  (The result set always returns the same fields, it just the joins and where clauses in multiple sub-selects that change.)
 
The procedure works great - I' seeing a 10 to 20-fold performance increase and in testing I'm showing that it's returning the correct data.
 
However, when try to use the procedure in a report, it's not returning any data even though the sample report is using one of the same sets of parameters that I've been testing where I know the SP returns data.
 
Here's how I've accessed the SP:
 
1.  In the Database Expert, open the connection, go to the correct Schema, and the to Qualifiers.  Here I see all of the packages in this schema.
2.  Open the package that I need and select my SP.
3.  Enter values for all of the parameters (2 datetimes and 5 strings).
 
I put the fields from the SP on the report, but no data appears.
 
HELP!!  The project manager has committed to a couple of the major users that we will have something available for them to test tomorrow.
 
Thanks!
 
-Dell
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 20 Jul 2011 at 5:14am
I found the problem - for some reason Crystal won't work with Oracle Stored Procs that are using dynamic SQL.  When I re-wrote the stored proc to run hard-coded queries based on the parameters instead of dynamically writing the query based on the parameters, it works fine....  It's not as elegant a solution but at this point it works so my client is happy.
 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.