How to get output of SQL select statement into text

Question

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.

Answer 1

declare
    body varchar2(32000) := '';
begin
    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*/
    end loop;
    p_email(body);
    exception
        when NO_DATA_FOUND then
            p_email('no data found');
        when others then
            raise;
end;