URGENT - Rpt Connected to Oracle Stored Procedure

[hilfy]

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

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Turkbear]

Hi,
Does your SP return a REFCURSOR - That is a requirement if you want to see/use the data returned by the proc.





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

 

[hilfy]

Yes, it is returning a ref cursor. In Crystal I see the names of the fields that it is supposed to return, just no data.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[briangriffin]

Have you tried connecting via ODBC? Not a permanent solution, but it might give you a clue, especially if it's a driver issue.

 

[hilfy]

I don't have that option - the report is run through BusinessObjects and accessed through Clarity PPM. BO is installed on a Linux server. There are no ODBC drivers installed there and the client won't install them.

The original version of this was using a weakly-typed cursor. I'm in the process of changing this over to a strongly-typed cursor to see if that will help. It means I have to change the dynamic query to insert into a table (there's a max of 53 rows - every Monday date in a year, so it shouldn't be to big a performance hit) and then select from that table for the final result set.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[hilfy]

It turned out that the problem was due to using dynamic SQL in the stored proc. Because there were 9 different parameter combinations, I was building the SQL as a string and then running it. Works fine in Oracle, but for some reason Crystal wouldn't get the data. When I re-wrote the stored proc to have a case statement with 9 separate hard-coded queries, it works. It's not as elegant a solution, but it solved the problem. I used this technique for two separate sections of the report and it went from taking 2-5 minutes to run using two commands to taking 15 - 40 seconds using two stored procs.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.