Re: stored proc returning array
Date: 1 Mar 2004 05:53:51 -0800
Message-ID: <54ff5605.0403010553.a4414c6_at_posting.google.com>
Mark,
Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0402281610.4ad97bbb_at_posting.google.com>...
> 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 Mon Mar 01 2004 - 14:53:51 CET