Thủ tục gửi email cảnh báo CLOB trong Oracle Database

1. Tạo thủ tục 
CREATE OR REPLACE PROCEDURE SYS.send_email_html_clob (str_to IN VARCHAR2,  str_subject   IN VARCHAR2,   str_body      IN CLOB)
IS
    l_mail_conn   UTL_SMTP.connection;
    p_str_to      VARCHAR2(4000) := trim(str_to);
    pos number(10,0) := 0;
    i number(10,0) := 0;
    L_OFFSET number := 1;
    L_AMMOUNT number := 1900;

BEGIN
    --- Update by BinhTV on 12/01/2018
    l_mail_conn := UTL_SMTP.open_connection ('10.20.30.40', 25);
    UTL_SMTP.helo (l_mail_conn, '10.20.30.40');
    UTL_SMTP.command (l_mail_conn, 'AUTH LOGIN');
    UTL_SMTP.command (
        l_mail_conn,
        UTL_RAW.cast_to_varchar2 (
            UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('Alerter'))));
    UTL_SMTP.command (
        l_mail_conn,
        UTL_RAW.cast_to_varchar2 (
            UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('12345678'))));
    UTL_SMTP.mail (l_mail_conn, 'Alerter@tranvanbinh.vn');

      pos   := INSTR (p_str_to, ';', 1, 1);
       -- while there are chunks left, loop
      WHILE (pos != 0)
      LOOP
      dbms_output.put_line('xx' || SUBSTR (p_str_to, 1, pos));
         UTL_SMTP.rcpt (l_mail_conn, SUBSTR (p_str_to, 1, pos-1));

         p_str_to        := SUBSTR (p_str_to, pos + 1, LENGTH (p_str_to));

         pos           := INSTR (p_str_to, ';', 1, 1);

         IF pos = 0
         THEN
            UTL_SMTP.rcpt (l_mail_conn, p_str_to);
         END IF;
      END LOOP;


    UTL_SMTP.open_data (l_mail_conn);
    UTL_SMTP.write_data (
        l_mail_conn,
        'Subject: =?UTF-8?Q?'
        || UTL_RAW.cast_to_varchar2(UTL_ENCODE.quoted_printable_encode (
                                        UTL_RAW.cast_to_raw (str_subject)))
        || '?='
        || UTL_TCP.crlf);
   UTL_SMTP.write_data (l_mail_conn, 'MIME-version: 1.0' || UTL_TCP.crlf);
    UTL_SMTP.write_data (
        l_mail_conn,
        'Content-Type: text/html;charset=utf-8' || UTL_TCP.crlf);
    UTL_SMTP.write_data (
        l_mail_conn,
        'Content-Transfer-Encoding: quoted-printable ' || UTL_TCP.crlf);

    UTL_SMTP.write_data (
        l_mail_conn,
           'Date: '
        || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
        || UTL_TCP.crlf);
    UTL_SMTP.write_data (l_mail_conn, 'To: ' || str_to || UTL_TCP.crlf);
    UTL_SMTP.write_data (
        l_mail_conn,
        'From: ' || 'Alerter@tranvanbinh.vn' || UTL_TCP.crlf);

    UTL_SMTP.write_data (
        l_mail_conn,
           'Reply-To: '
        || 'Alerter@tranvanbinh.vn'
        || UTL_TCP.crlf
        || UTL_TCP.crlf);

    WHILE L_OFFSET < DBMS_LOB.GETLENGTH(str_body) LOOP
        UTL_SMTP.WRITE_DATA(l_mail_conn,
        DBMS_LOB.SUBSTR(str_body,L_AMMOUNT,L_OFFSET));
        L_OFFSET := L_OFFSET + L_AMMOUNT ;
        L_AMMOUNT := LEAST(1900,DBMS_LOB.GETLENGTH(str_body) - L_AMMOUNT);
    END LOOP;

    UTL_SMTP.close_data (l_mail_conn);

    UTL_SMTP.quit (l_mail_conn);
END send_email_html_clob;
/

2. Test

begin

        SYS.send_email_html_clob ('tranbinh48ca@gmail.com,binhtv10@gmail.com',  'Test Subject',   'Test Body')

end;

@ Trần Văn Bình - Founder of Oracle DBA AZ
#HocOracleHaNoi
#học oracle #oracle database #khóa học oracle online #khóa học oca #học oca ở đâu #oca là gì #oca oracle #BossData #OraAz #OracleDBAAz #OracleTutorial #Quản_trị_cơ_sở_dữ_liệu_Oracle #OracleDBA #OracleDatabaseAdministration