SQL & PL/SQL

Oracle 19.

We have a mail handler that creates jira tickets depending on a result set from PLSQL. We generate tickets by sending a mail with a piece of plsql like this. v_jira_message is a CLOB

 SYS.UTL_MAIL.SEND(
                       sender=>'my_email@us.com',
                       recipients=>'my_mail_handler@us.com',
                       message=>v_jira_message,
                       subject=>'' || to_char(trunc(sysdate),'Day DD/Mon/YYYY') ,
                       mime_type=>'text/html'
                   );

This works. We're happy with it and getting great use out of it. At the moment we just have v_jira_message as a small piece of text, example

v_jira_message := 'Check 132 failed with value of col < 100"

or

v_jira_message := 'Check 127 approaching max value for week"

Here comes the SQL challenge.

The queries for the checks are all small, 3-4 columns at most and 5 rows at most.

We want to include result set of queries in the v_jira_message clob along with the message with the result set pipe delimited for rows, double pipe delimited for column header. So if I send that into JIRA I get a lovely formatted table in my jira ticket

||col1||col2||
|ABC|123|
|DEF|456|

Im trying to get a function fnc_sql_table_to_pipe_delim to do this for any statement where it will pass the result set back out where we dont know the columns or result set beforehand, a couple of examples

declare
v_clob clob;
v_jira_message clob;
begin
 v_check_sql := ‘select owner, segment_name, segment_type, bytes from dba_segments where rownum<=3’ 
 v_clob := 'rows from dba_Segments';
 v_clob := v_clob || chr(10 )|| fnc_sql_table_to_pipe_delim(v_check_sql);
 dbms_output.put_line(v_clob);
 v_jira_message := v_clob;
end;

--or 

declare 
v_clob clob; 
v_jira_message clob;
begin
v_check_sql := ‘select object_name, object_type, owner from all_objects where rownum<=5’ 
v_clob := '3 rows from dba_Segments'; 
v_clob := v_clob || chr(10 )|| fnc_sql_table_to_pipe_delim(v_check_sql); 
dbms_output.put_line(v_clob); 
v_jira_message := v_clob;
end;

How to write fnc_sql_table_to_pipe_delim is the question

any pointers on where to start. Ive been reading on DBMS_SQL but Im not getting close to it.