ref cursors, my only option?????

[programmerbrian]

Hello,
I have to turn a very large sql stmt. into a stored procedure for use in a CR 8.0 report.

The existing report takes forever to run, a lot of processing time is spent on the client side formatting and grouping the data.

In a previous stored procedure I grabbed my data into a cursor, fetched all the rows, did my grouping, summing, conditional stmt's, and stored my new rows into a PL/SQL table(array). Then just looped through the array and DBMS_OUTPUTED the rows.

Is there a way to do something similar with crystal reports or am I limited to a ref cursor and adding more code to an already large sql stmt to take care of the sums and grouping.

Thanks in advance.

 

[AnnetteH]

The Oracle stored procedure must return a ref cursor in order for Crystal to be able to use it, but why couldn't you store your results in a temp table (Create Global Temporary Table), populate it as before, and then set your cursor to Select * from the temp table?

 

[programmerbrian]

Thats a good idea.
Thanks