Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Avoiding external join by joining strings

Re: Avoiding external join by joining strings

From: Turkbear <john.g_at_dot.spamfree.com>
Date: Mon, 04 Oct 2004 09:45:43 -0500
Message-ID: <1096901098.qazEVq3UhUNCu/c6oc6xmQ@teranews>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US