I am trying to output the results of a SQL select statement into an e-mail, preferably text. I am using PL/SQL. I already have an e-mail function where I can specify a message body. However, I am not sure the best way to get the SQL select statement results into the message body for an arbitrary number of rows.
As an example, I might want to do
select name, data from my_table; and then be able to store this into a VARCHAR2 variable which I can include in an e-mail.
body varchar2(32000) := '';
for row in (select name, data from my_table) loop
body := body || ' ' || row.name || ' ' || to_char(row.data) || chr(13)||chr(10) ; /*chr(13)||chr(10) is line feed carriage return*/
when NO_DATA_FOUND then
p_email('no data found');
when others then