PL/SQL: отправка электронной почты на примерах кода

За прошедшие годы отправка электронной почты из хранимых процедур в Oracle постепенно упрощалась. Короткий пример:

/* Требует Oracle10g и выше */
BEGIN
   UTL_MAIL.send(
      sender     =>   Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.'
     ,recipients =>   Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.'
     ,subject    =>   'API for sending email'
     ,message    =>
'Dear Friend:
This is not spam. It is a mail test.
Mailfully Yours,
Bill'
   );
END;

При выполнении этого блока Oracle пытается отправить сообщение с использованием хоста SMTP (Simple Mail Transfer Protocol), настроенного в файле инициализации (см. следующий раздел). Заголовок UTL_MAIL.SEND выглядит так:

PROCEDURE send(sender IN VARCHAR2,
               recipients IN VARCHAR2,
               cc IN VARCHAR2 DEFAULT NULL,
               bcc IN VARCHAR2 DEFAULT NULL,
               subject IN VARCHAR2 DEFAULT NULL,
               message IN VARCHAR2 DEFAULT NULL,
               mime_type IN VARCHAR2
                            DEFAULT 'text/plain; charset=us-ascii',
               priority IN PLS_INTEGER DEFAULT 3);

Большинство параметров понятно без пояснений. Один нетривиальный совет по использованию: чтобы отправить сообщение нескольким получателям (в том числе и в полях cc и bcc), разделите адреса запятыми: recipients => 'Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript., Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.'

Если вы работаете в более ранней версии Oracle или хотите в большей степени контролировать процесс отправки, вы, как и прежде, можете использовать пакет UTL_SMTP — процедура получается чуть более сложной, но тем не менее работоспособной. Если отправка почты должна программироваться на еще более низком уровне, используйте UTL_TCP, внешнюю процедуру или хранимую процедуру Java.

Предварительная настройка

К сожалению, не все версии Oracle предоставляют готовый сервис отправки электронной почты. Встроенный пакет UTL_SMTP включается в установку по умолчанию, поэтому он обычно работает сразу. Но если вы работаете в Oracle11g Release 2, вам придется еще немного повозиться с настройкой безопасности.

Начиная с Oracle10g пакет UTL_MAIL не включается в стандартную установку Oracle. Чтобы подготовить UTL_MAIL к использованию, ваш администратор должен:

1. Присвоить значение параметру инициализации В Oracle10g Release 2 и выше это делается примерно так:

ALTER SYSTEM SET SMTP_OUT_SERVER = 'mailhost';

В Oracle10g Release 1 для настройки параметра приходилось вручную редактировать pfile. Строка содержит имена одного или нескольких хостов (разделенных запятыми); UTL_MAIL последовательно перебирает их, пока не найдет подходящий.

1.  После настройки параметра перезагрузить сервер базы данных, чтобы изменения вступили в силу. Удивительно, но факт.
2. Выполнить следующие сценарии с правами SYS:

@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

1.  Предоставить привилегии выполнения UTL_MAIL тем, кто будет пользоваться этим пакетом:

GRANT EXECUTE ON UTL_MAIL TO SCOTT;

Настройка сетевой безопасности

В Oracle11g Release 2 администратору базы данных придется проделать еще одно действие для любого пакета, выполняющего внешние сетевые вызовы, — к их числу относятся и UTL_SMTP с UTL_MAIL. Администратор должен создать список ACL (Access Control List), включить в него имя пользователя или роль и предоставить списку привилегию сетевого уровня. Простая заготовка ACL для этой цели может выглядеть так:

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( 
       acl          => 'mail-server.xml'
      ,description  => 'Permission to make network connections to mail server' 
      ,principal    =>	'SCOTT' /* Имя пользователя или роль */
      ,is_grant     =>	TRUE
      ,privilege    =>	'connect'
   );

   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( 
       acl          =>	'mail-server.xml'
      ,host         =>	'my-STMP-servername'
      ,lower_port   =>	25   /* Сетевой порт	SMTP	по умолчанию */
      ,upper_port   =>	NULL  /* Открывается	только порт    25   */
   );
END;

В наши дни сетевому администратору также иногда приходится вносить изменения в настройку брандмауэра, чтобы разрешить исходящие подключения через порт 25 с сервера базы данных. Возможно, администратору электронной почты тоже придется задать дополнительные разрешения!

Отправка короткого текстового сообщения

В предыдущем разделе было показано, как отправить текстовое сообщение средствами UTL_MAIL. Если вы используете UTL_SMTP, вашей программе придется взаимодействовать с почтовым сервером на более низком уровне: она должна открыть подключение, сформировать заголовки, отправить тело сообщения и (в идеале) проанализировать возвращаемые значения. На рис. 1 изображена схема взаимодействия между почтовым сервером и почтовым клиентом PL/SQL send_mail_via_utl_smtp.

Код этой простой хранимой процедуры:

1   PROCEDURE send_mail_via_utl_smtp
2     ( sender IN VARCHAR2
3      ,recipient IN VARCHAR2
4      ,subject IN VARCHAR2 DEFAULT NULL
5      ,message IN VARCHAR2
6      ,mailhost IN VARCHAR2 DEFAULT 'mailhost'
7      )
8   IS
9     mail_conn UTL_SMTP.connection;
10    crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
11    smtp_tcpip_port CONSTANT PLS_INTEGER := 25;
12  BEGIN
13    mail_conn := UTL_SMTP.OPEN_CONNECTION(mailhost, smtp_tcpip_port);
14    UTL_SMTP.HELO(mail_conn, mailhost);
15    UTL_SMTP.MAIL(mail_conn, sender);
16    UTL_SMTP.RCPT(mail_conn, recipient);
17    UTL_SMTP.DATA(mail_conn, SUBSTR(
18       'Date: ' || TO_CHAR(SYSTIMESTAMP, 'Dy, dd Mon YYYY HH24:MI:SS TZHTZM')
19       || crlf || 'From: ' || sender || crlf
20       || 'Subject: ' || subject || crlf
21       || 'To: ' || recipient || crlf
22       || message
23     , 1, 32767));
24
25    UTL_SMTP.QUIT(mail_conn);
26  END;

Рис. 1. Схема взаимодействия между почтовым клиентом PL/SQL и сервером SMTP

Ключевые аспекты этого кода перечислены в следующей таблице.

Из строк 17-23 видно, что эта процедура не может отправить сообщение, у которого длина части DATA превышает 32 767 байт (максимальная длина переменных PL/ SQL). Пакет UTL_SMTP позволяет отправлять и более длинные сообщения, но вам придется организовать потоковую запись данных с использованием многократных вызовов UTL_SMTP.WRITE_DATA.

Большинство почтовых программ ограничивает каждую строку текста 78 символами и двумя символами-завершителями. В общем случае рекомендуется не включать в каждую строку текста более 998 символов помимо символов возврата курсора/перевода строки (1000 байт, если считать завершители CR/LF). Не превышайте предел в 1000 байт, если вы не уверены в том, что ваш сервер реализует расширения SMTP «Service Extension».

Включение «удобных» имен в адреса электронной почты

Если вызвать приведенную выше процедуру следующим образом:

BEGIN
   send_mail_via_utl_smtp(Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.',
      Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.', 'mail demo', NULL);
END;

«видимые» заголовки сообщения, генерируемые строками 17-21, будут выглядеть примерно так:

Date: Wed, 23 Mar 2005 17:14:30 -0600 
From: Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript. 
Subject: mail demo
To: Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.

Люди (и многие программы для борьбы со спамом) предпочитают видеть в заголовках реальные имена в форме:

Date: Wed, 23 Mar 2005 17:14:30 -0600 
From: Bob Swordfish <Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.>
Subject: mail demo
To: "Scott Tiger, Esq.” <Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.>

Конечно, такое изменение можно внести несколькими способами; пожалуй, самое элегантное решение основано на разборе параметров отправителя и получателя. Именно такое решение Oracle использует в UTL_MAIL. Итак, например, UTL_MAIL.SEND можно вызывать с адресами следующего вида:

["]удобочитаемое имя["] <адрес_электронной_почты>

как в следующем примере:

BEGIN
   UTL_MAIL.send('Bob Swordfish <Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.>',
      '"Scott Tiger, Esq." <Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.>', 
      subject=>'mail demo');
END;

Но следует учитывать, что пакет Oracle также добавляет информацию о наборе символов, поэтому приведенный код генерирует заголовок следующего вида:

Date: Sat, 24 Jan 2009 17:47:00 ?0600 (CST)
From: Bob Swordfish <Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.>
To: Scott Tiger, Esq. <Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.>
Subject: =?WINDOWS-1252?Q?mail=20demo?=

И хотя для пользователей, привыкших к ASCII, такая запись может показаться странной, в области интернет-стандартов она вполне допустима; разумный почтовый клиент все равно должен интерпретировать информацию кодировки (вместо того, чтобы просто выводить ее). Одной из очевидных модификаций процедуры send_mail_via_utl_smtp может стать добавление параметров для удобочитаемых имен (или преобразование существующих параметров в записи).

Отправка текстового сообщения произвольной длины

Пакет UTL_MAIL удобен, но при отправке текстового сообщения, длина которого превышает 32 767 байт, он вам не поможет. Чтобы обойти это ограничение, можно изменить процедуру send_mail_via_utl_smtp так, чтобы параметр message имел тип данных CLOB. Также придется внести ряд других изменений:

/* File on web: send_clob.sp */
PROCEDURE send_clob_thru_email (
   sender    IN VARCHAR2
 , recipient IN VARCHAR2
 , subject   IN VARCHAR2 DEFAULT NULL
 , MESSAGE   IN CLOB
 , mailhost  IN VARCHAR2 DEFAULT 'mailhost'
)
IS
   mail_conn         UTL_SMTP.connection;
   crlf              CONSTANT VARCHAR2 (2) := CHR (13) || CHR (10);
   smtp_tcpip_port   CONSTANT PLS_INTEGER := 25;
   pos               PLS_INTEGER := 1;
   bytes_o_data      CONSTANT PLS_INTEGER := 32767;
   offset            PLS_INTEGER := bytes_o_data;
   msg_length        CONSTANT PLS_INTEGER := DBMS_LOB.getlength (MESSAGE);
BEGIN
   mail_conn := UTL_SMTP.open_connection (mailhost, smtp_tcpip_port);
   UTL_SMTP.helo (mail_conn, mailhost);
   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 (SYSTIMESTAMP, 'Dy, dd Mon YYYY HH24:MI:SS TZHTZM')
      || crlf
      || 'From: '
      || sender
      || crlf
      || 'Subject: '
      || subject
      || crlf
      || 'To: '
      || recipient
      || crlf
   );

   WHILE pos < msg_length
   LOOP
      UTL_SMTP.write_data (mail_conn, DBMS_LOB.SUBSTR (MESSAGE, offset, pos));
      pos := pos + offset;
      offset := LEAST (bytes_o_data, msg_length - offset);
   END LOOP;

   UTL_SMTP.close_data (mail_conn);

   UTL_SMTP.quit (mail_conn);
END send_clob_thru_email;

Вызовы open_data, write_data и close_data позволяют передать произвольное количество байтов почтовому серверу (до максимального размера почтового сообщения, установленного сервером). Учтите, что в этом коде делается одно серьезное допущение: предполагается, что данные CLOB были разбиты на строки правильной длины.

Теперь давайте посмотрим, как присоединить файл к сообщению.

Отправка сообщения с коротким вложением

Исходный стандарт электронной почты требовал, чтобы все сообщения состояли только из 7-разрядных ASCII-символов¹. Но как известно, в сообщениях могут присутствовать вложения, которые обычно хранятся в двоичном, а не в текстовом формате. Как передать двоичный файл в сообщении ASCII? Обычно для пересылки вложений используются почтовые расширения MIME1 (Multipurpose Internet Mail Extensions) в сочетании со схемой преобразования двоичных данных в ASCII base64. Следующий пример показывает, как происходит пересылка небольшого двоичного файла:

Date: Wed, 01 Apr 2009 10:16:51 ?0600
From: Bob Swordfish <Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.>
MIME-Version: 1.0
To: Scott Tiger <Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.>
Subject: Attachment demo
Content-Type: multipart/mixed;
 boundary="------------060903040208010603090401"

This is a multi-part message in MIME format.
--------------060903040208010603090401
Content-Type: text/plain; charset=us-ascii; format=fixed
Content-Transfer-Encoding: 7bit

Dear Scott:

I'm sending a gzipped file containing the text of the first
paragraph. Hope you like it.

Bob
--------------060903040208010603090401
Content-Type: application/x-gzip; name="hugo.txt.gz"
Content-Transfer-Encoding: base64
Content-Disposition: inline; filename="hugo.txt.gz"

H4sICDh/TUICA2xlc21pcy50eHQAPY5BDoJAEATvvqI/AJGDxjMaowcesbKNOwmZITsshhf7
DdGD105Vpe+K5tQc0Jm6sGScU8gjvbrmoG8Tr1qhLtSCbs3CEa/gaMWTTbABF3kqa9z42+dE
RXhYmeHcpHmtBlmIoBEpREyZLpERtjB/aUSxns5/Ci7ac/u0P9a7Dw4FECSdAAAA
--------------060903040208010603090401--

Хотя большая часть кода может быть стандартной, однако при генерировании таких сообщений необходимо следить за множеством технических деталей. К счастью, при отправке «небольших» вложений (с длиной менее 32 767 байт) из Oracle10g и выше на помощь приходит пакет UTL_MAIL. В следующем примере используется процедура UTL_MAIL.SEND_ATTACH_VARCHAR2, которая отправляет вложения в текстовом виде. Отправка приведенного выше сообщения выполняется следующим образом:

DECLARE
   b64 VARCHAR2(512) := 'H4sICDh/TUICA2xlc21...'; -- etc., as above
   txt VARCHAR2(512) := 'Dear Scott: ...'; -- etc., as above
BEGIN
   UTL_MAIL.send_attach_varchar2(
      sender => Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.'
     ,recipients => Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.'
     ,message => txt
     ,subject => 'Attachment demo'
     ,att_mime_type => 'application/x-gzip'
     ,attachment => b64
     ,att_inline => TRUE
     ,att_filename => 'hugo.txt.gz'
   );
END;

Новые параметры описаны в следующей таблице.

Типы MIME не могут выбираться произвольно; они, как и многие другие технические аспекты Интернета, определяются комитетом IANA (Internet Assigned Numbers Authority).

Среди часто используемых типов содержимого MIME можно выделить text/plain, multipart/mixed, text/html, application/pdf и application/msword. Полный список приведен на странице IANA по адресу http://www.iana.org/assignments/media-types/. Вероятно, вы заметили, что для присоединения к сообщению файла в кодировке base64 пришлось основательно потрудиться. Давайте подробнее рассмотрим действия, необходимые для преобразования двоичного файла в объект, пригодный для пересылки.

Отправка небольшого файла во вложении

Чтобы преобразовать небольшой двоичный файл в объект, который можно отправить в сообщении электронной почты, можно прочитать содержимое файла в переменную RAW и воспользоваться процедурой UTL_MAIL.SEND_ATTACH_RAW. Oracle преобразует двоичные данные в кодировку base64 и формирует необходимые директивы MIME. Скажем, пересылка файла /tmp/hugo.txt.gz (размером менее 32 767 байт) может быть выполнена следующим образом:

/* File on web: send_small_file.sql */
CREATE OR REPLACE DIRECTORY tmpdir AS '/tmp'
/
DECLARE
   the_file BFILE := BFILENAME('TMPDIR', 'hugo.txt.gz');
   rawbuf RAW(32767);
   amt PLS_INTEGER :=  32767;
   offset PLS_INTEGER := 1;
BEGIN
   DBMS_LOB.fileopen(the_file, DBMS_LOB.file_readonly);
   DBMS_LOB.read(the_file, amt, offset, rawbuf);
   UTL_MAIL.send_attach_raw
   (
      sender => Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.'
     ,recipients => Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript.'
     ,subject => 'Attachment demo'
     ,message => 'Dear Scott...'
     ,att_mime_type => 'application/x-gzip'
     ,attachment => rawbuf
     ,att_inline => TRUE
     ,att_filename => 'hugo.txt.gz'
   );

   DBMS_LOB.close(the_file);
END;

Если пакет UTL_MAIL недоступен, используйте инструкции, приведенные в следующем разделе. 

Вложение файла произвольного размера

Для отправки вложения большего размера можно воспользоваться традиционным пакетом UTL_SMTP; вложение преобразуется в кодировку base64 средствами встроенного пакета Oracle UTL_ENCODE. Следующая процедура отправляет BFILE с коротким текстовым сообщением:

/* File on web: send_bfile.sp */
 1    PROCEDURE send_bfile
 2      ( sender IN VARCHAR2
 3       ,recipient IN VARCHAR2
 4       ,subject IN VARCHAR2 DEFAULT NULL
 5       ,message IN VARCHAR2 DEFAULT NULL
 6       ,att_bfile IN OUT BFILE
 7       ,att_mime_type IN VARCHAR2
 8       ,mailhost IN VARCHAR2 DEFAULT 'mailhost'
 9       )
10    IS
11      crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
12      smtp_tcpip_port CONSTANT PLS_INTEGER := 25;
13      bytes_per_read CONSTANT PLS_INTEGER := 23829;
14      boundary CONSTANT VARCHAR2(78) := '-------5e9i1BxFQrgl9cOgs90-------';
15      encapsulation_boundary CONSTANT VARCHAR2(78) := '--' || boundary;
16      final_boundary CONSTANT VARCHAR2(78) := '--' || boundary || '--';
17
18      mail_conn  UTL_SMTP.connection;
19      pos PLS_INTEGER := 1;
20      file_length PLS_INTEGER;
21
22      diralias VARCHAR2(30);
23      bfile_filename VARCHAR2(512);
24      lines_in_bigbuf PLS_INTEGER := 0;
25
26      PROCEDURE writedata (str IN VARCHAR2, crlfs IN PLS_INTEGER DEFAULT 1)
27      IS
28      BEGIN
29         UTL_SMTP.write_data(mail_conn, str || RPAD(crlf, 2 * crlfs, crlf));
30      END;
31
32    BEGIN
33      DBMS_LOB.fileopen(att_bfile, DBMS_LOB.LOB_READONLY);
34      file_length := DBMS_LOB.getlength(att_bfile);
35
36      mail_conn := UTL_SMTP.open_connection(mailhost, smtp_tcpip_port);
37      UTL_SMTP.helo(mail_conn, mailhost);
38      UTL_SMTP.mail(mail_conn, sender);
39      UTL_SMTP.rcpt(mail_conn, recipient);
40
41      UTL_SMTP.open_data(mail_conn);
42      writedata('Date: ' || TO_CHAR(SYSTIMESTAMP,
43                   'Dy, dd Mon YYYY HH24:MI:SS TZHTZM') || crlf
44          || 'MIME-Version: 1.0' || crlf
45          || 'From: ' || sender || crlf
46          || 'Subject: ' || subject || crlf
47          || 'To: ' || recipient || crlf
48          || 'Content-Type: multipart/mixed; boundary="' || boundary || '"', 2);
49
50      writedata(encapsulation_boundary);
51      writedata('Content-Type: text/plain; charset=ISO-8859-1; format=flowed');
52      writedata('Content-Transfer-Encoding: 7bit', 2);
53      writedata(message, 2);
54
55      DBMS_LOB.filegetname(att_bfile, diralias, bfile_filename);
56      writedata(encapsulation_boundary);
57      writedata('Content-Type: '
58         || att_mime_type || '; name="' || bfile_filename || '"');
59      writedata('Content-Transfer-Encoding: base64');
60      writedata('Content-Disposition: attachment; filename="'
61         || bfile_filename || '"', 2);
62
63      WHILE pos < file_length
64      LOOP
65         writedata(UTL_RAW.cast_to_varchar2(
66                     UTL_ENCODE.base64_encode
67                        DBMS_LOB.substr(att_bfile, bytes_per_read, pos))), 0);
68         pos := pos + bytes_per_read;
69      END LOOP;
70
71      writedata(crlf || crlf || final_boundary);
72
73      UTL_SMTP.close_data(mail_conn);
74      UTL_SMTP.QUIT(mail_conn);
75      DBMS_LOB.CLOSE(att_bfile);
76    END;

Ключевые моменты этого кода перечислены в следующей таблице.

Да, нам когда-то тоже казалось, что отправка электронной почты — простое дело. К тому же эта процедура не обладает особой гибкостью: она позволяет отправить одну текстовую часть с вложением одного файла. Но по крайней мере вы можете взять ее за основу и доработать с учетом потребностей вашего собственного приложения.

И еще одно замечание по поводу построения правильно структурированых сообщений электронной почты: вместо того, чтобы зарываться в документацию RFC, попробуйте запустить почтового клиента, которого вы используете в повседневной работе, отправьте себе сообщение в форме, которую вы пытаетесь сгенерировать, а затем просмотрите исходный текст сообщения. Я столько раз проделывал это во время работы над этим блогом! Учтите, что некоторые почтовые клиенты (прежде всего Microsoft Outlook) не предоставляют средств для полного просмотра низкоуровневого текста.

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL... 4577 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48

Встроенные методы коллекций PL... 14414 просмотров sepia Tue, 29 Oct 2019, 09:54:01

Основы языка PL/SQL: использов... 4641 просмотров Ирина Светлова Tue, 06 Feb 2018, 14:04:03

Работа с числами в PL/SQL на п... 43508 просмотров Antoniy Mon, 28 May 2018, 16:45:11