Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Avoiding external join by joining strings
jochen.wiedmann_at_freenet.de (Jochen Wiedmann) wrote:
>Hi,
>
>I have a program, which reads a list of rows from table A. There is a
>1:n relation
>between table A and B. For any row we find in A, we start a query for
>the matching rows in B , reading the "name" column. Finally, we
>concatenate these "name"
>columns into a string like
>
> "name1, name2, name3"
>
>Is it possible, to let Oracle do the concatenation? If so, we could
>avoid the per row queries (which I call external join), thus possibly
>speeding up the operation a lot.
>
>
>Regards,
>
>Jochen
Look into building a stored procedure - Pl/Sql can handle this type of thing better than SqlPLus..
Basically you would open a 2 cursors..pass a parameter to select one value from Tablea and then loop through the matching values in the TableB cursor and concatenate the retrieved values..when all have been retrieved, close the loop close the cursors and return the variable...
hth Received on Mon Oct 04 2004 - 09:45:43 CDT