27.02.2016 5 min to read

Oracle: отправка электронной почты с помощью пакета UTL_SMTP

Category : Статьи

В БД Oracle существует несколько средств работы с почтой. Среди них для отправки писем имеется два пакета: UTL_MAIL и UTL_SMTP.

UTL_MAIL

Первый пакет появился сравнительно недавно – с версии Oracle 10g и является надстройкой над вторым пакетом. Как следствие, преимущества UTL_MAIL – отправка письма с помощью одной простой команды, где задается ряд параметров:

  • subject – название
  • sender – отправитель
  • recipients – получатели
  • cc – получатели копий
  • bcc – получатели скрытых копий
  • message – содержимое письма
  • mime_type – тип содержимого письма
  • priority – приоритет

Недостаток – остальные параметры соединения с сервером берутся по-умолчанию. Адрес сервера исходящей почты определяется в системе заранее:

ALTER SYSTEM SET smtp_out_server = 'smtp.mail.server' SCOPE=BOTH;

Из соображений безопасности, изначально этот пакет не настроен. Как следствие, перед использованием пакет необходимо установить:

$ORACLE_HOME/rdbms/admin/utlmail.sql

Таким образом, пакет UTL_MAIL удобно использовать при отправке почты через фиксированный публичный сервер, не требующий авторизации. Если же стоит более сложная задача – отправить письмо через почтовый сервер, требующий авторизации, по зашифрованному (SSL) каналу, то поможет только UTL_SMTP.

UTL_SMTP

    1. Отправка письма

      Этот пакет предоставляет полный набор возможностей соединения с почтовым сервером. Как следствие, необходимо вручную указать все параметры письма. Например, чтобы отправить письмо по адресу recipient@mail.com через smtp.server.com, необходимо выполнить следующие операции:

      DECLARE
          -- переменная, представляющая smtp-соединение
          mail_conn UTL_SMTP.connection;
      BEGIN
          -- установка соединения
          mail_conn := UTL_SMTP.open_connection('smtp.server.com');
          -- подтверждение установки связи
          UTL_SMTP.helo(mail_conn, 'smtp.server.com');
          -- установка адреса отправителя
          UTL_SMTP.mail(mail_conn, 'sender@mail.com');
          -- установка адреса получателя
          UTL_SMTP.rcpt(mail_conn, 'recipient@mail.com');
          -- отправка команды data, после которой можно начать передачу письма
          UTL_SMTP.open_data(mail_conn);
      
          -- отправка заголовков письма: дата, "от", "кому", "тема"
          UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss', 'NLS_DATE_LANGUAGE = RUSSIAN') || UTL_TCP.crlf);
          UTL_SMTP.write_data(mail_conn, 'From: sender@mail.com' || UTL_TCP.crlf);
          UTL_SMTP.write_data(mail_conn, 'To: recipient@mail.com' || UTL_TCP.crlf);
          UTL_SMTP.write_raw_data(mail_conn, UTL_RAW.cast_to_raw('Subject: message title' || UTL_TCP.crlf));
      
          -- текст письма
          UTL_SMTP.write_raw_data(mail_conn, UTL_RAW.cast_to_raw(UTL_TCP.crlf || 'Some letter for you.'));
      
          -- передача сигнала о завершении передачи сообщения
          UTL_SMTP.close_data(mail_conn);
          -- завершение сессии и закрытие соединения с сервером
          UTL_SMTP.quit(mail_conn);
      EXCEPTION
          -- если произошла ошибка передачи данных, закрыть соединение и вернуть
          -- ошибку передачи письма
          WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
          BEGIN
              UTL_SMTP.quit(c);
          EXCEPTION
              -- Если SMTP сервер недоступен, соединение с сервером отсутствует.
              -- Вызов QUIT приводит к ошибке. Обработка исключения позволяет
              -- игнорировать эту ошибку.
              WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
                  NULL;
          END;
      
          raise_application_error(-20000, 'Ошибка отправки почты: ' || SQLERRM);
      END;
    2. Кодировка

      Чтобы составить письмо в правильной кодировке, необходимо выяснить какой язык установлен в региональных настройках БД Oracle.

      SELECT VALUE INTO nls_charset
      FROM nls_database_parameters
      WHERE parameter = 'NLS_CHARACTERSET';

      Далее, во время передачи заголовков письма необходимо передать параметры кодировки текста:

      UTL_SMTP.write_data(mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
      UTL_SMTP.write_data(mail_conn, 'Content-Type: text/plain; charset="windows-1251"' || UTL_TCP.crlf);
      UTL_SMTP.write_data(mail_conn, 'Content-Transfer-Encoding: 8bit' || UTL_TCP.crlf);
      

      Чтобы правильно читалась тема письма, ее необходимо перекодировать в UTF8. Это можно сделать с помощью следующей функции:

      CREATE FUNCTION SUBJ_ENCODE(SUBJ_STR VARCHAR2) RETURN VARCHAR2
      -- Функция для перекодировки заголовка письма в utf-8
      AS
          a varchar2(1000);
          b varchar2(24);
          result varchar2(4000);
      BEGIN
          a := subj_str;
      
          WHILE LENGTH(a) > 24
          LOOP
              b := SUBSTR(a, 1, 24);
              a := SUBSTR(a, 25);
              RESULT :=
                  RESULT || '=?UTF-8?B?'
                  || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(CONVERT(b, 'utf8'))))
                  || '?=';
          END LOOP;
      
          IF LENGTH(a) > 0 THEN
              RESULT :=
                  RESULT || '=?UTF-8?B?'
                  || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(CONVERT(a, 'utf8'))))
                  || '?=';
          END IF;
      
          RETURN RESULT;
      END;

      Тогда передавать тему письма следует следующим образом:

      DECLARE subject varchar2(2000);
       ... 
      BEGIN
       ... 
           UTL_SMTP.write_raw_data (mail_conn, UTL_RAW.cast_to_raw('Subject: ' || subj_encode(NVL(subject, '(no subject)')) || UTL_TCP.crlf));
       ...
      END;
    3. Авторизация на SMTP-сервере

      Если почтовый сервер требует авторизации, то после установки и проверки соединения необходимо передать логин и пароль.

      utl_smtp.command(mail_conn, 'auth login');
      utl_smtp.command(mail_conn, utl_encode.text_encode('login', nls_charset, 1));
      utl_smtp.command(mail_conn, utl_encode.text_encode('password', nls_charset, 1));
      

       

    4. Работа UTL_SMTP по зашифрованному каналу (SSL)

      В первую очередь следует подчеркнуть, что UTL_SMTP не поддерживает соединение по SSL. Для обхода этой проблемы необходимо использовать дополнительные средства, позволяющие перенаправлять данные через SSL соединение. Из множества возможных вариантов удобно использовать Stunnel (на такое решение натолкнула заметка PL/SQL and Gmail (or UTL_SMTP with SSL)).

      Stunnel прост в установке и настройке. Имеется версия для Windows и Linux. Загрузить необходимые файлы можно с сайта Stunnel. Установка под Windows не вызывает никаких вопросов. Сборка из исходников под Linux требует наличия библиотеки SSL. Если таковая отсутствует, подойдет OpenSSL.

      После успешной установки необходимо настроить транспорт с помощью конфигурационного файла stunnel.conf. Местонахождение файла:

      • для Windows: %SYSTEMROOT%/system32/stunnel.conf
      • для linux: по-умолчанию установка идет в /usr/local, соответственно файл находится в /usr/local/etc/stunnel/stunnel.conf

      В этом файле необходимо указать следующие настройки:

      ; Use it for client mode
      client = yes
      
      [ssmtp]
      accept = 465
      connect = smtp.server.com:465

      Тем самым мы указали, что все обращения к системе по 465 порту следует перенаправлять на 465 порт почтового сервера smtp.server.com. Теперь можно запустить сервис stunnel.exe (Windows) или stunnel (Linux) и проверить правильность соединения. Для этого из командной строки следует запустить команду:

      telnet localhost 465

      Если все настроено верно, то должен появиться примерно такой ответ:

      220 smtp.server.com mail server

      Завершить соединение можно командой quit.

      quit
      221 2.0.0 Bye
      Connection closed by foreign host.
    5. Установка прав доступа ACL (только для Oracle 11g)

      Чтобы пользователь Oracle имел возможность устанавливать соединение с помощью пакета UTL_SMTP, ему необходимо создать соответствующие разрешения.

      -- создание списка доступа к установке соединения (на этом этапе сразу добавляется первый пользователь USER1)
      EXECUTE DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('acl_for_sendmail.xml', 'ACL for sending mail', 'USER1', TRUE, 'connect');
      -- задается адрес сервера и порт, куда идет подключение (localhost:465)
      EXECUTE DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('acl_for_sendmail.xml', 'localhost', 465);
      commit;
      
      -- добавление еще одного пользователя (USER2) в созданный список
      EXECUTE DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('acl_for_sendmail.xml', 'USER2', TRUE, 'connect');
      commit;
      
      -- проверка, что только что созданный список появился в БД
      SELECT host, lower_port, upper_port, acl FROM dba_network_acls
      -- просмотр списка пользователей, добавленных в список
      select acl, principal, privilege, is_grant, invert from dba_network_acl_privileges
      
      -- удалить весь список можно следующей командой
      EXECUTE DBMS_NETWORK_ACL_ADMIN.DROP_ACL('acl_for_sendmail.xml');
      commit;

      После этого все готово для отправки почты.

    6. Финальный пакет для работы с UTL_SMTP

      Собирая все вместе, получился вот такой пакет для отправки писем по SSL каналу.

      CREATE PACKAGE UTILS_MAIL
      AS
          mailhost VARCHAR2(30) := 'localhost'; -- сервер почты
          sender VARCHAR2(30) := 'sender@mail.com'; -- отправитель
      
          FUNCTION SUBJ_ENCODE(SUBJ_STR VARCHAR2) RETURN VARCHAR2;
          PROCEDURE SENDMAIL(RECIPIENT VARCHAR2, SUBJECT VARCHAR2, MESSAGE VARCHAR2);
      END;
      /
      
      CREATE OR REPLACE PACKAGE BODY IVANOV.UTILS_MAIL AS
          CREATE FUNCTION SUBJ_ENCODE(SUBJ_STR VARCHAR2) RETURN VARCHAR2
          -- Функция для перекодировки заголовка письма в utf-8
          AS
              a varchar2(1000);
              b varchar2(24);
              result varchar2(4000);
          BEGIN
              a := subj_str;
      
              WHILE LENGTH(a) > 24
              LOOP
                  b := SUBSTR(a, 1, 24);
                  a := SUBSTR(a, 25);
                  RESULT :=
                      RESULT || '=?UTF-8?B?'
                      || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(CONVERT(b, 'utf8'))))
                      || '?=';
              END LOOP;
      
              IF LENGTH(a) > 0 THEN
                  RESULT :=
                      RESULT || '=?UTF-8?B?'
                      || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(CONVERT(a, 'utf8'))))
                      || '?=';
              END IF;
      
              RETURN RESULT;
          END;
      
          PROCEDURE SENDMAIL(RECIPIENT VARCHAR2, SUBJECT VARCHAR2, MESSAGE VARCHAR2)
          -- Процедура для отправки письма
          AS
              mail_conn utl_smtp.connection;
              nls_charset varchar2(255);
          BEGIN
              select value
              into nls_charset
              from nls_database_parameters
              where parameter = 'NLS_CHARACTERSET';
      
              -- установка соединения
              mail_conn := UTL_SMTP.open_connection(mailhost, 465);
              UTL_SMTP.helo(mail_conn, mailhost);
              UTL_SMTP.command(mail_conn, 'auth login');
              UTL_SMTP.command(mail_conn, UTL_ENCODE.text_encode('login', nls_charset, 1));
              UTL_SMTP.command(mail_conn, UTL_ENCODE.text_encode('password', nls_charset, 1));
              UTL_SMTP.mail(mail_conn, sender);
              UTL_SMTP.rcpt(mail_conn, recipient);
              UTL_SMTP.open_data(mail_conn);
      
              -- передача данных: заголовки
              UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss', 'NLS_DATE_LANGUAGE = RUSSIAN') || UTL_TCP.crlf);
              UTL_SMTP.write_data(mail_conn, 'From: ' || sender || UTL_TCP.crlf);
              UTL_SMTP.write_data(mail_conn, 'To: ' || recipient || UTL_TCP.crlf);
              UTL_SMTP.write_raw_data
                  (mail_conn,
                  UTL_RAW.cast_to_raw('Subject: ' || subj_encode(NVL(subject, '(no subject)')) || UTL_TCP.crlf));
      
              -- передача данных: кодировки
              UTL_SMTP.write_data(mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
              UTL_SMTP.write_data(mail_conn, 'Content-Type: text/plain; charset="windows-1251"' || UTL_TCP.crlf);
              UTL_SMTP.write_data(mail_conn, 'Content-Transfer-Encoding: 8bit' || UTL_TCP.crlf);
      
              -- передача данных: текст письма
              UTL_SMTP.write_raw_data(mail_conn, UTL_RAW.cast_to_raw(UTL_TCP.crlf || message));
      
              -- завершение соединения
              UTL_SMTP.close_data(mail_conn);
              UTL_SMTP.quit(mail_conn);
          EXCEPTION
              WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
              BEGIN
                  UTL_SMTP.quit(c);
              EXCEPTION
                  WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
                      NULL;
              END;
          END;
      END;