-- This is a useful sample code on how to send email from PL/SQL procedure
PROCEDURE xx_lotus_email (
sender VARCHAR2,
recipient VARCHAR2,
subj VARCHAR2,
msg VARCHAR2
)
IS
conn UTL_SMTP.connection;
smtp_host VARCHAR2 (50) := '';
port PLS_INTEGER := 25;
mesg VARCHAR2 (2000) := NULL;
cr VARCHAR2 (2) := CHR (13) || CHR (10);
BEGIN
conn := UTL_SMTP.open_connection (smtp_host, port);
mesg :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| cr
|| 'From: '
|| sender
|| cr
|| 'Subject: '
|| subj
|| cr
|| 'To: '
|| recipient
|| cr
||
--Sending email to Lotus Notes smtp:
--The following line must contain the 'cr || cr || msg' or the message body
--will be blank
'Body of message: '
|| cr
|| cr
|| msg;
UTL_SMTP.helo (conn, smtp_host);
UTL_SMTP.mail (conn, sender);
UTL_SMTP.rcpt (conn, recipient);
UTL_SMTP.DATA (conn, mesg);
UTL_SMTP.quit (conn);
END xx_lotus_email;
sender VARCHAR2,
recipient VARCHAR2,
subj VARCHAR2,
msg VARCHAR2
)
IS
conn UTL_SMTP.connection;
smtp_host VARCHAR2 (50) := '';
port PLS_INTEGER := 25;
mesg VARCHAR2 (2000) := NULL;
cr VARCHAR2 (2) := CHR (13) || CHR (10);
BEGIN
conn := UTL_SMTP.open_connection (smtp_host, port);
mesg :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| cr
|| 'From: '
|| sender
|| cr
|| 'Subject: '
|| subj
|| cr
|| 'To: '
|| recipient
|| cr
||
--Sending email to Lotus Notes smtp:
--The following line must contain the 'cr || cr || msg' or the message body
--will be blank
'Body of message: '
|| cr
|| cr
|| msg;
UTL_SMTP.helo (conn, smtp_host);
UTL_SMTP.mail (conn, sender);
UTL_SMTP.rcpt (conn, recipient);
UTL_SMTP.DATA (conn, mesg);
UTL_SMTP.quit (conn);
END xx_lotus_email;
I have a requirement to attach a sql output in a email and send to multiple users, can any one help me.
ReplyDeletecurrently i am Using utl_smtp functionality, below is the script which i am using.
can anyone update the code such that it will pick the output from sql query.
DECLARE
v_From VARCHAR2(80) := '****.***@gmail.com';
v_Recipient VARCHAR2(80) := '****.***@gmail.com';
v_Subject VARCHAR2(80) := ' (autogenerated email)';
v_Mail_Host VARCHAR2(30) := 'mail1.88.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 ||
'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
'Hi Team,'|| crlf || -- Message body
' '|| crlf || -- Message body
'Enddate TMS User account whose HR record is inactive'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="excel.csv"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="excel.csv"'|| crlf ||
crlf ||
'CSV,file,attachement'|| crlf || -- Content of attachment
crlf ||
'-------SECBOUND--' -- End MIME mail
);
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;
/