Re: stored proc returning array

michaeltrosen_at_yahoo.com (Michael Trosen) wrote in message news:<54ff5605.0402271201.474391bc_at_posting.google.com>...
> Hi Everyone,
>
> I hope someone can help, I'm pretty new to pro*c programming.
>
> I have the following application setup:
>
> a pro*c program calls a stored procedure and recieves a cursor back:
>
> the cursor is defined as: SQL_CURSOR delpt_cursor
>
> it's assigned by:
> :delpt_cursor := radixbrc.retMMAddrsList(:zip,:RtNum,:ih_date)
>
> So, now I have all the data that was retrieved in the stored procedure
> in a cursor, and I can loop through it:
>
> for (;;)
> {
> get each piece of data
> put data in flat file
> }
>
> The problem with this approach is that it is too slow..
>
> So, i'm looking at instead of returning a cursor, returning a host
> array... does it make sense to do this instead? If so, how do you get
> the data out of the host array in the Pro*C code?
>
> Thank you for any help!!
>
> Michael

First, is it really necessary to get the data via a stored procedure rather than just querying it directly?

Either way make sure the problem is in passing the data back to the program and not in the performance of the query that builds the cursor. In order words make sure you do not have a quuery statement tuning issue instead of a retrieval issue.

If you can query the data directly you should be able to replace your single row processing loop with a much faster array fetch. Oracle will shove the data into a C language array and you print it from there.

HTH -- Mark D Powell -- Received on Sun Feb 29 2004 - 01:10:49 CET