SQL & PL/SQL

Hi All,

This is my first time playing around with utl_smtp. I'm assuming there is a simple way to embed the results of a query into the body of an email. I've included some code I copied from the web to play around with. I know the script won't work with the select as is, but it’s just there as a guide to what I want to do.

Any tips of trick would be appreciated.

declare
v_from varchar2(80) := 'emailfrom@company.com';
v_Recipient VARCHAR2(80) := 'emailto@company.com';
v_subject varchar2(80) := 'test subject';
v_Mail_Host VARCHAR2(30) := 'smtp.company.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
crlf ||
select username, account_status, default_tablespace, temporary_tablespace from dba_user
|| crlf
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
end;


Regards,
Adam