how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle
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.