UTL_SMTP - bami74/me GitHub Wiki

-- SMTP 셋팅은 알아서...

-- UTL_MAIL 사용 셋팅
$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/utlsmtp.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb

GRANT EXECUTE ON utl_smtp TO USER_NAME;

-- PUBLIC 으로 하던 특정 사용자에게 권한을 주던 마음데로 하면 됨

ALTER SYSTEM SET smtp_out_server='localhost' scope=both;

-- 메일 발송 서버 설정
-- 메일 테이블 생성
CREATE TABLE G1_MAIL(
    TRAN_PR      NUMBER(11) NOT NULL,  -- INDEX KEY
    TRAN_ID      VARCHAR2(20 BYTE),     --사용자ID
    TRAN_MAIL    VARCHAR2(255 BYTE) NOT NULL, -- 수신자 MAIL
    TRAN_SENDER  VARCHAR2(255 BYTE),    -- 발신자 MAIL
    TRAN_STATUS  VARCHAR2(5 BYTE),    -- 발신 상태
    TRAN_DATE    DATE NOT NULL,     -- 발신 일자
    TRAN_RSLTDATE     DATE,       -- 결과값 리턴
    TRAN_MSG          CLOB,       -- 메세지 내역
    TRAN_SUBJECT      VARCHAR2(500 BYTE),
    PRIMARY KEY ( HTLNO, TRAN_PR )
);

-- 발송 트리거 생성
CREATE or REPLACE TRIGGER G1_MAIL_I
    BEFORE INSERT ON G1_MAIL REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
SEND_MAIL_SMTP(
:NEW.TRAN_MAIL,
:NEW.TRAN_SUBJECT,
:NEW.TRAN_MSG,
:NEW.TRAN_PR,
:NEW.TRAN_SENDER
);

/*
-- 발송 스테이터스
CREATE TABLE G1_MAIL_STATUS(
    TRAN_PR       NUMBER(11) NOT NULL,
    TRAN_STATUS   VARCHAR2(255 BYTE),
    TRAN_MAIL     VARCHAR2(255 BYTE),
    TRAN_MESG     VARCHAR2(255 BYTE) NOT NULL
)
*/

-- 발송 프로시저
create or replace procedure send_mail_smtp(recipient in varchar2,
                                           subject in varchar2,
                                           message in long,
                                           idx in varchar2,
                                           accountmail in varchar2
                                           )
 is
  mailhost varchar2(30) := 'localhost'; -- -- host mail address
  mail_conn utl_smtp.connection;
  crlf varchar2(2) := chr(13) || chr(10);
  mesg long;
  v_subject long;
  v_code varchar2(30);
  v_code_master varchar2(30);
  v_reply utl_smtp.reply;
  accountname varchar2(255);

begin

  v_subject := subject;
  mesg := 'MIME-version: 1.0' || crlf ||
          'Content-Type: text/html;charset=euc-kr' || crlf ||
          'Content-Transfer-Encoding: quoted-printable ' || crlf ||
          'Date: ' || to_char(sysdate, 'dd Mon yy hh24:mi:ss') || crlf ||
          'From:  <' || accountmail || '>' || crlf ||
         --          'Subject: =?utf-8?Q?' || replace(UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(V_SUBJECT))), '=' || chr(13) || chr(10), '') || '?=' ||CRLF||
         -- 한글 깨질경우 UTF-8 OR EUC-KR 적용
          'Subject: =?euc-kr?Q?' || replace(utl_raw.cast_to_varchar2(utl_encode.quoted_printable_encode(utl_raw.cast_to_raw(v_subject))),
                                            '=' || chr(13) || chr(10),
                                            '') || '?=' || crlf ||
          'To: ' || recipient || crlf ||
          '' || crlf ||
          utl_raw.cast_to_varchar2(utl_encode.quoted_printable_encode(utl_raw.cast_to_raw(message)));

  mail_conn := utl_smtp.open_connection(mailhost, 25);
  utl_smtp.helo(mail_conn, mailhost);
  utl_smtp.mail(mail_conn, accountmail);
  v_reply := utl_smtp.rcpt(mail_conn, recipient);

  insert into fo.g1_mail_status
    (htlno, tran_pr, tran_status, tran_mail, tran_mesg)
  values
    (vhtlno, idx, v_reply.code, subject, v_reply.text);

  v_code := v_reply.code;
  v_code_master := v_reply.code;

  if v_code <> '550' then
    --utl_smtp.rcpt(mail_conn, recipient);
    utl_smtp.data(mail_conn, mesg);
    utl_smtp.quit(mail_conn);
    v_code := 0;
  end if;

end;

-- 발송 쿼리
insert into
  (tran_pr,
   tran_id,
   tran_mail,
   tran_sender,
   tran_status,
   tran_date,
   tran_msg,
   tran_subject)
values
  (seq.nextval,
   '사용자별 아이디(발신자 유저 아이디)',
   '수신자 메일',
   '발신자 메일',
   '1', -- 초기상태 1
   sysdate,
   '메일 내용',
   '메일 제목');

-- 발송 이후 처리
select tran_status
  into v_update_status
  from g1_mail_status
 where tran_pr = v_tran_pr;
update g1_mail
   set tran_status = v_update_status, tran_rsltdate = sysdate,
 where tran_pr = v_tran_pr;

commit;

/* ---참고---
UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW('한글한글')))
'=?euc-kr?Q?' || replace(UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW('한글한글'))), '=' || chr(13) || chr(10), '') || '?='
'=?UTF-8?Q?' || replace(UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW('한글한글'))), '=' || chr(13) || chr(10), '') || '?='

-> 메일 발송 프로시저 상에서 한글이 포함될만한 부분들을 위와 같이 수정함
-> 3가지중 한가지는 될꺼임...
-> 뭘써야 하는지는 잘 모르겠츰.ㅠ
*/

[출처] [ORACLE] UTL_SMTP|작성자 키에르

My SMTP

create or replace procedure my_send_mail_report_pr(p_report_num in number,
                                                   p_area       in varchar2) as
  v_content varchar2(2000);
  v_roop    number;
  v_sql_code number;
  v_sql_msg  varchar2(2000);
  v_filename varchar2(100);
  e_log_error exception;
begin
  v_filename := 'WeeklyReport_' || to_char(sysdate, 'YYYYMMDD') || '.xls';
  v_content  := '주간 위클리 리포트입니다.' || chr(10) || '본 메일은 발신전용으로, 수신되지 않습니다.';
  for t in (select crypto_aes256.dec_aes(email_id) email
              from my_mail_desc_tb
             where report_num = p_report_num
               and area = p_area) loop
    mail_files('[email protected]',
               t.email,
               '주간 위클리 레포트',
               v_content,
               'DIR_MAIL',
               v_filename);
  end loop;
exception
  when others then
    v_sql_msg := 'Error';
end my_send_mail_report_pr;

create or replace procedure mail_files(p_from_name        varchar2,
                                       p_to_name          varchar2,
                                       p_subject          varchar2,
                                       p_message          varchar2,
                                       p_oracle_directory varchar2,
                                       p_binary_file      varchar2) is
  -- Example procedure to send a mail with an in line attachment
  -- encoded in Base64
  -- this procedure uses the following nested functions:
  --   binary_attachment - calls:
  --     begin_attachment - calls:
  --       write_boundary
  --       write_mime_header
  --
  --     end attachment - calls;
  --       write_boundary
  /*
  사내망: smtp.system.com (127.0.0.1) 25포트
  ===========================================
  제   목: MY 메일링 기능 추가를 위한 메일 서버 연동 요청  
  */
  -- change the following line to refer to your mail server
  v_smtp_server      varchar2(100) := '127.0.0.1';
  v_smtp_server_port number := 25;
  v_directory_name varchar2(100);
  v_file_name      varchar2(100);
  v_mesg           varchar2(32767);
  v_conn           utl_smtp.connection;
  --
  procedure write_mime_header(p_conn  in out nocopy utl_smtp.connection,
                              p_name  in varchar2,
                              p_value in varchar2) is
  begin
    utl_smtp.write_raw_data(p_conn,
                            utl_raw.cast_to_raw(p_name || ': ' || p_value ||
                                                utl_tcp.crlf));
  end write_mime_header;

  --
  procedure write_boundary(p_conn in out nocopy utl_smtp.connection,
                           p_last in boolean default false) is
  begin
    if (p_last) then
      utl_smtp.write_data(p_conn,
                          '--DMW.Boundary.605592468--' || utl_tcp.crlf);
    else
      utl_smtp.write_data(p_conn,
                          '--DMW.Boundary.605592468' || utl_tcp.crlf);
    end if;
  end write_boundary;

  --
  procedure end_attachment(p_conn in out nocopy utl_smtp.connection,
                           p_last in boolean default true) is
  begin
    utl_smtp.write_data(p_conn, utl_tcp.crlf);
    if (p_last) then
      write_boundary(p_conn, p_last);
    end if;
  end end_attachment;

  --
  procedure begin_attachment(p_conn         in out nocopy utl_smtp.connection,
                             p_mime_type    in varchar2 default 'text/plain',
                             p_inline       in boolean default false,
                             p_filename     in varchar2 default null,
                             p_transfer_enc in varchar2 default null) is
  begin
    write_boundary(p_conn);
    if (p_transfer_enc is not null) then
      write_mime_header(p_conn,
                        'Content-Transfer-Encoding',
                        p_transfer_enc);
    end if;
    write_mime_header(p_conn, 'Content-Type', p_mime_type);
    if (p_filename is not null) then
      if (p_inline) then
        write_mime_header(p_conn,
                          'Content-Disposition',
                          'inline; filename="' || p_filename || '"');
      else
        write_mime_header(p_conn,
                          'Content-Disposition',
                          'attachment; filename="' || p_filename || '"');
      end if;
    end if;
    utl_smtp.write_data(p_conn, utl_tcp.crlf);
  end begin_attachment;

  --
  procedure binary_attachment(p_conn      in out utl_smtp.connection,
                              p_file_name in varchar2,
                              p_mime_type in varchar2) is
    c_max_line_width constant pls_integer default 54;
    v_amt         binary_integer := 672 * 3; /* ensures proper format; 2016 */
    v_bfile       bfile;
    v_file_length pls_integer;
    v_buf         raw(2100);
    v_modulo      pls_integer;
    v_pieces      pls_integer;
    v_file_pos    pls_integer := 1;
  begin
    begin_attachment(p_conn         => p_conn,
                     p_mime_type    => p_mime_type,
                     p_inline       => true, --p_inline => TRUE,
                     p_filename     => p_file_name,
                     p_transfer_enc => 'base64');
    begin
      v_bfile := bfilename(p_oracle_directory, p_file_name);
      -- Get the size of the file to be attached
      v_file_length := dbms_lob.getlength(v_bfile);
      -- Calculate the number of pieces the file will be split up into
      v_pieces := trunc(v_file_length / v_amt);
      -- Calculate the remainder after dividing the file into v_amt chunks
      v_modulo := mod(v_file_length, v_amt);
      if (v_modulo <> 0) then
        -- Since the file does not devide equally
        -- we need to go round the loop an extra time to write the last
        -- few bytes - so add one to the loop counter.
        v_pieces := v_pieces + 1;
      end if;
      dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
      for i in 1 .. v_pieces loop
        -- we can read at the beginning of the loop as we have already calculated
        -- how many iterations we will take and so do not need to check
        -- end of file inside the loop.
        v_buf := null;
        dbms_lob.read(v_bfile, v_amt, v_file_pos, v_buf);
        v_file_pos := i * v_amt + 1;
        utl_smtp.write_raw_data(p_conn, utl_encode.base64_encode(v_buf));
      end loop;
    end;
    dbms_lob.fileclose(v_bfile);
    end_attachment(p_conn => p_conn);
  exception
    when no_data_found then
      end_attachment(p_conn => p_conn);
      dbms_lob.fileclose(v_bfile);
  end binary_attachment;

  --
  -- Main Routine
  --
begin
  --
  -- Connect and set up header information:
  --
  --   v_conn:= UTL_SMTP.OPEN_CONNECTION( v_smtp_server, v_smtp_server_port );
  v_conn := utl_smtp.open_connection(v_smtp_server, v_smtp_server_port);
  utl_smtp.ehlo(v_conn, v_smtp_server);
  --   utl_smtp.command( v_conn, 'AUTH LOGIN');
  --   utl_smtp.command( v_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_smtp_id))));
  --   utl_smtp.command( v_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_smtp_pass))));
  utl_smtp.mail(v_conn, p_from_name);
  utl_smtp.rcpt(v_conn, p_to_name);
  utl_smtp.open_data(v_conn);
  --   UTL_SMTP.WRITE_DATA(v_conn, 'Subject: '|| p_subject ||UTL_TCP.CRLF);
  utl_smtp.write_raw_data(v_conn,
                          utl_raw.cast_to_raw('From: ' || p_from_name ||
                                              utl_tcp.crlf));
  utl_smtp.write_raw_data(v_conn,
                          utl_raw.cast_to_raw('To: ' || p_to_name ||
                                              utl_tcp.crlf));
  utl_smtp.write_raw_data(v_conn,
                          utl_raw.cast_to_raw('Subject: ' || p_subject ||
                                              utl_tcp.crlf));
  --
  v_mesg := 'Content-Transfer-Encoding: 7bit' || utl_tcp.crlf ||
            'Mime-Version: 1.0' || utl_tcp.crlf ||
            'Content-Type: multipart/mixed;boundary="DMW.Boundary.605592468"' ||
            utl_tcp.crlf || utl_tcp.crlf || '--DMW.Boundary.605592468' ||
            utl_tcp.crlf || 'Content-Transfer-Encoding: binary' ||
            utl_tcp.crlf || 'Content-Type: text/html' || utl_tcp.crlf ||
            utl_tcp.crlf || p_message || utl_tcp.crlf ||
            '--DMW.Boundary.605592468' || utl_tcp.crlf;
  --
  utl_smtp.write_raw_data(v_conn, utl_raw.cast_to_raw(v_mesg));
  --
  -- Add the Attachment
  --
  if p_binary_file is not null then
    binary_attachment(p_conn      => v_conn,
                      p_file_name => p_binary_file,
                      -- Modify the mime type at the beginning of this line depending
                      -- on the type of file being loaded.
                      --     p_mime_type => 'text/plain; name="'||p_binary_file||'"'
                      p_mime_type => 'application/vnd.ms-excel; name="' ||
                                     p_binary_file || '"');
  end if;
  --
  -- Send the email
  --
  utl_smtp.close_data(v_conn);
  utl_smtp.quit(v_conn);
end;

create or replace procedure mail_files_auth(p_from_name        varchar2,
                                            p_to_name          varchar2,
                                            p_subject          varchar2,
                                            p_message          varchar2,
                                            p_oracle_directory varchar2,
                                            p_binary_file      varchar2,
                                            p_smtp_server      varchar2,
                                            p_smtp_server_port varchar2,
                                            p_smtp_id          varchar2,
                                            p_smtp_pass        varchar2) is
  -- Example procedure to send a mail with an in line attachment
  -- encoded in Base64
  -- this procedure uses the following nested functions:
  --   binary_attachment - calls:
  --     begin_attachment - calls:
  --       write_boundary
  --       write_mime_header
  --
  --     end attachment - calls;
  --       write_boundary
  -- change the following line to refer to your mail server
  --   v_smtp_server VARCHAR2(100) := '150.19.7.30';
  --   v_smtp_server_port NUMBER := 25;
  --   v_smtp_server VARCHAR2(100) := '125.209.209.240';
  --   v_smtp_server_port NUMBER := 587;
  v_directory_name varchar2(100);
  v_file_name      varchar2(100);
  v_mesg           varchar2(32767);
  v_conn           utl_smtp.connection;
  --
  procedure write_mime_header(p_conn  in out nocopy utl_smtp.connection,
                              p_name  in varchar2,
                              p_value in varchar2) is
  begin
    utl_smtp.write_raw_data(p_conn,
                            utl_raw.cast_to_raw(p_name || ': ' || p_value ||
                                                utl_tcp.crlf));
  end write_mime_header;

  --
  procedure write_boundary(p_conn in out nocopy utl_smtp.connection,
                           p_last in boolean default false) is
  begin
    if (p_last) then
      utl_smtp.write_data(p_conn,
                          '--DMW.Boundary.605592468--' || utl_tcp.crlf);
    else
      utl_smtp.write_data(p_conn,
                          '--DMW.Boundary.605592468' || utl_tcp.crlf);
    end if;
  end write_boundary;

  --
  procedure end_attachment(p_conn in out nocopy utl_smtp.connection,
                           p_last in boolean default true) is
  begin
    utl_smtp.write_data(p_conn, utl_tcp.crlf);
    if (p_last) then
      write_boundary(p_conn, p_last);
    end if;
  end end_attachment;

  --
  procedure begin_attachment(p_conn         in out nocopy utl_smtp.connection,
                             p_mime_type    in varchar2 default 'text/plain',
                             p_inline       in boolean default false,
                             p_filename     in varchar2 default null,
                             p_transfer_enc in varchar2 default null) is
  begin
    write_boundary(p_conn);
    if (p_transfer_enc is not null) then
      write_mime_header(p_conn,
                        'Content-Transfer-Encoding',
                        p_transfer_enc);
    end if;
    write_mime_header(p_conn, 'Content-Type', p_mime_type);
    if (p_filename is not null) then
      if (p_inline) then
        write_mime_header(p_conn,
                          'Content-Disposition',
                          'inline; filename="' || p_filename || '"');
      else
        write_mime_header(p_conn,
                          'Content-Disposition',
                          'attachment; filename="' || p_filename || '"');
      end if;
    end if;
    utl_smtp.write_data(p_conn, utl_tcp.crlf);
  end begin_attachment;

  --
  procedure binary_attachment(p_conn      in out utl_smtp.connection,
                              p_file_name in varchar2,
                              p_mime_type in varchar2) is
    c_max_line_width constant pls_integer default 54;
    v_amt         binary_integer := 672 * 3; /* ensures proper format; 2016 */
    v_bfile       bfile;
    v_file_length pls_integer;
    v_buf         raw(2100);
    v_modulo      pls_integer;
    v_pieces      pls_integer;
    v_file_pos    pls_integer := 1;
  begin
    begin_attachment(p_conn         => p_conn,
                     p_mime_type    => p_mime_type,
                     p_inline       => true, --p_inline => TRUE,
                     p_filename     => p_file_name,
                     p_transfer_enc => 'base64');
    begin
      v_bfile := bfilename(p_oracle_directory, p_file_name);
      -- Get the size of the file to be attached
      v_file_length := dbms_lob.getlength(v_bfile);
      -- Calculate the number of pieces the file will be split up into
      v_pieces := trunc(v_file_length / v_amt);
      -- Calculate the remainder after dividing the file into v_amt chunks
      v_modulo := mod(v_file_length, v_amt);
      if (v_modulo <> 0) then
        -- Since the file does not devide equally
        -- we need to go round the loop an extra time to write the last
        -- few bytes - so add one to the loop counter.
        v_pieces := v_pieces + 1;
      end if;
      dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
      for i in 1 .. v_pieces loop
        -- we can read at the beginning of the loop as we have already calculated
        -- how many iterations we will take and so do not need to check
        -- end of file inside the loop.
        v_buf := null;
        dbms_lob.read(v_bfile, v_amt, v_file_pos, v_buf);
        v_file_pos := i * v_amt + 1;
        utl_smtp.write_raw_data(p_conn, utl_encode.base64_encode(v_buf));
      end loop;
    end;
    dbms_lob.fileclose(v_bfile);
    end_attachment(p_conn => p_conn);
  exception
    when no_data_found then
      end_attachment(p_conn => p_conn);
      dbms_lob.fileclose(v_bfile);
  end binary_attachment;

  --
  -- Main Routine
  --
begin
  --
  -- Connect and set up header information:
  --
  --   v_conn:= UTL_SMTP.OPEN_CONNECTION( v_smtp_server, v_smtp_server_port );
  v_conn := utl_smtp.open_connection(p_smtp_server, p_smtp_server_port);
  if p_smtp_id is null then
    utl_smtp.helo(v_conn, p_smtp_server);
  else
    utl_smtp.ehlo(v_conn, p_smtp_server);
    utl_smtp.command(v_conn, 'AUTH LOGIN');
    utl_smtp.command(v_conn,
                     utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_smtp_id))));
    utl_smtp.command(v_conn,
                     utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_smtp_pass))));
  end if;
  utl_smtp.mail(v_conn, p_from_name);
  utl_smtp.rcpt(v_conn, p_to_name);
  utl_smtp.open_data(v_conn);
  --   UTL_SMTP.WRITE_DATA(v_conn, 'Subject: '|| p_subject ||UTL_TCP.CRLF);
  utl_smtp.write_raw_data(v_conn,
                          utl_raw.cast_to_raw('From: ' || p_from_name ||
                                              utl_tcp.crlf));
  utl_smtp.write_raw_data(v_conn,
                          utl_raw.cast_to_raw('To: ' || p_to_name ||
                                              utl_tcp.crlf));
  utl_smtp.write_raw_data(v_conn,
                          utl_raw.cast_to_raw('Subject: ' || p_subject ||
                                              utl_tcp.crlf));
  --
  v_mesg := 'Content-Transfer-Encoding: 7bit' || utl_tcp.crlf ||
            'Mime-Version: 1.0' || utl_tcp.crlf ||
            'Content-Type: multipart/mixed;boundary="DMW.Boundary.605592468"' ||
            utl_tcp.crlf || utl_tcp.crlf || '--DMW.Boundary.605592468' ||
            utl_tcp.crlf || 'Content-Transfer-Encoding: binary' ||
            utl_tcp.crlf || 'Content-Type: text/html' || utl_tcp.crlf ||
            utl_tcp.crlf || p_message || utl_tcp.crlf ||
            '--DMW.Boundary.605592468' || utl_tcp.crlf;
  --
  utl_smtp.write_raw_data(v_conn, utl_raw.cast_to_raw(v_mesg));
  --
  -- Add the Attachment
  --
  if p_binary_file is not null then
    binary_attachment(p_conn      => v_conn,
                      p_file_name => p_binary_file,
                      -- Modify the mime type at the beginning of this line depending
                      -- on the type of file being loaded.
                      --     p_mime_type => 'text/plain; name="'||p_binary_file||'"'
                      p_mime_type => 'application/vnd.ms-excel; name="' ||
                                     p_binary_file || '"');
  end if;
  --
  -- Send the email
  --
  utl_smtp.close_data(v_conn);
  utl_smtp.quit(v_conn);
end;