title: HTML emails from oracle database
body: <p>In my project, the requirement is to send HTML emails (notifications) from database (Oracle). I am using UTL_SMTP utility for the same. However the notification templates are needed to be stored in one of the database columns and each notification templates have HTML text and variables.</p>
  
  <p>Please, find below the UTL_SMTP code:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION Email_Temp(
  p_to            IN NVARCHAR2,
  p_subject       IN NVARCHAR2,
  p_template_id   IN Form_Templates.template_id%type)
  RETURN          NVARCHAR2
  AS
  Ret_String      NVARCHAR2(10);
  p_from          NVARCHAR2 (2000) := 'This is fixed(Database server)';
  p_smtp_host     NVARCHAR2 (2000) := 'Database';
  p_smtp_port     NUMBER := 25;
  p_html_msg      NVARCHAR2 (4000) := NULL; 
  l_mail_conn     UTL_SMTP.connection;
  l_boundary      NVARCHAR2 (50) := '----=*#abc1234321cba#*=';
  BEGIN
  
  l_mail_conn := UTL_SMTP.open_connection (p_smtp_host, p_smtp_port);
  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, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data (l_mail_conn, 'Sent: ' || TO_CHAR (SYSDATE,'FMDay, FMMonthDD, YYYY HH12:MI:SS PM') || UTL_TCP.crlf);
  UTL_SMTP.write_data (l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  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/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf
                      || UTL_TCP.crlf
                     );
  
  IF p_html_msg IS NOT NULL
  THEN
  select html_string into p_html_msg from Form_Templates where template_id = p_template_id;
    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);
    UTL_SMTP.write_data (l_mail_conn, p_html_msg);
    UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  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);
  Ret_String := 'Sucess';
  RETURN Ret_String;
  END;
  /
  </code></pre>
  
  <p>Now, each templates which are going to be stored in the database columns (with fixed text and variables) look like below sample:</p>
  
  <pre><code>&lt;html&gt;
  &lt;body&gt;
  &lt;h4 style="color:blue;"&gt;Assignee Notification&lt;/h4&gt;&lt;br&gt;
  Hello Assignee,
  &lt;br&gt;&lt;br&gt;
  You have been assigned a document.
  &lt;br&gt;&lt;br&gt;
  &lt;font size="3" color="blue"&gt;&lt;b&gt;Project Name:&lt;/b&gt;&lt;/font&gt; variable
  &lt;br&gt;
  &lt;font size="3" color="blue"&gt;&lt;b&gt;Document Nickname: &lt;/b&gt;&lt;/font&gt; variable
  &lt;br&gt;&lt;br&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>I need to call the templates of mail function but at the same time, templates should contain the fixed text and variables with values.</p>