how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle I have a table A that has 7 different 1-N relationships. Given an A id, now I am running 7 simple queries to get the N side rows. I suspect this could be sped up.

Is it possible to:


create a stored procedure, with only input the A id
the sp runs the 7 queries, at each step concatenating the result to a single string. Rows/Columns should be separated in some way to allow automatic parsing later (not sure what is the best practice here)
the sp returns the single string, and in my code I only do a round-trip to Oracle


Is this possible? Is my gut right that this would be faster? Any pointers?

thanks
So you want to concatenate multiple rows and columns into a single string, just to parse it later. Overhead 1.

Next, you want to write PL/SQL for something that can be easily done in plain SQL. Overhead 2.

Something like this (I just used random views for the 7 data source):

select listagg(x, '|') within group (order by null) from
(
  select 'COL11' || '-' || 'COL12' as x from v$database where rownum = 1 union all
  select 'COL21' || '-' || 'COL22'      from v$instance where rownum = 1 union all
  select 'COL31' || '-' || 'COL32'      from v$logfile  where rownum = 1 union all
  select 'COL41' || '-' || 'COL42'      from v$log      where rownum = 1 union all
  select 'COL51' || '-' || 'COL52'      from v$datafile where rownum = 1 union all
  select 'COL61' || '-' || 'COL62'      from v$tempfile where rownum = 1 union all
  select 'COL71' || '-' || 'COL72'      from v$version  where rownum = 1
);


You want to minimize round trips. You do not need all this hassle for that, you can fetch multiple rows at a time.