utl_smtp 를 이용한 첨부파일 메일 전송

$ sqlplus "/ as sysdba"

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   -- change the following line to refer to your mail server   v_smtp_server VARCHAR2(100) := '메일서버 도메인';   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_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 );   UTL_SMTP.HELO( v_conn, v_smtp_server );   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); --   v_mesg:= 'Content-Transfer-Encoding: 7bit' || UTL_TCP.CRLF ||     'Content-Type: multipart/mixed;boundary="DMW.Boundary.605592468"' || UTL_TCP.CRLF ||     'Mime-Version: 1.0' || UTL_TCP.CRLF ||     '--DMW.Boundary.605592468' || UTL_TCP.CRLF ||     'Content-Transfer-Encoding: binary'||UTL_TCP.CRLF||     'Content-Type: text/plain' ||UTL_TCP.CRLF ||     UTL_TCP.CRLF || p_message || UTL_TCP.CRLF ; --   UTL_SMTP.WRITE_RAW_DATA ( v_conn, UTL_RAW.CAST_TO_RAW(v_mesg) );   --   -- Add the Attachment   --   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||'"'   );   --   -- Send the email   --   UTL_SMTP.CLOSE_DATA( v_conn );   UTL_SMTP.QUIT( v_conn ); END;

첨부파일이 위치한 Directory 생성

CREATE OR REPLACE DIRECTORY ATTACH AS '/disk/data/attach'; grant read, write on directory ATTACH to SCOTT;

Mail 발송

SQL> conn scott/tiger SQL> exec mail_files('유저명@메일주소', '받을사람@메일주소', '"제목 : 한글"', '"내용 : 한글"', 'ATTACH', '첨부파일.zip') SQL> commit;

에러 상황 1

ORA-24247: 네트워크 액세스가 ACL(액세스 제어 목록)에 의해 거부되었습니다. ORA-06512: "SYS.UTL_TCP",  17행 ORA-06512: "SYS.UTL_TCP",  267행 ORA-06512: "SYS.UTL_SMTP",  161행 ORA-06512: "SYS.UTL_SMTP",  197행 ORA-06512: "SCOTT.MAIL_FILES",  156행 ORA-06512:  1행

조치사항 1

$ sqlplus "/ as sysdba"

set serveroutput on create or replace procedure mailserver_acl(   aacl       varchar2,   acomment   varchar2,   aprincipal varchar2,   aisgrant   boolean,   aprivilege varchar2,   aserver    varchar2,   aport      number) is begin    begin     DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);      dbms_output.put_line('ACL dropped.....');   exception     when others then       dbms_output.put_line('Error dropping ACL: '||aacl);       dbms_output.put_line(sqlerrm);   end;   begin     DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);     dbms_output.put_line('ACL created.....');   exception     when others then       dbms_output.put_line('Error creating ACL: '||aacl);       dbms_output.put_line(sqlerrm);   end;    begin     DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);     dbms_output.put_line('ACL assigned.....');           exception     when others then       dbms_output.put_line('Error assigning ACL: '||aacl);       dbms_output.put_line(sqlerrm);   end;      commit;   dbms_output.put_line('ACL commited.....'); end; / begin   mailserver_acl(     'mailserver_acl.xml',     'ACL for used Email Server to connect',     'SCOTT',     TRUE,     'connect',     '메일서버 도메인',     25);    end; / begin    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('mailserver_acl.xml','SCOTT',TRUE,'connect');    commit; end; /

에러 상황 2

ORA-22288: 파일 또는 LOB GETLENGTH 작업이 실패되었습니다 No such file or directory ORA-06512: "SYS.DBMS_LOB",  850행 ORA-06512: "SCOTT.MAIL_FILES",  119행 ORA-06512: "SCOTT.MAIL_FILES",  175행 ORA-06512:  1행

ORA-22285: GETLENGTH 작업에 존재하지 않은 디렉토리 또는 파일입니다. ORA-06512: "SYS.DBMS_LOB",  850행 ORA-06512: "SCOTT.MAIL_FILES",  119행 ORA-06512: "SCOTT.MAIL_FILES",  175행 ORA-06512:  1행

조치 사항 2

디렉토리 권한 문제 SQL> grant read, write on directory 디렉토리명 to DB유저명;