Oracle PLSQL - Handling Email Subject with mime header encoding in RFC2047 (Oracle 9i, 10g and 11g)

In Oracle 11g, it is very easy with function UTL_ENCODE.MIMEHEADER_ENCODE()


select utl_encode.MIMEHEADER_ENCODE('Oracle 發送 Mail 測試')  as t from dual;

you will get the mime header encoded string
=?UTF-8?Q?Oracle=20=E7=99=BC=E9=80=81=20Mail=20=E6=B8=AC=E8=A9=A6?=
then you can use this on UTL_SMTP
WRITE_DATA(L_CONN, 'Subject', utl_encode.MIMEHEADER_ENCODE('Oracle 發送 Mail 測試') );



In Oracle 9i & 10g

However this  does not work in earlier Oracle version like 9i.
Then you need to do this in three steps

1. get the current database character set
SELECT VALUE into L_CHARSET FROM v$nls_parameters  WHERE parameter LIKE 'NLS_CHARACTERSET' ;

2. convert the subject string (P_SUBJECT) to UTF8 character set
P_SUB := 'Oracle 發送 Mail 測試';
L_SUB := UTL_RAW.CAST_TO_RAW(CONVERT(P_SUBJECT,'UTF8',L_CHARSET));
now the  L_SUB will be 4F7261636C6520E799BCE98081204D61696C20E6B8ACE8A9A6



3. format it to the quoted mime header
source : 4F7261636C6520E799BCE98081204D61696C20E6B8ACE8A9A6
target :  =?UTF-8?Q?=4F=72=61=63=6C=65=20=E7=99=BC=E9=80=81=20=4D=61=69=6C=20=E6=B8=AC=E8=A9=A6?=
actually, we need to add a prefix "=?UTF-8?Q?" and a suffix "?=" to the source.
and add "=" every two characters to source

to do this we need a function regexp_replace() on 10g, however this is not available in 9i.  We are using one from Phil's Blog.  The code is attached at end of this page.

     L_SUB := '=?UTF-8?Q?'||regexp_replace( L_SUB, '(..)', '=\1' )||'?=';


then the L_SUB will be same as our target. Simply write this to subject header.

    WRITE_DATA(L_CONN, 'Subject', L_SUB );

REGEXP_REPLACE / REGEXP_LIKE for Oracle 9i 

from Phil's Blog

create or replace
FUNCTION regexp_replace (
   source_char       IN   VARCHAR2
 , pattern           IN   VARCHAR2
 , replace_string    IN   VARCHAR2
 , POSITION          IN   PLS_INTEGER DEFAULT 1
 , occurrence        IN   PLS_INTEGER DEFAULT 0
 , match_parameter   IN   VARCHAR2 DEFAULT NULL)
   RETURN VARCHAR2 AS
/*************************************************************************
 * Program  : regexp_replace
 * Version  : 1.0
 * Author   : Philip Moore
 * Date     : 20-JUN-2009 Anno Domini
 * Purpopse : This provides a pseudo "REGEXP_REPLACE" function for Oracle 9iR2
 * Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!
 *************************************************************************/
   -- Variables
   l_source_string                    VARCHAR2 (32767);
   l_temp_string                      VARCHAR2 (32767);
   l_flags                            VARCHAR2 (10);
   l_occurrence                       PLS_INTEGER;
   l_end_of_pattern_pos               PLS_INTEGER;
   l_string_pos                       PLS_INTEGER;
BEGIN
   -- Substr the source_char to start at the position specified
   l_source_string := SUBSTR (source_char, POSITION);

   -- Set up the flags argument
   IF occurrence = 0 THEN
      l_flags := 'g';
   ELSE
      l_flags := NULL;
   END IF;

   l_flags := l_flags || match_parameter;

   -- Now replace the regular expression pattern globally if "g"
   IF INSTR (l_flags, 'g') > 0 THEN
      owa_pattern.CHANGE (line          => l_source_string
                        , from_str      => pattern
                        , to_str        => replace_string
                        , flags         => l_flags);
   -- Not a global replace - loop until the "occurrence"th occurrence is replaced...
   ELSE
      l_string_pos := 0;
      l_occurrence := 0;
      WHILE l_string_pos < LENGTH (l_source_string) AND l_occurrence < occurrence LOOP
         l_string_pos := l_string_pos + 1;

         l_end_of_pattern_pos := owa_pattern.amatch (line          => l_source_string
                                                   , from_loc      => l_string_pos
                                                   , pat           => pattern
                                                   , flags         => match_parameter);

         IF l_end_of_pattern_pos != 0 THEN
            l_occurrence := l_occurrence + 1;
         END IF;
      END LOOP;

      IF l_occurrence = occurrence THEN
         l_temp_string := SUBSTR (l_source_string
                                , l_string_pos
                                , (l_end_of_pattern_pos - l_string_pos));
         owa_pattern.CHANGE (line          => l_temp_string
                           , from_str      => pattern
                           , to_str        => replace_string
                           , flags         => l_flags);
         l_source_string := SUBSTR (l_source_string
                                  , 1
                                  , l_string_pos - 1) || l_temp_string || SUBSTR (l_source_string, l_end_of_pattern_pos);
      END IF;
   END IF;

   -- Piece the string back together if needed...
   IF POSITION > 1 THEN
      l_source_string := SUBSTR (source_char
                               , 1
                               , (POSITION - 1)) || l_source_string;
   END IF;

   RETURN l_source_string;
END regexp_replace;
/


CREATE OR REPLACE FUNCTION regexp_like (source_char     IN VARCHAR2
                                      , pattern         IN VARCHAR2
                                      , match_parameter IN VARCHAR2 DEFAULT NULL)
RETURN INTEGER
AS
/*************************************************************************
 * Program  : regexp_like
 * Version  : 1.0
 * Author   : Philip Moore
 * Date     : 20-JUN-2009 Anno Domini
 * Purpopse : This provides a pseudo "REGEXP_LIKE" operator for Oracle 9iR2
 * Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!
 *************************************************************************/
  -- Variables
  l_return INTEGER;
BEGIN
   IF owa_pattern.match (line  => source_char
                       , pat   => pattern
                       , flags => match_parameter) THEN
      l_return := 1;
   ELSE
      l_return := 0;
   END IF;

   RETURN l_return;
END regexp_like;
/