Re: Ref cursors

mk wrote:

> Hi All,
>
> I have a question regarding the use of ref cursors-
>
> This is what I would like to do in a stored proc-
> Open a refcursor (a selected set of data)-process each row in the
> refcursor (with some business logic-each row could be processed
> differently) and finally return the same ref cursor out
>
> The only way that i am aware of this being possible is inserting each
> row in a temp table after processing it and then opening a cursor on
> that table-this might be expensive since we are processing
> transactions in millions/per day!
>
> Any and all help is appreciated!
>
> Thanks
> MK

Either that or open a regular cursor to do the work. Save the result set and then open a ref cursor on the results.

But the thought that comes to mind here is that your design is your problem. You should be storing data in the form in which it is required. It is one thing to query a few hundred or thousand rows and process them. But millions? I think you are saving your data in the wrong format. Why not process it before it goes into the tables?

Also consider using materialized views if your data does not need to be real-time.

Daniel A. Morgan Received on Mon Jun 11 2001 - 22:33:38 CDT