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

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

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

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

Этот пакет предоставляет полный набор возможностей соединения с почтовым сервером. Как следствие, необходимо вручную указать все параметры письма. Например, чтобы отправить письмо по адресу 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;

Кодировка

Чтобы составить письмо в правильной кодировке, необходимо выяснить какой язык установлен в региональных настройках БД 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;

Авторизация на 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));
```
Работа 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.
```

Установка прав доступа 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;

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

Финальный пакет для работы с 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;

UTL_MAIL

UTL_SMTP

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

Кодировка

Авторизация на SMTP-сервере

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

Установка прав доступа ACL (только для Oracle 11g)

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

You Might Also Like

Полезные функции для Oracle

ORA-32794 “cannot drop a system-generated sequence” – неудаляемые последовательности в Oracle

Новое в Oracle 11gR2: снова о склейке нескольких записей в одну строку (listagg)