通過oracle，是同pl/sql實現發送mail功能。

功能建立之初，我被要求編寫oracle預存程序，並實現mail的發送。這個之前我做過，直接copy以前的代碼，輕鬆實現了。

為了做個記錄，我把代碼複製上來。代碼很容易懂，注釋也足夠，不多作解釋了。

CREATE OR REPLACE PACKAGE sendMailISPROCEDURE funcSendMail(sUid     IN       VARCHAR2);END sendMail;/CREATE OR REPLACE PACKAGE BODY sendMailIS  c utl_smtp.connection;  boundarystrend  varchar2(100):='--boundary_str--';  smtpserver      varchar2(100):='lcmail01.linkcafe.ne.jp';  boundarystr     varchar2(100):='boundary_str';  boundarystrline varchar2(100):='--boundary_str';  ExistAttachment boolean;PROCEDURE funcSendMail(                         sUid            VARCHAR2                      ) ISBEGINDECLARE  TYPE typMailData IS RECORD (       toAdd           DEWEY.CYSCC0370.D0120%TYPE  -- 宛先アドレス     , toName          DEWEY.CYSCC0370.D0120%TYPE  -- 宛先名稱     , ccAdd           DEWEY.CYSCC0370.D0120%TYPE  -- CCアドレス     , ccName          DEWEY.CYSCC0370.D0120%TYPE  -- CC名稱     , fromAdd         DEWEY.CYSCC0370.D0120%TYPE  -- 送信元アドレス     , fromName        DEWEY.CYSCC0370.D0120%TYPE  -- 送信元名稱     , title           DEWEY.CYSCC0370.D0041%TYPE  -- メールタイトル     , pContext        DEWEY.CYSCC0370.D0040%TYPE  -- メール本文      ) ;        MailData            typMailData ;  binaryData          BLOB;  rowCYSCT0291        DEWEY.CYSCT0291%ROWTYPE;         CURSOR getMail       IS   SELECT B.D0041 B_D0041  --タイトル        , B.D0120 B_D0120  --送信先メールアドレス        , B.D0100 B_D0100  --送信先事業所ID        , B.D0010 B_D0010  --送受信ナンバー        , A.D0030 A_D0030  --PDFデータ        , B.D0040 B_D0040  --送信內容        , B.D0020 B_D0020  --送信日     FROM DEWEY.CYSCT0291 A        , DEWEY.CYSCC0370 B    WHERE A.D0020 = B.D0010      AND B.D0044 IN ('0','4'); --0：未送信 1：送信中 2：送信完了 3：ｷｬﾝｾﾙ 4：送信ｴﾗｰ  flag varchar2(1);BEGIN  FOR nLoop IN getMail LOOP    flag := '1';         UPDATE DEWEY.CYSCC0370       SET D0020 = SYSDATE,           D0042 = sysdate,           D0044 = flag,           D0930 = SYSDATE,           D0940 = sUid     WHERE D0010 = nLoop.B_D0010         ;               MailData.toAdd    := nLoop.B_D0120;    MailData.toName   := '';    MailData.ccAdd    := null;    MailData.ccName   := null;    MailData.fromAdd  := 'masamichi.kaneda@u-s-systems.co.jp';    MailData.fromName := 'masamichi.kaneda@u-s-systems.co.jp';    MailData.title    := nLoop.B_D0041;    MailData.pContext := nLoop.B_D0040;    -- メール基本部分作成    if openMail(MailData.toAdd,    -- 宛先アドレス                MailData.toName,   -- 宛先名                MailData.ccAdd,    -- CCアドレス                MailData.ccName,   -- CC名                MailData.fromAdd,  -- 送信元アドレス                MailData.fromName, -- 送信元名                MailData.title,    -- メールタイトル                MailData.pContext  -- メール本文                ) = false then      flag := '4';    else      flag := '2';      -- 添付ファイル      if addAttachmentFile('application/pdf',                           nLoop.B_D0100||to_char(nLoop.B_D0020,'yyyymmdd')||'.pdf',                           nLoop.A_D0030)  = false then        flag := '4';      ELSE        flag := '2';        utl_smtp.write_data(c,boundarystrend|| utl_tcp.CRLF);--區切り文字        utl_smtp.close_data(c);        utl_smtp.quit(c);      end if;    end if;    IF flag = '2' THEN      UPDATE DEWEY.CYSCC0370         SET D0043 = sysdate       WHERE D0010 = nLoop.B_D0010         ;    END IF;         UPDATE DEWEY.CYSCC0370       SET D0020 = SYSDATE         , D0044 = flag         , D0930 = SYSDATE         , D0940 = sUid     WHERE D0010 = nLoop.B_D0010         ;            END LOOP;  EXCEPTION  WHEN OTHERS THEN    rollback;      END ;END funcSendMail;FUNCTION openMail(   mailto      IN VARCHAR2,                     totext      IN VARCHAR2,                     mailcc      IN VARCHAR2,                     cctext      IN VARCHAR2,                     mailfrom    IN VARCHAR2,                     fromtext    IN VARCHAR2,                     title       IN VARCHAR2,                     mailbody    IN VARCHAR2  ) RETURN BOOLEAN IS  BEGIN      DECLARE          cc  varchar2(1000);          pos integer;      BEGIN          c := utl_smtp.open_connection(smtpserver);          utl_smtp.helo(c, smtpserver);          utl_smtp.mail(c, mailfrom);          utl_smtp.rcpt(c, mailto);          IF mailcc IS NOT NULL AND cctext IS NOT NULL THEN            utl_smtp.rcpt(c, mailcc);          END IF;          utl_smtp.open_data(c);          utl_smtp.write_data(c,'From: "=?iso-2022-jp?B?');          utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(fromtext) ));          utl_smtp.write_data(c,'?=" <'||mailfrom||'>'|| utl_tcp.CRLF);          utl_smtp.write_data(c,'To: "=?iso-2022-jp?B?');          IF totext IS NULL THEN             utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(' ') ));          ELSE             utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(totext) ));          END IF;          utl_smtp.write_data(c,'?=" <'||mailto||'>'|| utl_tcp.CRLF);          IF mailcc IS NOT NULL AND cctext IS NOT NULL THEN            utl_smtp.write_data(c,'Cc: "=?iso-2022-jp?B?');            utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(cctext) ));            utl_smtp.write_data(c,'?=" <'||mailcc||'>'|| utl_tcp.CRLF);          END IF;                    utl_smtp.write_data(c,'Subject: =?iso-2022-jp?B?');          --バイナリサイズが３の倍數でないとbase64_encodeがおかしくなる          cc:='';          for pos in 1..length(title) LOOP            cc:=cc||substr(title,pos,1);            if lengthB(cc)>20 and round(lengthB(cc)*8/3)*3=lengthB(cc)*8 then              utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(cc)));              cc:='';            end if;          end LOOP;          if length(cc)>0 then            utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(cc)));          end if;           --utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(title)));          utl_smtp.write_data(c,'?='|| utl_tcp.CRLF);                    utl_smtp.write_data(c,'MIME-Version: 1.0'|| utl_tcp.CRLF);          utl_smtp.write_data(c,'Content-Type: multipart/mixed; boundary="'||boundarystr||'"'|| utl_tcp.CRLF);          utl_smtp.write_data(c,'Content-Transfer-Encoding: base64'|| utl_tcp.CRLF);          utl_smtp.write_data(c,''|| utl_tcp.CRLF);                    --本文          utl_smtp.write_data(c,boundarystrline|| utl_tcp.CRLF);--區切り文字          utl_smtp.write_data(c,'MIME-Version: 1.0'|| utl_tcp.CRLF);          utl_smtp.write_data(c,'Content-Type: text/plain; charset="SJIS"'|| utl_tcp.CRLF);          utl_smtp.write_data(c,'Content-Transfer-Encoding: base64'|| utl_tcp.CRLF);          utl_smtp.write_data(c,''|| utl_tcp.CRLF); --空行          IF mailbody IS NULL THEN             utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(' ') ));          ELSE             utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(mailbody) ));          END IF;          utl_smtp.write_data(c, utl_tcp.CRLF);          utl_smtp.write_data(c,boundarystrline|| utl_tcp.CRLF);--區切り文字          RETURN  TRUE;      EXCEPTION          WHEN OTHERS THEN              RETURN  FALSE;      END;  END;  FUNCTION addAttachmentFile(mimetype       in VARCHAR2,                            attachfilename in VARCHAR2,                            data           in blob) RETURN BOOLEAN IS  BEGIN      DECLARE          buffer RAW(32767);          amt BINARY_INTEGER := 300;          --pos INTEGER := 2147483647;          pos INTEGER := 1;          ix  NUMBER;      BEGIN           if ExistAttachment then              utl_smtp.write_data(c,boundarystrline|| utl_tcp.CRLF);--區切り文字            end if;                        --添付1            utl_smtp.write_data(c,'MIME-Version: 1.0'|| utl_tcp.CRLF);            utl_smtp.write_data(c,'Content-Transfer-Encoding: base64'|| utl_tcp.CRLF);            utl_smtp.write_data(c,'Content-Type: '||mimetype||';');            utl_smtp.write_data(c,'name="=?iso-2022-jp?B?');            utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(attachfilename)));            utl_smtp.write_data(c,'?="'|| utl_tcp.CRLF);                          --ファイル名            utl_smtp.write_data(c,'Content-Disposition: attachment;');            utl_smtp.write_data(c,'filename="=?iso-2022-jp?B?');            utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw(attachfilename)));            utl_smtp.write_data(c,'?="'|| utl_tcp.CRLF);                       --添付データ            utl_smtp.write_data(c,''|| utl_tcp.CRLF); --空行            if dbms_lob.GETLENGTH(data)>0 then                                while pos < dbms_lob.getlength(data) loop                    dbms_lob.read(data,amt,pos,buffer);                    utl_smtp.write_raw_data(c, utl_encode.base64_encode(buffer));                    pos  := pos + amt ;                    --amt := least(1000,dbms_lob.getlength(src_lob) - l_ammount);                end loop;            else              utl_smtp.write_raw_data(c, utl_encode.base64_encode( utl_raw.cast_to_raw('データがありません') ));            end if;            utl_smtp.write_data(c, utl_tcp.CRLF);          ExistAttachment:=true; --添付あり          RETURN  TRUE;                EXCEPTION          WHEN OTHERS THEN              RETURN  FALSE;      END;  END;END sendMail;/SHOW ERR