You can directly use SYS.UTL_SMTP package from your e-mail sending code. But for ease of use i recommend creating the following procedure. Because if you use UTL_SMTP directly from your code, you have to write UTL_SMTP package parameters again and again. With this following procedure you can easily send e-mails from your e-mail sending code.
Please carefully examine and change parts specific to your site on the following code. Like "smtp_hostname","smtp_port".
CREATE OR REPLACE PROCEDURE SYSTEM.BB_send_html_email
(p_sender_name VARCHAR2, -- Sender name
p_sender_mail VARCHAR2, -- Sender email
p_recipient VARCHAR2, -- Recipient name
p_subject VARCHAR2, -- Subject
p_message VARCHAR2 -- Message content
) is
v_conn SYS.UTL_SMTP.CONNECTION;
v_smtp_hostname varchar(200) := '12.345.67.890'; --SMTP server ip
v_smtp_port varchar2 (10):='123'; --SMTP server port
v_smtp_uname VARCHAR2(200) := ''; --SMTP server username (optional)
v_smtp_passwd VARCHAR2(200) := ''; --SMTP server user password (optional)
v_sender VARCHAR2(200) := null;
CHAR_SET constant varchar(200) := 'Content-Type: text/html;charset=UTF-8' ||SYS.UTL_TCP.CRLF;
MIME_VERSION constant varchar(200) := 'MIME-version: 1.0' || SYS.UTL_TCP.CRLF;
CONT_ENCODING constant varchar(200) := 'Content-Transfer-Encoding: quoted-printable ' ||SYS.UTL_TCP.CRLF;
BEGIN
if p_message is null then
return;
end if;
v_conn := SYS.UTL_SMTP.open_connection(v_smtp_hostname, v_smtp_port);
if v_smtp_uname is not null then
SYS.UTL_SMTP.ehlo(v_conn, v_smtp_hostname);
SYS.UTL_SMTP.command(v_conn, 'AUTH LOGIN');
SYS.UTL_SMTP.command(v_conn,
sys.utl_raw.cast_to_varchar2(SYS.UTL_ENCODE.base64_encode(sys.utl_raw.cast_to_raw(v_smtp_uname))));
SYS.UTL_SMTP.command(v_conn,
sys.utl_raw.cast_to_varchar2(SYS.UTL_ENCODE.base64_encode(sys.utl_raw.cast_to_raw(v_smtp_passwd))));
else
SYS.UTL_SMTP.helo(v_conn, v_smtp_hostname);
end if;
SYS.UTL_SMTP.Helo(v_conn, v_smtp_hostname);
SYS.UTL_SMTP.Mail(v_conn, p_sender_mail);
SYS.UTL_SMTP.rcpt(v_conn, p_recipient);
SYS.UTL_SMTP.OPEN_DATA(v_conn);
SYS.UTL_SMTP.WRITE_DATA(v_conn, MIME_VERSION);
SYS.UTL_SMTP.WRITE_DATA(v_conn, CHAR_SET);
SYS.UTL_SMTP.WRITE_DATA(v_conn, CONT_ENCODING);
SYS.UTL_SMTP.WRITE_DATA(v_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD.MM.YYYY hh24:mi:ss') || ' +0200 (GMT)' || SYS.UTL_TCP.CRLF);
SYS.UTL_SMTP.write_raw_data(v_conn, sys.utl_raw.cast_to_raw('Subject:' || p_subject));
SYS.UTL_SMTP.WRITE_DATA(v_conn, SYS.UTL_TCP.CRLF); -- This CLRF is for 'Subject'.
if p_sender_name is null then
v_sender := p_sender_mail;
else
v_sender := '"' || p_sender_name || '" <' || p_sender_mail || '>';
end if;
SYS.UTL_SMTP.write_raw_data(v_conn, sys.utl_raw.cast_to_raw('From:' || v_sender));
SYS.UTL_SMTP.WRITE_DATA(v_conn, SYS.UTL_TCP.CRLF); --This CLRF is for 'From'.
SYS.UTL_SMTP.WRITE_DATA(v_conn, 'To: ' || p_recipient || SYS.UTL_TCP.CRLF);
SYS.UTL_SMTP.WRITE_DATA(v_conn, SYS.UTL_TCP.CRLF);
SYS.UTL_SMTP.WRITE_RAW_DATA(v_conn, SYS.UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(sys.utl_raw.CAST_TO_RAW(p_message)));
SYS.UTL_SMTP.WRITE_DATA(v_conn, SYS.UTL_TCP.CRLF);
SYS.UTL_SMTP.CLOSE_DATA(v_conn);
SYS.UTL_SMTP.QUIT(v_conn);
END;
Then you need to give ACL permissions.
BEGIN
dbms_network_acl_admin.create_acl (acl => 'utl_smtp.xml',
description => 'SMTP Access',
principal => 'SYSTEM',
is_grant => TRUE,
PRIVILEGE => 'connect',
start_date => NULL,
end_date => NULL
);
COMMIT;
END;
BEGIN
dbms_network_acl_admin.add_privilege (acl =>'utl_smtp.xml',
principal => 'SYSTEM',
is_grant => TRUE,
PRIVILEGE => 'resolve',
start_date => NULL,
end_date => NULL
);
COMMIT;
END;
BEGIN
dbms_network_acl_admin.assign_acl (acl => 'utl_smtp.xml',
HOST => '12.345.67.890',
lower_port => 123,
upper_port => 123
);
COMMIT;
END;
commit;
Finally you can create and then execute the following procedure for simply sending a table content.
CREATE OR REPLACE procedure SYSTEM.BB_send_html_email_test_proc
is
CURSOR c1 IS
select ... from ...;
VAR_EMAIL_CONTENT clob;
begin
EXECUTE IMMEDIATE 'alter session set nls_date_format = ''DD.MM.YYYY HH24:MI:SS''';
VAR_EMAIL_CONTENT := '<html> <body><center><table border="6" cellpadding="2" cellspacing="2" >';
VAR_EMAIL_CONTENT := VAR_EMAIL_CONTENT || '<tr><td><font color="#ff0000"> SOME_HEADER_TEXT_HERE </font></td></tr>';
VAR_EMAIL_CONTENT := VAR_EMAIL_CONTENT || '<tr><td><font color="#ff0000"> COLUMN_NAME_1 </font></td><td><font color="#ff0000"> COLUMN_NAME_2 </font></td></tr>';
FOR c_rec1 in c1
LOOP
VAR_EMAIL_CONTENT := VAR_EMAIL_CONTENT || '<tr><td><b>'|| C_REC1.COLUMN_NAME_1 ||'</b></td><td>' || C_REC1.COLUMN_NAME_2 ||'</td></tr>';
END LOOP;
VAR_EMAIL_CONTENT := VAR_EMAIL_CONTENT ||'</table></center></body></html>';
SYSTEM.BB_send_html_email('BB','b.b@xyxy.com.tr','dba_team@xyxy.com.tr','Subject_xxx',VAR_EMAIL_CONTENT);
END BB_send_html_email_test_proc;
/
Hiç yorum yok:
Yorum Gönder