The last few days I’ve been struggling with Oracle APEX trying to send e-mails via APEX_MAIL from different SMTP servers. But since SMTP configuration is at Instance level, it’s not that easy neither secure to keep changing instance settings and sending e-mails. (you can see the whole information at the oracle forum: https://forums.oracle.com/forums/message.jspa?messageID=10376262#10376262).
Resuming I needed the following settings:
- Each Mail can be sent from different SMTP servers (Even in the same queue of e-mails each one could be sent from different server)
- Body can be Plain text or HTML
- E-mails can have multiple BLOB attachments
So finally I’ve googled around and mixed different solutions using UTL_SMTP to achieve my specifications. It could seem like reinventing the wheel, but I think it’s necessarily since APEX_MAIL (at APEX 4.1.1) uses UTL_MAIL, and UTL_MAIL configures SMTP connection at instance level as well. Therefore, it’s not likely in future versions of APEX to provide feature of sending e-mails from different SMTP servers.
In order to achieve sending as many BLOB attachments as needed, you need to previously create the following types at your schema:
|
1 2 3 4 5 6 |
CREATE OR REPLACE TYPE mail_attachment AS OBJECT ( attachment_name varchar2(2000), attachment_mime varchar2(200), attachment_blob blob ); CREATE OR REPLACE TYPE table_attachments IS TABLE OF mail_attachment; |
I don’t like that much creating custom types since they are uncomfortable to use, but it is the only way to implement it without creating DB tables. At the end, I’ll show how to properly construct required types.
The e-email sending procedure is the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
CREATE OR REPLACE PROCEDURE custom_send_mail ( p_to IN VARCHAR2, p_cc IN VARCHAR2 DEFAULT NULL, p_bcc IN VARCHAR2 DEFAULT NULL, p_from IN VARCHAR2, p_subject IN VARCHAR2, p_text_msg IN VARCHAR2 DEFAULT NULL, p_html_msg IN CLOB DEFAULT NULL, p_attachments IN table_attachments DEFAULT NULL, p_smtp_host IN VARCHAR2, p_smtp_port IN NUMBER DEFAULT 25, p_username IN VARCHAR2 DEFAULT NULL, p_password IN VARCHAR2 DEFAULT NULL) AS l_mail_conn UTL_SMTP.connection; l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*='; l_step PLS_INTEGER := 24573; l_length PLS_INTEGER; l_begin PLS_INTEGER := 1; l_buffer_size INTEGER := 75; l_raw RAW(32767); BEGIN /* **** Author: Josep Coves **** Version: 1.0 **** Date: 08/06/2012 **** josepcoves@relational.es **** http://www.relational.es **** Based on Tim's procedure, Thanks Tim! **** http://www.oracle-base.com/articles/misc/email-from-oracle-plsql.php#html */ l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port); utl_smtp.command( l_mail_conn, 'AUTH LOGIN'); --Establish SMTP connection IF (p_username is not null) then utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( p_username ))) ); utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( p_password ))) ); end if; UTL_SMTP.helo(l_mail_conn, p_smtp_host); UTL_SMTP.mail(l_mail_conn, p_from); UTL_SMTP.rcpt(l_mail_conn, p_to); UTL_SMTP.open_data(l_mail_conn); UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf); if (p_cc is not null) then UTL_SMTP.write_data(l_mail_conn, 'CC: ' || p_cc || UTL_TCP.crlf); end if; if (p_bcc is not null) then UTL_SMTP.write_data(l_mail_conn, 'BCC: ' || p_bcc || UTL_TCP.crlf); end if; UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf); --Plain Text Body IF p_text_msg IS NOT NULL THEN UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, p_text_msg); UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf); ELSIF p_html_msg IS NOT NULL THEN -- HTML Body UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf); FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_html_msg) - 1 )/l_step) LOOP UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_html_msg, l_step, i * l_step + 1)); END LOOP; UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf); END IF; IF p_attachments IS NOT NULL THEN FOR i IN 1..p_attachments.count LOOP UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' ||p_attachments(i).attachment_mime || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf); UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attachments(i).attachment_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf); l_length := dbms_lob.getlength(p_attachments(i).attachment_blob); l_begin := 1; l_raw := null; l_buffer_size := 75; WHILE l_begin < l_length LOOP dbms_lob.read( p_attachments(i).attachment_blob, l_buffer_size, l_begin, l_raw ); utl_smtp.write_raw_data( l_mail_conn, utl_encode.base64_encode(l_raw) ); utl_smtp.write_data( l_mail_conn, utl_tcp.crlf ); l_begin := l_begin + l_buffer_size; END LOOP ; UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf); END LOOP; END IF; UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf); UTL_SMTP.close_data(l_mail_conn); UTL_SMTP.quit(l_mail_conn); END; |
I provide also an example of how to call the function passing blob attachments:
Filling custom type manually:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
declare v_clob clob := '<strong>Test email</strong>'; v_blob blob; t_a table_attachments := null; v_a mail_attachment ; begin ... -- Code to inform v_blob ... v_a := mail_attachment('Filename.pdf','application/pdf',v_blob); t_a := table_attachments(); t_a.extend(1); t_a(1) := v_a; custom_send_mail (p_to => 'to@mail.com', p_from => 'from@mail.com', p_subject => 'test', p_html_msg => v_clob, p_attachments = t_a, p_smtp_host => 'stmp.server.com', p_smtp_port => 25, p_username => 'email@server.com', p_password => 'password'); end; |
Filling via SELECT INTO:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
declare v_clob clob := '<strong>Test email</strong>'; v_blob blob; t_a table_attachments := null; v_a mail_attachment ; begin SELECT mail_attachment(t.column_name,t.column_mimetype,t.column_blob) BULK COLLECT INTO t_a FROM attachments_table t; custom_send_mail (p_to => 'to@mail.com', p_from => 'from@mail.com', p_subject => 'test', p_html_msg => v_clob, p_attachments => t_a, p_smtp_host => 'stmp.server.com', p_smtp_port => 25, p_username => 'email@server.com', p_password => 'password'); end; |
Code based on:
http://www.oracle-base.com/articles/misc/email-from-oracle-plsql.php#html
Alternatives (CES.email_files):
