Miscellaneous - E-mail

Type	ID	Disposition	Filename
application/pdf	-	attachment	pdf_file1.pdf
application/pdf	-	attachment	pdf_file2.pdf
image/jpeg	logo.jpg@tedstruik.nl	inline	Ted Struik - Oracle Development logo.jpg
image/jpeg	small_oval.jpg@tedstruik.nl	inline	small_oval.jpg

A package to send emails with plain text and/or html body, including attachments (both inline and as attachment).

Getting the code to comply with MIME specifications was a challenge; not because of the complexity of MIME, but mostly because of the hard-to-read documentation... Especially getting the boundaries to work took a while.
Does not include all MIME features (yet), but works well.

The package also contains some test procedures to easily generate test e-mails (these use the pdf and image objects from the ted_mail_test_parts table - you can use your own test data for that of course).

Table

Table
	Name	ted_mail_test_parts
	DDL	CREATE TABLE "TEDSTRUIK"."TED_MAIL_TEST_PARTS" ( "TYPE" VARCHAR2(100), "ID" VARCHAR2(100), "DISPOSITION" VARCHAR2(100), "FILENAME" VARCHAR2(100), "DATA" BLOB ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" LOB ("DATA") STORE AS BASICFILE ( TABLESPACE "USERS" ENABLE STORAGE IN ROW 4000 CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

Package

Package
	Name	ted_mail_old
	Source	package ted_mail_old is -- record type type part_recType is record ( blob_data blob -- optional (blob_data OR clob_data must be not null) , clob_data clob -- optional (blob_data OR clob_data must be not null) , content_type varchar2 (100) -- required -- e.g. 'image/jpeg' (blob) or 'application/pdf' (blob) or 'text/plain' (clob) , content_type_charset varchar2 (100) -- optional -- e.g. 'utf-8' , content_type_name varchar2 (100) -- optional -- e.g. 'image001.jpg' , content_transfer_encoding varchar2 (100) -- optional -- e.g. 'base64' (blob) or '8bit' (clob) , content_id varchar2 (100) -- optional -- e.g. 'image001.jpg' , content_description varchar2 (100) -- optional -- e.g. 'image001.jpg' , content_disposition varchar2 (100) -- required -- 'attachment' or 'inline' , content_disposition_filename varchar2(1000) -- optional (only required if content_disposition='attachment') -- e.g. 'file.pdf' , content_disposition_size varchar2 (100) -- optional -- e.g. '5116' , content_disposition_cre_date varchar2 (100) -- optional -- e.g. 'Mon, 30 Mar 2015 06:31:50 GMT' , content_disposition_mod_date varchar2 (100) -- optional -- e.g. 'Mon, 30 Mar 2015 06:31:50 GMT' ) ; -- record variable part_rec part_recType; -- table type of records type part_tabType is table of part_recType; -- table variable part_tab part_tabType; procedure sendmail ( p_sender varchar2 , p_recipients_to varchar2 -- Comma separated ljst of 1 or more email adresses , p_recipients_cc varchar2 := null -- Comma separated ljst of 1 or more email adresses , p_recipients_bcc varchar2 := null -- Comma separated ljst of 1 or more email adresses , p_subject varchar2 , p_body clob , p_html_body clob , p_part_tab part_tabType ) ; function image_to_part ( p_blob_data in blob , p_content_id in varchar2 , p_file_name in varchar2 , p_file_type in varchar2 := null -- e.g. 'jpeg' or 'jpg' or 'gif' or 'png' (if null then extension of p_filename is used) , p_disposition in varchar2 := 'attachment' -- 'attachment' or 'inline' ) return part_recType ; function pdf_to_part ( p_blob_data in blob , p_file_name in varchar2 , p_disposition in varchar2 := 'attachment' -- 'attachment' or 'inline' ) return part_recType ; procedure sendmail_test ( p_include_inline_yn in varchar := 'Y' , p_include_attachment_yn in varchar := 'Y' ) ; end ted_mail_old;

Package Body

Package Body
	Name	ted_mail_old
	Source	package body ted_mail_old is g_smtp_domain varchar2(100) := ted_mail_parameter.c_smtp_domain; -- use you own smtp server setting here g_smtp_host varchar2(100) := ted_mail_parameter.c_smtp_host; -- use you own smtp server setting here g_smtp_port pls_integer := ted_mail_parameter.c_smtp_port; -- use you own smtp server setting here procedure sendmail ( p_sender varchar2 , p_recipients_to varchar2 -- Comma separated ljst of 1 or more email adresses , p_recipients_cc varchar2 := null -- Comma separated ljst of 1 or more email adresses , p_recipients_bcc varchar2 := null -- Comma separated ljst of 1 or more email adresses , p_subject varchar2 , p_body clob , p_html_body clob , p_part_tab part_tabType ) is -- Send emails with plain text and/or html body, including attachments (both inline and as attachment) c_boundary_mix constant varchar2(256) := '4481a643c84303_mix'; c_boundary_alt constant varchar2(256) := '4481a643c84303_alt'; c_boundary_rel constant varchar2(256) := '4481a643c84303_rel'; c utl_smtp.connection ; v_boundary_mix_needed boolean := false; v_boundary_alt_needed boolean := false; v_boundary_rel_needed boolean := false; v_part_mix part_recType; v_part_rel part_recType; v_part_alt part_recType; v_part_text_plain part_recType; v_part_text_html part_recType; v_count_parts_inline pls_integer := 0; v_count_parts_attachment pls_integer := 0; procedure common ( c out utl_smtp.connection ) is v_recipients_to_table apex_application_global.vc_arr2; v_recipients_cc_table apex_application_global.vc_arr2; v_recipients_bcc_table apex_application_global.vc_arr2; begin -- make connection to smtp c := utl_smtp.open_connection ( host => g_smtp_host , port => g_smtp_port ) ; -- identify the domain of the sender utl_smtp.helo(c, g_smtp_domain); -- start a mail, specify the sender utl_smtp.mail(c, p_sender); -- identify recipients (to) v_recipients_to_table := apex_string.string_to_table(p_recipients_to, ','); for i in 1 .. v_recipients_to_table.count loop utl_smtp.rcpt(c, v_recipients_to_table(i)); end loop; -- identify recipients (cc) v_recipients_cc_table := apex_string.string_to_table(p_recipients_cc, ','); for i in 1 .. v_recipients_cc_table.count loop utl_smtp.rcpt(c, v_recipients_cc_table(i)); end loop; -- identify recipients (bcc) v_recipients_bcc_table := apex_string.string_to_table(p_recipients_bcc, ','); for i in 1 .. v_recipients_bcc_table.count loop utl_smtp.rcpt(c, v_recipients_bcc_table(i)); end loop; -- start the mail body utl_smtp.open_data(c); utl_smtp.write_data(c, 'From: ' \|\| p_sender \|\| utl_tcp.crlf); utl_smtp.write_data(c, 'To: ' \|\| p_recipients_to \|\| utl_tcp.crlf); if p_recipients_cc is not null then utl_smtp.write_data(c, 'Cc: ' \|\| p_recipients_cc \|\| utl_tcp.crlf); end if; utl_smtp.write_data(c, 'Subject: ' \|\| p_subject \|\| utl_tcp.crlf); utl_smtp.write_data(c, 'Date: ' \|\| to_char(systimestamp, 'Dy, DD Mon YYYY HH24:MI:SS TZHTZM', 'NLS_DATE_LANGUAGE = ENGLISH') \|\| utl_tcp.crlf); -- RFC 5322, section 3.3 utl_smtp.write_data(c, 'MIME-Version: 1.0' \|\| utl_tcp.crlf); exception when utl_smtp.transient_error or utl_smtp.permanent_error then utl_smtp.quit(c); raise; end common; function header ( p_name in varchar2 , p_value in varchar2 , p_parm_01_name in varchar2 := null , p_parm_01_value in varchar2 := null , p_parm_02_name in varchar2 := null , p_parm_02_value in varchar2 := null , p_parm_03_name in varchar2 := null , p_parm_03_value in varchar2 := null , p_parm_04_name in varchar2 := null , p_parm_04_value in varchar2 := null , p_parm_05_name in varchar2 := null , p_parm_05_value in varchar2 := null ) return varchar2 is v_result varchar2(4000); function format_value ( p_name in varchar2 , p_value in varchar2 ) return varchar2 is v_value varchar2(1000) := convert(p_value, 'US7ASCII'); begin -- add brackets if needed if p_name in ('Content-ID') then v_value := '<' \|\| v_value \|\| '>'; end if; return v_value; end format_value; function format_parm_value ( p_parm_name in varchar2 , p_parm_value in varchar2 ) return varchar2 is v_parm_value varchar2(1000) := convert(p_parm_value, 'US7ASCII'); begin -- add quotes if needed if p_parm_name not in ( 'filename', 'size' ) then v_parm_value := '"' \|\| v_parm_value \|\| '"'; end if; return v_parm_value; end format_parm_value; begin if p_value is null then return null; end if; v_result := p_name \|\| ': ' \|\| format_value(p_name, p_value); if p_parm_01_value is not null then v_result := v_result \|\| ';' \|\| utl_tcp.crlf \|\| ' ' \|\| p_parm_01_name \|\| '=' \|\| format_parm_value(p_parm_01_name, p_parm_01_value); end if; if p_parm_02_value is not null then v_result := v_result \|\| ';' \|\| utl_tcp.crlf \|\| ' ' \|\| p_parm_02_name \|\| '=' \|\| format_parm_value(p_parm_02_name, p_parm_02_value); end if; if p_parm_03_value is not null then v_result := v_result \|\| ';' \|\| utl_tcp.crlf \|\| ' ' \|\| p_parm_03_name \|\| '=' \|\| format_parm_value(p_parm_03_name, p_parm_03_value); end if; if p_parm_04_value is not null then v_result := v_result \|\| ';' \|\| utl_tcp.crlf \|\| ' ' \|\| p_parm_04_name \|\| '=' \|\| format_parm_value(p_parm_04_name, p_parm_04_value); end if; if p_parm_05_value is not null then v_result := v_result \|\| ';' \|\| utl_tcp.crlf \|\| ' ' \|\| p_parm_05_name \|\| '=' \|\| format_parm_value(p_parm_05_name, p_parm_05_value); end if; v_result := v_result \|\| utl_tcp.crlf; return v_result; end header; procedure begin_mime_block ( po_conn in out nocopy utl_smtp.connection , p_boundary in varchar2 , p_part in part_recType , p_part_content_type_boundary in varchar2 := null , p_part_content_type_type in varchar2 := null ) is begin if p_boundary is not null then utl_smtp.write_data(po_conn, '--' \|\| p_boundary \|\| utl_tcp.crlf); end if; -- Content-Type utl_smtp.write_data ( po_conn , header ( 'Content-Type', p_part.content_type , 'charset' , p_part.content_type_charset , 'name' , p_part.content_type_name , 'boundary' , p_part_content_type_boundary , 'type' , p_part_content_type_type ) ) ; -- Content-Transfer utl_smtp.write_data ( po_conn , header ( 'Content-Transfer-Encoding', p_part.content_transfer_encoding ) ) ; -- Content-ID utl_smtp.write_data ( po_conn , header ( 'Content-ID', p_part.content_id ) ) ; -- Content-Description utl_smtp.write_data ( po_conn , header ( 'Content-Description', p_part.content_description ) ) ; -- Content-Disposition utl_smtp.write_data ( po_conn , header ( 'Content-Disposition', p_part.content_disposition , 'filename' , p_part.content_disposition_filename , 'size' , p_part.content_disposition_size , 'creation-date' , p_part.content_disposition_cre_date , 'modification-date' , p_part.content_disposition_mod_date ) ) ; utl_smtp.write_data(po_conn, utl_tcp.crlf); end begin_mime_block; procedure end_mime_block ( po_conn in out nocopy utl_smtp.connection , p_boundary in varchar2 ) is begin utl_smtp.write_data(po_conn, utl_tcp.crlf); utl_smtp.write_data(po_conn, '--' \|\| p_boundary \|\| '--' \|\| utl_tcp.crlf); end end_mime_block; procedure send_mime_blob ( po_conn in out nocopy utl_smtp.connection , p_blob in blob , p_content_transfer_encoding in varchar2 ) is l_offset number; l_buffer_size integer := 45; l_raw raw(45); l_length number; begin l_offset := 1; l_length := dbms_lob.getlength( p_blob ); while l_offset < l_length loop dbms_lob.read( p_blob, l_buffer_size, l_offset, l_raw); if lower(p_content_transfer_encoding) = 'base64' then utl_smtp.write_raw_data(po_conn, utl_encode.base64_encode(l_raw)); else utl_smtp.write_raw_data(po_conn, utl_raw.cast_to_varchar2(l_raw)); end if; utl_smtp.write_data( po_conn, utl_tcp.crlf); l_offset := l_offset + l_buffer_size; end loop; end send_mime_blob; procedure send_mime_clob ( po_conn in out nocopy utl_smtp.connection , p_clob in clob ) is l_offset number; l_length number; begin l_offset := 1; l_length := 1900; while l_offset < dbms_lob.getlength( p_clob ) loop utl_smtp.write_data( po_conn, dbms_lob.substr( p_clob,l_length,l_offset)); l_offset := l_offset + l_length; l_length := least(1900,dbms_lob.getlength( p_clob) - l_length); end loop; end send_mime_clob; procedure send_mime_lob ( po_conn in out nocopy utl_smtp.connection , p_part in part_recType ) is begin if p_part.blob_data is not null then send_mime_blob( po_conn, p_part.blob_data, p_part.content_transfer_encoding ); elsif p_part.clob_data is not null then send_mime_clob( po_conn, p_part.clob_data ); end if; end send_mime_lob; begin -- init parts v_part_mix.content_type := 'multipart/mixed'; v_part_rel.content_type := 'multipart/related'; v_part_alt.content_type := 'multipart/alternative'; -- v_part_text_plain.content_type := 'text/plain'; v_part_text_plain.content_type_charset := 'utf-8'; v_part_text_plain.content_disposition := 'inline'; -- v_part_text_html.content_type := 'text/html'; v_part_text_html.content_type_charset := 'utf-8'; v_part_text_html.content_disposition := 'inline'; -- calc v_count_parts_inline & v_count_parts_attachment if p_part_tab is not null then for i in 1 .. p_part_tab.count loop if p_part_tab(i).content_disposition = 'inline' then v_count_parts_inline := v_count_parts_inline + 1; elsif p_part_tab(i).content_disposition = 'attachment' then v_count_parts_attachment := v_count_parts_attachment + 1; end if; end loop; end if; -- calc v_boundary_%_needed if v_count_parts_inline > 0 or v_count_parts_attachment > 0 then v_boundary_mix_needed := true; end if; -- if dbms_lob.getlength(p_body) > 0 and dbms_lob.getlength(p_html_body) > 0 then v_boundary_alt_needed := true; end if; -- if dbms_lob.getlength(p_html_body) > 0 and v_count_parts_inline > 0 then v_boundary_rel_needed := true; end if; -- open connection common( c ); -- begin multipart/mixed begin_mime_block ( po_conn => c , p_boundary => null , p_part => v_part_mix , p_part_content_type_boundary => c_boundary_mix ) ; -- begin multipart/related (if needed) if v_count_parts_inline > 0 then begin_mime_block ( po_conn => c , p_boundary => c_boundary_mix , p_part => v_part_rel , p_part_content_type_boundary => c_boundary_rel , p_part_content_type_type => 'multipart/alternative' ) ; end if; -- begin multipart/alternative begin_mime_block ( po_conn => c , p_boundary => ( case when v_count_parts_inline = 0 then c_boundary_mix else c_boundary_rel end ) , p_part => v_part_alt , p_part_content_type_boundary => c_boundary_alt ) ; -- text/plain if p_body is not null then begin_mime_block ( po_conn => c , p_boundary => c_boundary_alt , p_part => v_part_text_plain ) ; send_mime_clob( c, p_body ); utl_smtp.write_data(c, utl_tcp.crlf); end if; -- text/html if p_html_body is not null then begin_mime_block ( po_conn => c , p_boundary => c_boundary_alt , p_part => v_part_text_html ) ; send_mime_clob( c, p_html_body ); utl_smtp.write_data(c, utl_tcp.crlf); end if; -- end multipart/alternative end_mime_block( c, p_boundary => c_boundary_alt ); -- begin multipart/related (inline parts) if v_count_parts_inline > 0 then for i in 1 .. p_part_tab.count loop if p_part_tab(i).content_disposition = 'inline' then begin_mime_block ( po_conn => c , p_boundary => c_boundary_rel , p_part => p_part_tab(i) ) ; send_mime_lob( c, p_part_tab(i)); end if; end loop; -- end multipart/related end_mime_block( c, p_boundary => c_boundary_rel ); end if; -- begin multipart/mixed (attachment parts) if v_count_parts_attachment > 0 then for i in 1 .. p_part_tab.count loop if p_part_tab(i).content_disposition = 'attachment' then begin_mime_block ( po_conn => c , p_boundary => c_boundary_mix , p_part => p_part_tab(i) , p_part_content_type_type => null ) ; send_mime_lob( c, p_part_tab(i)); end if; end loop; end if; -- end multipart/mixed end_mime_block( c, p_boundary => c_boundary_mix ); -- end body utl_smtp.close_data( c ); -- end connectie utl_smtp.quit( c ); exception when utl_smtp.transient_error or utl_smtp.permanent_error then utl_smtp.quit( c ); raise; when others then raise; end sendmail; function image_to_part ( p_blob_data in blob , p_content_id in varchar2 , p_file_name in varchar2 , p_file_type in varchar2 := null -- e.g. 'jpeg' or 'jpg' or 'gif' or 'png' (if null then extension of p_filename is used) , p_disposition in varchar2 := 'attachment' -- 'attachment' or 'inline' ) return part_recType is v_result part_recType; v_file_type varchar2(100) := lower(p_file_type); v_content_type varchar2(100); begin if v_file_type is not null then v_file_type := lower(p_file_type); else v_file_type := lower( substr(p_file_name, instr(p_file_name,'.')+1 ) ); end if; v_content_type := 'image/' \|\| ( case v_file_type when 'jpg' then 'jpeg' else v_file_type end ) ; v_result.blob_data := p_blob_data; v_result.clob_data := null; v_result.content_type := v_content_type; v_result.content_type_charset := null; v_result.content_type_name := p_file_name; v_result.content_transfer_encoding := 'base64'; v_result.content_id := p_content_id; v_result.content_description := p_file_name; v_result.content_disposition := p_disposition; v_result.content_disposition_filename := p_file_name; v_result.content_disposition_size := trim( dbms_lob.getlength(p_blob_data)); v_result.content_disposition_cre_date := null; v_result.content_disposition_mod_date := null; return v_result; end image_to_part; function pdf_to_part ( p_blob_data in blob , p_file_name in varchar2 , p_disposition in varchar2 := 'attachment' -- 'attachment' or 'inline' ) return part_recType is v_result part_recType; begin v_result.blob_data := p_blob_data; v_result.clob_data := null; v_result.content_type := 'application//pdf'; v_result.content_type_charset := null; v_result.content_type_name := p_file_name; v_result.content_transfer_encoding := 'base64'; v_result.content_id := null; v_result.content_description := p_file_name; v_result.content_disposition := p_disposition; v_result.content_disposition_filename := p_file_name; v_result.content_disposition_size := trim( dbms_lob.getlength(p_blob_data)); v_result.content_disposition_cre_date := null; v_result.content_disposition_mod_date := null; return v_result; end pdf_to_part; procedure sendmail_test ( p_include_inline_yn in varchar := 'Y' , p_include_attachment_yn in varchar := 'Y' ) is v_part_tab part_tabtype := part_tabtype(); v_body varchar2(32767); v_html_body varchar2(32767); cursor c1 is select rownum as r , t.* from ted_mail_test_parts t where ( t.disposition = 'inline' and upper(p_include_inline_yn) = 'Y' ) or ( t.disposition = 'attachment' and upper(p_include_attachment_yn) = 'Y' ) ; begin v_body := 'PLAIN TEXT'; v_html_body := '<html><body>' \|\| utl_tcp.crlf \|\| '<b>HTML</b> BODY<br/>' \|\| utl_tcp.crlf \|\| 'p_include_inline_yn = '\|\|p_include_inline_yn\|\|'<br/>' \|\| utl_tcp.crlf \|\| 'p_include_attachment_yn = '\|\|p_include_attachment_yn\|\|'<br/>' \|\| utl_tcp.crlf ; -- add inline parts & attachments for c1_rec in c1 loop v_part_tab.extend; if c1_rec.type = 'image/jpeg' then v_part_tab(c1_rec.r) := image_to_part(p_blob_data => c1_rec.data, p_content_id => c1_rec.id, p_file_name => c1_rec.filename, p_disposition => c1_rec.disposition); elsif c1_rec.type = 'application/pdf' then v_part_tab(c1_rec.r) := pdf_to_part(p_blob_data => c1_rec.data, p_file_name => c1_rec.filename, p_disposition => c1_rec.disposition); else v_part_tab(c1_rec.r).blob_data := c1_rec.data; v_part_tab(c1_rec.r).content_type := c1_rec.type; v_part_tab(c1_rec.r).content_type_charset := null; v_part_tab(c1_rec.r).content_type_name := c1_rec.filename; v_part_tab(c1_rec.r).content_transfer_encoding := 'base64'; v_part_tab(c1_rec.r).content_id := c1_rec.id; v_part_tab(c1_rec.r).content_disposition := c1_rec.disposition; v_part_tab(c1_rec.r).content_disposition_filename := c1_rec.filename; v_part_tab(c1_rec.r).content_disposition_size := dbms_lob.getlength(c1_rec.data); end if; if c1_rec.disposition = 'inline' then v_html_body := v_html_body \|\| 'image: ' \|\| c1_rec.filename \|\| utl_tcp.crlf \|\| '<img src="cid:' \|\| c1_rec.id \|\| '"><br/>' \|\| utl_tcp.crlf ; end if; end loop; v_html_body := v_html_body \|\| '</body></html>' \|\| utl_tcp.crlf; sendmail ( p_sender => 'ted_mail_old.sendmail_test@tedstruik.nl' , p_recipients_to => 'test@tedstruik.nl' , p_recipients_cc => null , p_recipients_bcc => null , p_subject => 'ted_mail_old.sendmail_test' \|\| ' - p_include_inline_yn = '\|\|p_include_inline_yn \|\| ' - p_include_attachment_yn = '\|\|p_include_attachment_yn , p_body => v_body , p_html_body => v_html_body , p_part_tab => v_part_tab ) ; end; end ted_mail_old;

Table ted_mail_test_parts

Column Actions

Info

Code

Table

Package

Package Body