REM
REM example.sql - A PL/SQL code example to demonstrate how to use the UTL_SMTP
REM package to send emails in ASCII and non-ASCII character sets, emails
REM with text or binary attachments. This example uses the demo_mail helper
REM package that utilizes the UTL_SMTP package to send emails via SMTP.
REM  
REM Note: this example relies on the UTL_ENCODE PL/SQL package in Oracle 9i.
REM For Oracle 8i users, please use example8i instead.

REM Send an email in text to two recipients

BEGIN
  demo_mail.mail(
    sender     => 'Me <me@some-company.com>',
    recipients => 'Someone <someone@some-company.com>, ' ||
                  '"Another one" <another-one@some-company.com>',
    subject    => 'Test',
    message    => 'Hi! This is a test.');
END;
/

REM Send an email in HTML

DECLARE
  conn utl_smtp.connection;
BEGIN
  conn := demo_mail.begin_mail(
    sender     => 'Me <me@some-company.com>',
    recipients => 'Someone <someone@some-company.com>',
    subject    => 'HTML E-mail Test',
    mime_type  => 'text/html');

  demo_mail.write_text(
    conn    => conn,
    message => '<h1>Hi! This is a <i>test</i>.</h1>');
  
  demo_mail.end_mail( conn => conn );

END;
/

REM Send an email with 2 attachments.

DECLARE
  conn      utl_smtp.connection;
  req       utl_http.req;  
  resp      utl_http.resp;
  data      RAW(200);
BEGIN
  conn := demo_mail.begin_mail(
    sender     => 'Me <me@some-company.com>',
    recipients => 'Someone <someone@some-company.com>',
    subject    => 'Attachment Test',
    mime_type  => demo_mail.MULTIPART_MIME_TYPE);

  demo_mail.attach_text(
    conn      => conn,
    data      => '<h1>Hi! This is a test.</h1>',
    mime_type => 'text/html');
  
  demo_mail.begin_attachment(
    conn         => conn,
    mime_type    => 'image/gif',
    inline       => TRUE,
    filename     => 'image.gif',
    transfer_enc => 'base64');

  -- In writing Base-64 encoded text following the MIME format below,
  -- the MIME format requires that a long piece of data must be splitted
  -- into multiple lines and each line of encoded data cannot exceed
  -- 80 characters, including the new-line characters. Also, when
  -- splitting the original data into pieces, the length of each chunk
  -- of data before encoding must be a multiple of 3, except for the
  -- last chunk. The constant demo_mail.MAX_BASE64_LINE_WIDTH
  -- (76 / 4 * 3 = 57) is the maximum length (in bytes) of each chunk
  -- of data before encoding.

  req := utl_http.begin_request('http://www.some-company.com/image.gif');
  resp := utl_http.get_response(req);

  BEGIN
    LOOP
      utl_http.read_raw(resp, data, demo_mail.MAX_BASE64_LINE_WIDTH);
      demo_mail.write_raw(
        conn    => conn,
        message => utl_encode.base64_encode(data));
    END LOOP;
  EXCEPTION
    WHEN utl_http.end_of_body THEN
      utl_http.end_response(resp);
  END;
  demo_mail.end_attachment( conn => conn );

  demo_mail.attach_text(
    conn      => conn,
    data      => '<h1>This is a HTML report.</h1>',
    mime_type => 'text/html',
    inline    => FALSE,
    filename  => 'report.htm',
    last      => TRUE);
  
  demo_mail.end_mail( conn => conn );

END;
/

REM Send an email in Chinese (big5). This needs to be executed in a database
REM with ZHT16BIG5 character set.

DECLARE
  conn utl_smtp.connection;
BEGIN
  conn := demo_mail.begin_mail(
    sender     => 'Me <me@some-company.com>',
    recipients => 'Someone <someone@some-company.com>',
    subject    => 'Chinese Email Test',
    mime_type  => 'text/plain; charset=big5');

  demo_mail.write_mb_text(
    conn    => conn,
    message => 'Chinese email example - ???幱?l?l?󨒤l' || utl_tcp.CRLF);

  demo_mail.end_mail( conn => conn );
END;
/