Xpoint
   [напомнить пароль]

Проблема с русскими буквами в PL/SQL коде для отправки e-mail

Метки: руссификация, oracle, unicode
2005-08-02 17:01:29 [обр] alexabm М[досье]

Уважаемые коллеги! Подскажите как решить проблему. Приведу код на PL/SQL для отправки e-mail через Exchange Server.

DECLARE
c UTL_SMTP.CONNECTION;
BODYTXT VARCHAR2(4000) := 'Тестовое тело сообщения';
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
c := UTL_SMTP.OPEN_CONNECTION('VBNX4COM');
UTL_SMTP.HELO(c, 'ggg.com');
UTL_SMTP.MAIL(c, 'xyz@ggg.com');
UTL_SMTP.RCPT(c, 'xzz@ggg.com');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <xyz@ggg.com>');
send_header('To', '"Recipient" <xzz@ggg.com>');
send_header('Subject', 'Тестовое сообщение');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || BODYTXT);
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
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; -- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);
END;

В результате его выполнения e-mail нормально приходит адресату xzz@ggg.com. Но приходят корректно только все сообщения на английском, на русском же приходят вопросительные знаки вида: ?????????? ???????????. Прочитал в документации про возможность такого варианта и сделал отправку через функцию WRITE_RAW_DATA вместо WRITE_DATA. Теперь вместо вопросительных знаков приходят символы в другой кодировке: оЮЙЕРШ ПСЯЯЙХИ РЕЙЯР. Скорее всего в моем PL/SQL коде нужно где-то прописать кодировку KOI8-R. Вопрос: как корректно прописать и где? Или может быть есть еще какой-то способ? На сервере установлен Oracle 10. Код PL/SQL написан для последующей вставки в Oracle Portal.

спустя 1 час 36 минут [обр] GRAy(0/259)[досье]
С поправочкой на вашу кодировку ;) и можно без text/html.
    send_header('Content-Type', 'text/html;charset=windows-1251');
спустя 4 минуты [обр] GRAy(0/259)[досье]

Прошу пардона, это не всё оказывается...
вот процедура, которую я писал в своё время для тех же целей что и у вас, полностью:

  procedure sendMail(
   p_mailserv varchar2,
   p_recipient_email varchar2,
   p_recipient_alias varchar2,
   p_subj varchar2,
   p_body varchar2
  ) is
    subj raw(2000) := utl_raw.cast_to_raw(convert('Subject: '||p_subj|| UTL_TCP.CRLF,'CL8MSWIN1251','UTF8'));
    bod raw(32000) := utl_raw.cast_to_raw(convert(p_body,'CL8MSWIN1251','UTF8'));
    c UTL_SMTP.CONNECTION;
    PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
    BEGIN
      UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
    END;
  BEGIN
    c := UTL_SMTP.OPEN_CONNECTION(p_mailserv);
    UTL_SMTP.HELO(c, 'mail.mydomain.com');
    UTL_SMTP.MAIL(c, 'portal@mydomain.com');
    UTL_SMTP.RCPT(c, p_recipient_email);
    UTL_SMTP.OPEN_DATA(c);
    send_header('From','"Portal" <portal@mydomain.com>');
    send_header('To',p_recipient_alias||' '||p_recipient_email);
    UTL_SMTP.write_raw_data(c,subj);
    send_header('Content-Type', 'text/html;charset=windows-1251');
    UTL_SMTP.write_raw_data(c, bod);
    UTL_SMTP.CLOSE_DATA(c);
    UTL_SMTP.QUIT(c);
  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; -- When the SMTP server is down or unavailable, we don't have
                -- a connection to the server. The QUIT call will raise an
                -- exception that we can ignore.
      END;
      raise_application_error(-20000,
        'Failed to send mail due to the following error: ' || sqlerrm);
  END;

Обратите внимание, что я предварительно перекодирую строку varchar2 в отличную от Unicode кодировку.

спустя 19 часов [обр] alexabm М[досье]
GRAy
Попробовал ваш код. Но у меня по почте приходят вместо русских букв: ???????? ?????. Т.е. проблема та же что и в самом начале при посылке через функцию WRITE_DATA в вышеприведенном мной коде. Подскажите, что может быть не так?
спустя 35 минут [обр] GRAy(0/259)[досье]
Погодите, а вы что через write_data делаете сейчас? Т.е. пытаетесь перекодировать, а потом опять отправлять через write_data? Ничего не выйдет - в качестве параметра у write_data varchar2, насколько я помню, и, как следствие, опять двухбайтовая кодировка. Без write_raw_data не обойтись. В моём коде он как раз и используется.
Если нет, то тогда два варианта:
  1. NLS_LANG на сервере какой? Должен быть AMERICA_AMERICAN.CL8MSWIN1251 - (по крайней мере мой код работал в таких условиях)
  2. А может это ваш почтовый сервер шалит? У меня такое подозрение. Если можете, проверьте, что ему приходит и что он в результате отправлеяет.
спустя 2 часа 50 минут [обр] alexabm М[досье]

GRAy
Нет я естественно через write_raw_data отправлял. Проблема не в этом. Уже разобрался, немного модифицировав ваш код:

  subj raw(2000) := utl_raw.cast_to_raw(convert('Subject: '||p_subj,'UTF8','CL8MSWIN1251'));
  bod raw(32000) := utl_raw.cast_to_raw(convert(p_body,'UTF8','CL8MSWIN1251'));
........
  send_header('Content-Type', 'text/html;charset=UTF-8');
.......

Теперь все работает нормально. Большое спасибо за помощь.

спустя 2 часа 22 минуты [обр] GRAy(0/259)[досье]
Хм... если вы устанавливаете charset=UTF-8 по идее перекодировать нет необходимости (попробуйте проверить) в Oracle и так всё в Unicode.
спустя 1 год 10 месяцев [обр] myctuk[досье]

КОллеги,
Возникла аналогичная проблема. На тестовой домашней базе ора 9.2 проблем не было.
Допустим из БД нужно вернуть набор записей запросом:

SELECT SKIL_ID, SKIL_NAME,SKIL_DESC,SKIL_GRANT
FROM DZ_SKILL;

Если в качестве запроса используется пример выше, то всё отлично, если же:

создаются 2 типа:
CREATE OR REPLACE TYPE sel_skil_res AS OBJECT
       (res_id CHAR(12),
        res_name CHAR(128),
        res_desc CHAR(128),
        res_grant CHAR(3));
CREATE OR REPLACE TYPE sel_skil_table AS TABLE OF sel_skil_res;

А затем функция:
CREATE OR REPLACE
FUNCTION SEL_SKIL(row NUMBER) RETURN sel_skil_table PIPELINED
IS
out_skil sel_skil_res :=sel_skil_res(NULL,NULL,NULL,NULL);
countid NUMBER;
skilid CHAR(12);
name CHAR(128);
descr CHAR(128);
grantt CHAR(3);
BEGIN
   SELECT COUNT(SKIL_ID)
   INTO countid
   FROM DZ_SKILL;
   --
   IF countid<>0 THEN
      DECLARE
      CURSOR c_get_skil IS
        SELECT SKIL_ID, SKIL_NAME,SKIL_DESC,SKIL_GRANT
        FROM DZ_SKILL;
      BEGIN
        OPEN c_get_skil;
        FETCH c_get_skil INTO skilid, name, descr, grantt;
        WHILE c_get_skil%FOUND
        LOOP
          out_skil.RES_ID := skilid;
          out_skil.res_name := name;
          out_skil.res_desc := descr;
          out_skil.res_grant := grantt;
          pipe row(out_skil);
          --
        FETCH c_get_skil INTO skilid, name, descr, grantt;
        END LOOP;
        CLOSE c_get_skil;
      END;
   END IF;
   RETURN;
END;

то запрос вида:
SELECT * FROM TABLE(SEL_SKIL(0));

выдаёт в качестве содержимого ячеек каракули, есть предположение, что это CL8MSWIN1251.
обращение реализуется на PHP, но думаю это не важно в данном случае.

Причиной усложнение скриптов стало поставленная задача, условием которой было не допустить наличия в коде (в моем случае PHP) структуры таблиц.
Заранее благодарен.

спустя 3 часа 7 минут [обр] GRAy(0/259)[досье]
CHAR - однобайтовый тип данных. А вообще, достаточно странная постановка задачи и тем более способ её решения. Что значит "не допустить наличия в коде структуры таблиц" - sql injection испугались чтоли? Зачем применять pipeline функции, если не нужна какая-то сложная обработка строк? Зачем делать select count(skil_id) ... когда можно просто открыть курсор в for`е и цикл не случится ни разу если записей нет. Вообще без крайней нужды с оракловыми типами связываться не рекомендую ибо глючны всё ещё. Оракл - реляционная субд, и хороша именно как реляционная субд, имхо.
спустя 6 часов [обр] myctuk[досье]

это задание не рабочее, а университетское. возможно, я не понял первоначального замысла преподавателя, т.е. в результате получение, удаление, добавление и изменение данных должно быть организованно посредством вызова хранимых процедур или функций.
Но как это реализовать иначе? опыт pl/sql очень не большой.
да и PHP возвращает данные только в виде табличных данных.

буду очень благодарен, если покажете иной простой пример.
по поводу CHAR, чем он не подходит?
прошу прощение за такие банальные вопросы ...
вот собственно таблица:
CREATE TABLE dz_skill (
       skil_id CHAR(12) NOT NULL,
       skil_name CHAR(128) NOT NULL,
       skil_desc CHAR(128) NULL,
       skil_grant CHAR(3) NULL
) TABLESPACE "USERS";

отсылать к преподавателю поздно ... сессия уже пришла.
спасибо.

спустя 15 часов [обр] GRAy(0/259)[досье]
myctuk[досье] Заводите отдельный топик под свой вопрос, а то модератор обидется ;) Там полностью приведите условия вашей задачи так как её вам сформулировал преподаватель.
Powered by POEM™ Engine Copyright © 2002-2005