2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

How to get output of SQL select statement into text

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.

 

userimage

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;

Answer is