{"id":6,"date":"2014-05-27T22:32:18","date_gmt":"2014-05-27T22:32:18","guid":{"rendered":"http:\/\/localhost\/wordpress\/?p=6"},"modified":"2014-05-27T23:10:47","modified_gmt":"2014-05-27T23:10:47","slug":"send-email-with-bcc-from-oracle-sp","status":"publish","type":"post","link":"https:\/\/blog.silverterra.net\/?p=6","title":{"rendered":"Send email with bcc from Oracle SP"},"content":{"rendered":"<p>There is a general problem to send an invisible copy of email from oracle stored procedure.<br \/>\nLet&#8217;s start with a simple mailer propcedure and add a new recipient (second one) to the connection &#8211; that will be BCC!<br \/>\nThis second one recipient is missing from the header &#8220;To:&#8221; section and will be invisible.<\/p>\n<pre data-enlighter-language=\"sql\" class=\"EnlighterJSRAW\">\r\n  ...\r\n  c := UTL_SMTP.OPEN_CONNECTION(p_mailserv); -- 1. open a server connection\r\n  UTL_SMTP.HELO(c, p_mailserv);              -- 2. say hello\r\n  UTL_SMTP.MAIL(c, p_mailsender);            -- 3. say want to send email\r\n  UTL_SMTP.RCPT(c, p_recipient_email);       -- 4. put the recipient (visible one)\r\n                                             --    I will put into the header \r\n                                             --    &quot;To&quot; section as well\r\n                                             --    that's why will be visible\r\n  IF p_bcc &lt;&gt; ' ' THEN\r\n    UTL_SMTP.RCPT(c, p_bcc);                 -- 5. put a new recipient (bcc)\r\n  END IF;\r\n  ...\r\n  UTL_SMTP.OPEN_DATA(c);\r\n<\/pre>\n<p>and here is a working sample from and oracle8i webdb environment:<\/p>\n<pre data-enlighter-language=\"sql\" class=\"EnlighterJSRAW\">PROCEDURE Html_Email(\r\n  p_from_email IN VARCHAR2,\r\n  p_from_alias IN VARCHAR2,\r\n  p_recipient_email IN VARCHAR2,\r\n  p_recipient_alias IN VARCHAR2,\r\n  p_subj IN VARCHAR2 DEFAULT ' ',\r\n  p_body IN VARCHAR2 DEFAULT ' ',\r\n  p_bcc in varchar2 default ' '\r\n) IS\r\n\r\n\/*\r\n this procedure is to send html based email\r\n*\/\r\n\r\np_mailserv VARCHAR2(100) := '** the mailer server **'; -- mail server\r\np_mailsender VARCHAR2(100) := '** sender account **';  -- account to send email\r\nc UTL_SMTP.CONNECTION; \r\n\r\nPROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS\r\nBEGIN\r\n  UTL_SMTP.WRITE_DATA(c, NAME || ': ' || HEADER || UTL_TCP.CRLF);\r\nEND; \r\n\r\nPROCEDURE send_data(l_body_html VARCHAR2) AS\r\nl_offset NUMBER;\r\nl_ammount NUMBER;\r\nBEGIN\r\n  l_offset  := 1;\r\n  l_ammount := 1900;\r\n  WHILE l_offset &lt; LENGTH(l_body_html) LOOP\r\n    UTL_SMTP.WRITE_DATA(c,SUBSTR(l_body_html, l_offset, l_ammount));\r\n    l_offset := l_offset + l_ammount ;\r\n    l_ammount := LEAST(1900, LENGTH(l_body_html) - l_ammount);\r\n  END LOOP;\r\nEND; \r\n\r\nBEGIN\r\n    \r\n  -- open connection and prepare the sending\r\n  c := UTL_SMTP.OPEN_CONNECTION(p_mailserv);\r\n  UTL_SMTP.HELO(c, p_mailserv);\r\n  UTL_SMTP.MAIL(c, p_mailsender);\r\n  UTL_SMTP.RCPT(c, p_recipient_email);\r\n\r\n  -- put the bcc if we need\r\n  IF p_bcc &lt;&gt; ' ' THEN\r\n    UTL_SMTP.RCPT(c, p_bcc);\r\n  END IF;\r\n    \r\n  -- header\r\n  UTL_SMTP.OPEN_DATA(c);\r\n  send_header('From', '&quot;' || p_from_alias || '&quot; &lt;' || p_from_email || '&gt;');\r\n  send_header('To', '&quot;' || p_recipient_alias || '&quot; &lt;' || p_recipient_email || '&gt;'); \r\n  send_header('Subject', p_subj); \r\n  send_header('Content-Type', 'text\/html;charset=windows-1251');\r\n    \r\n  -- body\r\n  send_data(p_body);\r\n\r\n  -- close the connection\r\n  UTL_SMTP.CLOSE_DATA(c);\r\n  UTL_SMTP.QUIT(c);\r\n\r\n  -- error catching\r\n  EXCEPTION\r\n    WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN\r\n      BEGIN\r\n        UTL_SMTP.QUIT(c);\r\n      EXCEPTION\r\n        WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN\r\n          NULL;\r\n      END;\r\n      htp.p('Failed to send mail due to the following error: ' || SQLERRM); \r\n\r\nEND;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>There is a general problem to send an invisible copy of email from oracle stored procedure. Let&#8217;s start with a simple mailer propcedure and add a new recipient (second one) to the connection &#8211; that will be BCC! This second one recipient is missing from the header &#8220;To:&#8221; section and will be invisible. and here [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-6","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/6","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6"}],"version-history":[{"count":5,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/6\/revisions"}],"predecessor-version":[{"id":22,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/6\/revisions\/22"}],"wp:attachment":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}