Using Oracle, the mail sending function is the same as that of PL/SQL.

At the beginning of the function, I was asked to write an oracle stored procedure and send mail. I have done this before, and directly copy the previous code to achieve it easily.

To make a record, I copied the code. The code is easy to understand and the comments are sufficient.

Create or replace package sendmailisprocedure funcsendmail (SUID in varchar2); End Sendmail;/create or replace package body sendmailis C utl_smtp.connection; boundarystrend varchar2 (100): = '-- accept --'; smtpserver varchar2 (100): = 'lcmail01 comment'; boundarystr varchar2 (100): = 'boundary _ str'; boundarystrline varchar2 (100): = '-- boundary_str'; existattachment Boolean; procedure funcsendmail (Su Id varchar2) isbegindeclare type typmaildata is record (toadd Dewey. cyscc0370.d0120 % Type -- Wan Xianzhi zookeeper, toname Dewey. cyscc0370.d0120 % Type -- Wan xianname, ccadd Dewey. cyscc0370.d0120 % Type -- CC has been deployed successfully, ccname Dewey. cyscc0370.d0120 % Type -- CC name, fromadd Dewey. cyscc0370.d0120 % Type -- the sent cell contains too many bytes, fromname Dewey. cyscc0370.d0120 % Type -- Name of the sent cell, title Dewey. cyscc0370.d0041 % Type -- commandid was too large, pcontext Dewey. cyscc037 0. d0040 % Type -- Skip skip this article); maildata typmaildata; binarydata blob; rowcysct0291 Dewey. cysct0291 % rowtype; cursor getmail is select B. d0041 B _d0041 -- contains invalid values, B. d0120 B _d0120 -- send a mail first when there are too many attempts, B. d0100 B _d0100 -- ID of the Business Office for sending mail, B. d0010 B _d0010 -- send the trusted Certificate,. d0030 a_d0030 -- PDF character watermark, B. d0040 B _d0040 -- mail content, B. d0020 B _d0020 -- mail date from Dewey. cysct0291 A, Dewey. cyscc0370 B where. d0020 = B. d0010 and B. d0044 in (' 0 ', '4'); -- 0: not sent; 1: sent; 2: sent; 3: sent; 3: sent; 2: sent; 2: sent; 2: sent; 2: sent ); 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; -- returns the basic part of delayed messages as if openmail (maildata. toadd, -- Wan xianxianzhi has too many maildata. toname, -- Wan first name maildata. ccadd, -- CC cannot publish encrypted maildata. ccname, -- CC name maildata. fromadd, -- send the cell into delayed maildata. fromname, -- send the cell name maildata. title, -- too many attempts have been made to parse maildata. pcontext -- plain text) = false then flag: = '4'; els E flag: = '2'; -- add a token when creating a variable if addattachmentfile ('application/pdf ', nloop. B _d0100 | to_char (nloop. B _d0020, 'yyyymmdd') | 'hangzhou', nloop. a_d0030) = false then flag: = '4'; else flag: = '2'; utl_smtp.write_data (C, boundarystrend | utl_tcp.crlf); -- partition limit text limit (C ); utl_smtp.quit (c); 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 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: = small (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 (C, utl_encode.base64_encode (utl_raw.cast_to_raw (''); else then (C, then (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? '); -- Too many bytes have been written into memory when there are 3 multiples of rows have been encoded into base64_encode when there are too many rows without 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_s MTP. 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: 000000' | 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); convert (C, ''| utl_tcp.crlf); -- utl_smtp.write_data (C, boundarystrline | utl_tcp.crlf); -- partition character text 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); -- empty line if mailbody is null then encode (C, utl_encode.base64_encode (utl_raw.cast_to_raw (''); else utl_smtp.write_raw_data (C, utl_encode.base64_encode (utl_raw.c Ast_to_raw (mailbody); end if; then (C, utl_tcp.crlf); utl_smtp.write_data (C, boundarystrline | utl_tcp.crlf); -- returns true for the partition literal; exception when others then return false; 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 I Nteger: = 1; IX Number; begin if existattachment then encode (C, boundarystrline | utl_tcp.crlf); -- partition vertex text end if; -- add 1 utl_smtp.write_data (C, 'mime-version: 100' | 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); -- specify the names 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); -- add ータ utl_smtp.write_data (C,'' | utl_tcp.crlf); -- empty line 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; -- add writable Response Return true; Exception when others then return false; end Sendmail;/show err