Re: Oracle 9i PL/SQL Collection Question

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 16 May 2004 08:18:15 -0700
Message-ID: <1084720697.657013@yasure>

Aaron Rouse wrote:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1084657398.145929_at_yasure>...
> 

>>Far too much code for me to take the time to read and understand it.
>>
>>Suffice it to say that unless you are being paid by the hour ... I
>>still can't understand the point of anything more than a simple:
>>
>>SELECT .....
>>INTO x, y, z
>>FROM .......
>>WHERE ......
>>
>>Then rather than using a function return the three values as OUT
>>parameters from a stored proc.

> 
> 
> If I were to spit out the three values via a stored procedure then
> would I be able to select from those values or should I just quit over
> complicating it and plug those three values into a table, select the
> item I need, then truncate it for the next go around.

In kinder gentler form I'll repeat what Sybrand has said. You seem to being trying to use Oracle as though it is some other product of which you are more familiar. That approach almost always produces code that doesn't perform well and/or doesn't scale.

Definitely stop overcomplicating it. That is true in all cases on all platforms with all products.

No table required. Just use the variables returned. Or perhaps see if you could just put the query that is generating those three values into the form of an in-line view within the SQL statement that needs them (you might be more familiar with the phrase dervied table).

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sun May 16 2004 - 10:18:15 CDT