본문 바로가기
Oracle/admin

DBMS_SMTP

소마후니 2017. 2. 26. 11:28

01 | SMTP 메일 전송

SMTP의 개념

SMTP에 관해 설명하기에 앞서 필자 역시 DB 프로그래밍만 주로 해왔기 때문에 인터넷이나 메일과 관련된 내용을 잘 아는 편이 아님을 밝혀 둔다. 따라서 SMTP란 무엇이고 몇 년도에 미국 어디에선가 아무개가 만들었고, 처음에는 이런저런 기능뿐이었지만 시간이 흘러감에 따라 새 기능이 추가되었다는 식의 설명보다는 필자가 학습자 입장에서 질문하고 이에 답하는 형식으로 내용을 서술하겠다. 이런 식의 접근방법이 더 쉽게 내용을 전달할 수도 있고, 학습자 입장에서 필자가 궁금해했었던 내용을 독자들도 궁금해 할 것이라는 생각에서 꺼낸 아이디어다. 그럼 SMTP가 무엇인지 파헤쳐 보자.


Q SMTP가 뭐죠?

A ‘Simple Mail Transfer Protocol’의 약자로 우리말로 간편메일전송규약이라고 하죠. HTTP가 인터넷 상에서 하이퍼텍스트 문서를 주고받는 규약이듯, SMTP는 인터넷 상에서 메일을 주고받기 위한 규약입니다.

Q 무슨 뜻인지는 대충 알 것 같기도 한데 …잘 모르겠네요ㅠㅠ.

A 음, 가령 편지봉투의 좌측 상단에는 보내는 사람, 우측 하단에는 받는 사람 주소를 쓰는데 이것은 편지를 주고 받는 사람 사이의 약속, 사회적인 규약이라고 할 수 있죠. 마찬가지로 이메일을 주고 받을 때 서로 다른 형식을 사용하면 문제가 발생하니 일종의 규약을 만든 것인데, 편지를 보낼 때 보내는 사람, 받는 사람 주소를 쓰고 편지 내용을 작성하는 규약이 바로 SMTP라고 할 수 있어요.

Q 그렇다면 그 규약이란 것에는 어떤 것들이 있나요?

A 이메일을 보낼 때 필요한 것이 바로 규약의 세부사항에 해당됩니다. 예를 들어, 보내는 사람, 받는 사람, 제목, 본문, 첨부파일 등이 필요한데 이러한 내용을 명시해 놓은 것이죠.

Q 이메일을 전송하는 것이니 통신과 관련된 내용도 규약에 포함되겠네요?!

A 네, HTTP가 기본 포트를 80번으로 사용하듯 SMTP는 25번을 기본 포트로 사용하며 모든 내용이 7비트 ASCII 문자로 구성됩니다.

Q POP3라고 들어봤는데 이것도 SMTP의 일종인가요?

A 아닙니다. SMTP는 이메일을 보내는 규약이고, POP3는 Post Office Protocol의 약자로 3은 버전을 의미합니다. 즉 최초에는 POP으로 시작해 현재는 POP3까지 나왔으며, 이메일을 받는 규약이에요. 참고로 받는 규약으로는POP3와 더불어 IMAP도 사용됩니다.

Q SMTP에 대해 감이 잡히는가 싶더니 이메일을 받는 규약이라니… 더 헷갈리네요.

A 이메일이 오고가는 전 과정을 설명하는 것이 낫겠네요. 예컨대, hong@daam.net이란 메일 주소를 가진 홍길동이라는 사람이 kim@gmeil.com이란 메일 주소를 가진 김유신에게 메일을 보내는데, 두 사람 모두 자신의 PC에 설치된 Outlook을 사용해 메일을 주고 받는다고 해 봅시다. 홍길동은 받는 사람의 주소에 김유신의 주소를 넣고 메일을 작성해서< 전송> 버튼을 누르면, 잠시 후 김유신 역시 Outlook을 열고 홍길동에게서 온 메일을 읽겠죠. 별도의 설명이 없어도 될 만큼 간단한 과정이지만 실제 무대 뒤에서 벌어지는 일은 조금 더 복잡합니다.
<전송> 버튼을 클릭하면 SMTP를 이용해 daam.net이란 회사의 SMTP 서버, 즉 보내는 메일 서버로 전송됩니다. 그리고 나서 Daam.net의 SMTP 서버는 김유신의 메일 계정이 있는 gmeil.com의 메일 서버로 메일을 보냅니다. 이때도 역시 SMTP를 사용하죠. 김유신이 Outlook을 열고 로그인 함과 동시에 gmeil.com의 받는 메일 서버에서 김유신의 PC로 메일이 다운로드됩니다. 이때 POP3나 IMAP을 사용합니다.

Q 대충 이해가 갑니다. 그런데 이 장에서는 SMTP에 대해서만 설명하는 건가요? POP3는 알 필요가 없나요?

A 이번 장에서는 UTL_SMTP와 UTL_MAIL 시스템 패키지를 이용해 메일을 보내는 것입니다. 따라서 SMTP에 대해서만 알고 있으면 돼요.

Q SMTP를 이용해 메일을 보낸다는 것은 알겠는데, 도대체 시스템 패키지를 어떤 식으로 사용한다는 건가요?

A 그 방법에 대해서는 다음 절에서 배울 거에요.


SMTP 명령어를 이용한 메일 전송

SMTP와 이메일 전송과 관련된 내용을 간단히 살펴봤고 이제 SMTP를 이용해 실제로 메일을 전송해보자. 하지만 메일을 보내기 위해선 반드시 필요한 것이 있다.

사전 준비 사항

메일을 보내려면 보내는 메일 서버, 즉 SMTP 서버에 접속해 SMTP 명령어를 사용해야 한다. 따라서 실습을 위해서는 SMTP 서버가 준비되어 있어야 한다. 필자의 경우 회사에서는 메일 서버를 이용할 수 있어 메일을 보내는 데 문제가 없었지만, 집에서는 접속할 수 없어 수많은 시행착오와 갖은 우여곡절 끝에 PC에 무료 SMTP 서버 프로그램을 설치했다(참고로 이 장의 예제에서는 필자 PC에 설치한 SMTP에서 만든 하나의 메일 계정으로 메일 전송과 수신에 사용하고 있다). 따라서 독자 여러분도 실습을 위해서는 이용 가능한 SMTP 서버가 반드시 준비되어 있어야 한다.


SMTP 명령어

SMTP 서버가 준비되었다고 가정하고 이제 SMTP 명령어에 대해 알아 보자. 이 장에서는 오라클에서 제공하는 시스템 패키지를 사용해 메일을 보내는 것이니 UTL_SMTP와 UTL_MAIL 패키지 사용법만 알면 될 것 같은데 굳이 SMTP 명령어까지 자세히 설명하는 이유는 뭘까? UTL_SMTP 패키지에 내장되어 있는 여러 함수와 프로시저가 SMTP 명령어를 그대로 구현한 것이기 때문이다. 따라서 SMTP 명령어에 대해 이해하고 있으면 UTL_SMTP 패키지도 쉽게 사용할 수 있을 것이다. 기본적인 SMTP 명령어는 다음과 같다.

▼ 표 18-1 기본 SMTP 명령어
명령어사용 방법설명
HELOHELO 도메인명SMTP 서버와의 대화를 위한 초기화 기능
MAIL (FROM)MAIL FROM: <보내는 주소>새로운 메일 트랜잭션이 시작되면서 보내는 메일 주소를 확인시킨다. 편지 봉투에 보내는 사람 주소를 쓰는 것과 같다.
RCPT(TO)RCPT TO: <받는 주소>받는 메일 서버에 수신자의 메일 주소를 알리는 명령어로 편지 봉투에 받는 사람 주소를 쓰는 것과 같다. 받는 메일 주소가 정확하지 않으면 오류가 발생한다.
DATADATA클라이언트에서 서버로 메일 내용 전송 시 사용된다. 이 명령어가 성공적으로 이루어지면 서버로부터 354 응답코드가 되돌아온다.
메일 내용은 <CR><LF>로 행으로 구분되고 맨 마지막 행에 ‘.’을 전송하면 서버로의 메시지 전송이 완료된다. DATA에서 ‘.’ 사이가 메일 내용이 되는 것이다.
RSETRSET서버 내부 상태를 리셋하고 메일 트랜잭션을 중단시킨다. 트랜잭션 초기화 시 사용한다.
NOOPNOOP특정 역할을 하지 않고 이 명령어를 전송하면 서버로부터 250 OK 응답코드가 돌아온다. 서버와의 연결이 끊어지지 않았는지 확인할 때 주로 사용된다.
QUITQUIT서버로 세션 종료를 요청하는 명령어다.

기본 명령어 외에도 인증과 보안에 관련된 EHLO, AUTH, STARTTLS와 같은 추가로 확장된 명령어가 있다.


메일 전송 실습

그럼 기본 SMTP 명령어를 사용해 간단한 메일을 보내 보자. 여기서는 텔넷을 사용해 SMTP 메일 서버에 접속해 메일을 보낼 텐데, 텔넷을 사용해 SMTP 서버와 접속이 가능해야 한다. 필자의 PC에 설치된 SMTP 서버 정보는 다음과 같다.

• SMTP 서버명(주소): localhost

• SMTP 서버 도메인명: hong.com

• 기본 포트: 25

• 이메일계정: charieh@hong.com (보내는 메일과 받는 메일 주소로 사용할 것이다)


telnet 명령어로 SMTP 서버에 접속하려면 명령어창에서 “telnet smtp서버명 포트번호”를 입력한다.

▼ 그림 18-1 텔넷을 이용한 SMTP 서버 연결

성공적으로 연결되었다. 메일을 보내기 위해 실행할 명령어는 [표 18-1]에 나와 있듯이 ‘HELO → MAIL FROM → RCPT TO → DATA → 메일내용 작성 → ‘.’ → QUIT’ 순이며, 각 명령어를 실행하면 서버로부터 응답코드와 메시지가 수신된다. SMTP 명령어를 사용해 간단한 메일을 보내는 방식은 다음과 같다.

    S: 220 Welcome Hong Mail
    C: HELO hong.com                  -- "Helo 도메인명"으로 연결 초기화
    S: 250 Hello.                     -- 서버 응답
    C: MAIL FROM: <charieh@hong.com>  -- MAIL 명령어
    S: 250 OK                         -- 서버 응답
    C: RCPT TO: <charieh@hong.com>    -- RCPT 명령어
    S: 250 OK                         -- 서버 응답
    C: DATA                           -- DATA 명령어로 메일 내용 작성 시작을 알림
    S: 354 OK, send.                  -- 서버 응답코드는 354
    C: From: sender<charieh@hong.com> -- 보내는 사람 정보를 작성
    C: To: receiver<charieh@hong.com> -- 받는 사람 정보를 작성
    C: Subject: Mail Sender Test      -- 메일 제목
    C:                                -- 메일 본문 시작
    C: Hello.
    C: This is the mail test.
    C: Thank you very much.
    C: Best regards.
    C: .                             -- 본문 내용 입력이 끝나면 "." 입력
    S: 250 Queued <66.722 seconds>   -- 서버 응답
    C: quit                          -- 메일 세션 종료
    S: 221 goodbye                   -- 서버 응답
    (S는 서버 메시지, C는 클라이언트에서 실행한 명령임)


약간 길긴 하지만 그리 어렵지 않은 내용이다. 실제로 위 내용대로 메일을 보낸 화면은 다음과 같다.

▼ 그림 18-2 SMTP** 명렁어를 이용한 메일 전송

제대로 메일이 전송됐는지 확인해 보자. 위 예제는 보낸 사람과 받는 사람 주소가 동일하고 같은 SMTP 서버를 사용했다. Outlook을 통해 전송된 메일을 열어보면 다음과 같다.

▼ 그림 18-3 O**utlook을 통해 본 전송된 메일

성공적으로 전송되었고 텔넷으로 SMTP 명령어를 직접 사용해 메일을 보낼 수 있음을 확인했다.


02 | UTL_SMTP를 이용한 메일 전송

이전 절에서 SMTP의 개념과 SMTP 명령어에 대해 배웠고 이를 사용해 실제로 메일을 전송하는 것까지 해봤으니 이제 본격적으로 UTL_SMTP 패키지를 이용해 메일을 전송해 볼 때가 됐다. UTL_SMTP 패키지의 서브 프로그램, 즉 이 패키지의 함수나 프로시저들은 SMTP 명령어를 그대로 옮겨 놓은 것이 많아 이해하는데 그리 어렵지 않을 것이다. 하지만 그 전에 먼저 UTL_SMTP와 UTL_MAIL 패키지를 사용하려면 준비해야 할 사항이 있는데 이에 대해 살펴 보자.


메일 전송을 위한 사전준비 사항

오라클 11g2 버전 이전에서는 UTL_SMTP, UTL_MAIL 패키지를 사용하는데 있어 아무런 문제가 없었다. 하지만 11g2 버전부터는 보안이 강화돼 UTL_SMTP, UTL_MAIL, UTL_TCP, UTL_HTTP 등 네트워크 통신과 관련된 시스템 패키지를 사용하려면 별도로 ACL(Access Control List)이란 것을 만들어 놔야 한다. ACL을 만드는 이유는 악의적인 해커가 오라클 DB에 침투해 이런 시스템 패키지를 이용해 외부로 이메일 전송, 외부 사이트 접속 및 공격을 할 수 있으므로 사전에 ACL에 등록된 사용자만 이런 시스템 패키지를 이용할 수 있게 하기 위해서다. 만약 ACL에 등록되지 않은 사용자가 UTL_SMPT 등의 패키지를 이용해 메일 전송을 시도하면 “ORA-24247: 네트워크 액세스가 ACL(액세스 제어 목록)에 의해 거부되었습니다.” 오류가 발생한다.

그럼 ACL 등록은 어떻게 하는 것일까? 이 역시 DBMS_NETWORK_ACL_ADMIN이라는 시스템 패키지를 이용해 등록할 수 있다. 먼저 이 패키지의 주요 서브 프로그램에 대해 알아보도록 하자.


① CREATE_ACL 프로시저

ACL을 생성하는 프로시저다.

    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
                 acl         IN VARCHAR2,
                 description IN VARCHAR2,
                 principal   IN VARCHAR2,
                 is_grant    IN BOOLEAN,
                 privilege   IN VARCHAR2,
                 start_date  IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
                 end_date    IN TIMESTAMP WITH TIMEZONE DEFAULT NULL );

• acl: 생성할 ACL 명칭, xxxx.xml 형태로 기술

• description: ACL에 대한 설명

• principal: ACL에 추가할 사용자 롤(권한) 명

• is_grant: 권한 부여 여부

• privilege: ‘connect’를 명시한다(호스트명을 IP주소로 명시할 때는 ‘resolve’)

• start_date: 시작일자. 디폴트 값은 NULL.

• end_date: 종료일자. 반드시 시작일자보다 크게 명시해야 하며 디폴트 값은 NULL


② ADD_PRIVILEGE 프로시저

특정 사용자에게 네트워크 접근 권한을 부여하는 프로시저다.

    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
                     acl        IN VARCHAR2,
                     principal  IN VARCHAR2,
                     is_grant   IN BOOLEAN,
                     privilege  IN VARCHAR2,
                     position   IN PLS_INTEGER DEFAULT NULL,
                     start_date IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
                     end_date   IN TIMESTAMP WITH TIMEZONE DEFAULT NULL );

• acl: ACL 명칭, xxxx.xml 형태로 기술

• principal: ACL에 추가할 사용자 롤(권한) 명, 대소문자 구분

• is_grant: 권한 부여 여부

• privilege: ‘connect’나 ‘resolve’

• position: ACL의 위치 값으로 생략 가능

• start_date: 시작일자. 디폴트 값은 NULL.

• end_date: 종료일자. 반드시 시작일자보다 크게 명시해야 하며 디폴트 값은 NULL


③ ASSIGN_ACL 프로시저

ACL에 호스트 컴퓨터, 도메인 혹은 IP 등을 할당하는 프로시저다.

    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
                     acl        IN VARCHAR2,
                     host       IN VARCHAR2,
                     lower_port IN PLS_INTEGER DEFAULT NULL
                     upper_port IN PLS_INTEGER DEFAULT NULL);

• acl: ACL 명칭

• host: 호스트. 호스트 명칭이나 IP 주소가 올 수 있다. 보통 ‘*’를 명시

• lower_port: TCP 포트 하한값

• upper_port: TCP 포트 상한값


④ ACL 등록 및 권한 할당

최소한 알아야 할 3개의 프로시저에 대해 살펴봤다. 이제 ACL을 등록해 보자. ACL 등록을 위해 CREATE_ACL 프로시저를 호출해 보자.

입력

    BEGIN

    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
              acl => 'my_mail.xml',
              description => '메일전송용 ACL',
              principal => 'ORA_USER',  -- ORA_USER란 사용자에게 권한 할당
              is_grant => true,
              privilege => 'connect');

      COMMIT;
    END;

결과

    익명 블록이 완료되었습니다.

오류 없이 실행되었다. 주의할 점은 principal 매개변수는 대소문자를 구분하니 반드시 사용자나 롤명으로는 대문자를 입력하도록 한다. 이제 ADD_PRIVILEGE 프로시저를 호출해 권한을 등록해 보자.

입력

    BEGIN

    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
              acl => 'my_mail.xml',
              principal => 'ORA_USER',  -- ORA_USER란 사용자에게 권한 할당
              is_grant => true,
              privilege => 'resolve');

      COMMIT;
    END;

결과

    익명 블록이 완료되었습니다.


마지막으로 ASSIGN_ACL 프로시저를 호출해 ACL과 호스트명을 연결해 보자.

입력

    BEGIN

    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
              acl => 'my_mail.xml',
              host => 'localhost',  -- 호스트명
              lower_port => 25 );

      COMMIT;
    END;

결과

    익명 블록이 완료되었습니다.

등록 작업은 모두 끝났다. 제대로 등록됐는지 확인하려면 DBA_NETWORK_ACLS 시스템 뷰를 조회해보면 된다.

입력

    SELECT *
      FROM DBA_NETWORK_ACLS;

결과

결과를 보면 성공적으로 등록됐음을 알 수 있다. 만약 이미 등록된 ACL을 삭제하려면 다음과 같이 DROP_ACL 프로시저를 호출하면 된다.

입력

    BEGIN
      DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
           acl =>'my_mail.xml');
    END;

이제 사전준비 작업은 모두 마쳤으니 본격적으로 UTL_SMPT 패키지에 대해 살펴 보자.


UTL_SMTP 패키지의 타입과 서브 프로그램

UTL_SMTP 패키지에 내장된 대표적인 타입, 함수, 프로시저에 대해 살펴 보자.

① CONNECTION 레코드 타입

SMTP 연결 정보가 있는 PL/SQL 레코드 타입이다. 구문과 주요 필드는 다음과 같다.

    TYPE connection IS RECORD (
        host            VARCHAR2(255),
        port            PLS_INTEGER,
        tx_timeout      PLS_INTEGER,
        private_tcp_con utl_tcp.connection,
        private_state   PLS_INTEGER);

• host: SMTP 서버명

• port: SMTP 포트

• tx_timeout: 연결 타임아웃


② REPLY, REPLIES 레코드 타입

SMTP 명령어를 사용할 때 서버로부터 응답코드를 받는데, 이 정보를 받는 레코드 타입이다.

        TYPE reply IS RECORD (
        code PLS_INTEGER,
        text VARCHAR2(508));

    TYPE replies IS TABLE OF reply INDEX BY BINARY_INTEGER;

• code: 3자리 응답코드

• text: 텍스트 메시지


③ OPEN_CONNECTION 함수

SMTP 서버와 연결을 하며 SMTP.connection을 반환하는 함수다.

    UTL_SMTP.OPEN_CONNECTION (
        host                          IN VARCHAR2,
        port                          IN PLS_INTEGER DEFAULT 25,
        tx_timeout                    IN PLS_INTEGER DEFAULT NULL,
        wallet_path                   IN VARCHAR2 DEFAULT NULL,
        wallet_password               IN VARCHAR2 DEFAULT NULL,
        secure_connection_before_smtp IN BOOLEAN DEFAULT FALSE)
    RETURN connection;

• host: SMTP 호스트명

• port: 포트번호

• tx_timeout: 연결 타임아웃

• wallet_path, wallet_password, secure_connection_before_smtp : SSL/TLS 연결을 위한 매개변수


④ HELO 프로시저

SMTP의 HELO 명령어 역할을 하며 같은 이름의 함수도 존재한다.

    UTL_SMTP.HELO (
        c      IN OUT NOCOPY connection,
        domain IN VARCHAR2);

• c: SMTP connection

• domain: 도메인명


⑤ MAIL 프로시저

SMTP의 MAIL 명령어 역할을 하며 같은 이름의 함수도 존재한다.

    UTL_SMTP.MAIL (
        c          IN OUT NOCOPY connection,
        sender     IN VARCHAR2,
        parameters IN VARCHAR2 DEFAULT NULL);

• c: SMTP connection

• sender: 보내는 메일 주소

• parameter: 추가 매개변수


⑥ RCPT 프로시저

SMTP의 RCPT 명령어 역할을 하며 같은 이름의 함수도 존재한다.

    UTL_SMTP.RCPT (
        c          IN OUT NOCOPY connection,
        recipient  IN VARCHAR2,
        parameters IN VARCHAR2 DEFAULT NULL);

• c: SMTP connection

• recipient: 받는 메일 주소

• parameter: 추가 매개변수


⑦ OPEN_DATA 프로시저

SMTP의 DATA 명령어 역할을 하는데, 뒤이어 설명할 WRITE_DATA, WRITE_RAW_DATA를 호출하기 전에 반드시 호출해야 하는 프로시저이며, 같은 이름의 함수도 존재한다. OPEN_DATA를 호출하기 전에 반드시 OPEN_CONNECTION, HELO, MAIL, RCTP가 먼저 호출되어야 한다.

    UTL_SMTP.OPEN_DATA (
        c IN OUT NOCOPY connection);

• c: SMTP connection


⑧ WRITE_DATA 프로시저

메일 내용을 작성하는 프로시저로, SMTP DATA 명령어로 From, To, Subject 등 메일 본문을 작성한다. 메일 본문의 내용은< CR><LF>(이 두 값 입력은 UTL_TCP.CRLF 함수를 사용한다)로 분리된다.

    UTL_SMTP.WRITE_DATA (
        c    IN OUT NOCOPY connection,
        data IN VARCHAR2 CHARACTER SET ANY_CS);

• c: SMTP connection

• data: 헤더를 포함한 이메일 메시지의 텍스트 부분. F‘ rom’, ‘To’, ‘Subject’ 등이 이에 해당됨.

WRITE_DATA 프로시저는 data 매개변수로 들어오는 텍스트를 US7ASCII로 변환한 다음 전송하는데 변환에 실패한 문자는 ‘?’로 바뀐다. 따라서 영어가 아닌 다중 바이트 문자를 사용하면 글자가 깨져 “???…” 형태로 전송되므로 한글을 사용하려면 바로 다음에 설명할 WRITE_RAW_DATA 프로시저를 사용해야 한다.


⑨ WRITE_RAW_DATA 프로시저

WRITE_DATA와 같은 역할을 하지만 data 매개변수의 형태가 RAW 타입이다. 따라서 한글 같은 다중 바이트 메시지를 전송할 때는 이 프로시저를 사용해야 한다.

    UTL_SMTP.WRITE_RAW_DATA (
        c    IN OUT NOCOPY connection,
        data IN RAW) ;

• c: SMTP connection

• data: 헤더를 포함한 이메일 메시지의 텍스트 부분. F‘ rom’, ‘To’, ‘Subject’ 등이 이에 해당되며 RAW 타입임.

매개변수가 RAW 타입이므로 이 함수를 사용할 때는 문자를 RAW로 변경해야 하는데, 이는 UTL_RAW.CAST_TO_RAW 함수를 사용하면 된다.


⑩ CLOSE_DATA

WRITE_DATA나 WRITE_RAW_DATA를 이용해 메일 본문 작성을 마친 후 호출하며 본문 작성이 끝났음을 알리는 역할을 한다. SMTP 명령어로 본문 작성 시 “.”을 입력하는 것이라고 보면 된다.

    UTL_SMTP.CLOSE_DATA (
        c IN OUT NOCOPY connection);

• c: SMTP connection


⑪ QUIT 함수

SMTP의 QUIT 명령어와 같은 역할을 하며, 메일 세션을 종료하고 SMTP 서버와 연결을 끊는다.

    UTL_SMTP.QUIT (
        c IN OUT NOCOPY connection);

• c: SMTP connection


⑫ NOOP 프로시저

SMTP의 NOOP 명령어와 동일한 역할을 하며 같은 이름의 함수도 존재한다.

    UTL_SMTP.NOOP (
        c IN OUT NOCOPY connection);

• c: SMTP connection


⑬ RSET 프로시저

SMTP의 RSET 명령어처럼 메일의 트랜잭션을 종료시키며 같은 이름의 함수도 존재한다.

    UTL_SMTP.RSET (
        c IN OUT NOCOPY connection);

• c: SMTP connection


⑭ 기타

지금까지 설명한 서브 프로그램 이외에도 EHLO, AUTH, STARTTLS 등의 서브 프로그램이 있는데, 이들은 인증이나 SSL(Secure Socket Layer) / TSL(Transport Layer Security)을 이용해 SMTP와 연결할 때 사용된다. SSL과 TSL은 보안과 인증을 위한 보안 프로토콜로 자세한 내용은 관련 서적을 참조하길 바란다.


UTL_SMTP를 이용한 메일 전송

이제 모든 준비를 마쳤으니 UTL_SMTP 패키지를 사용해 메일을 보내는 방법을 살펴볼텐데, 먼저 텍스트로만 이루어진 간단한 메일 전송부터 시작해서 첨부파일이 포함된 메일까지 전송해 볼 것이다.


간단한 메일 전송

지금까지 배운 내용을 토대로 간단한 메일을 전송해 보자. 아래의 익명 블록을 실행해 보자.

입력

    DECLARE
      vv_host    VARCHAR2(30) := 'localhost'; -- SMTP 서버명
      vn_port    NUMBER := 25;                -- 포트번호
      vv_domain  VARCHAR2(30) := 'hong.com';

      vv_from    VARCHAR2(50) := 'charieh@hong.com';  -- 보내는 주소
      vv_to      VARCHAR2(50) := 'charieh@hong.com';  -- 받는 주소

      c utl_smtp.connection;  -- SMTP 서버 연결 객체


    BEGIN
      c := UTL_SMTP.OPEN_CONNECTION(vv_host, vn_port);  -- SMPT 서버와 연결

      UTL_SMTP.HELO(c, vv_domain); -- HELO

      UTL_SMTP.MAIL(c, vv_from);   -- 보내는사람
      UTL_SMTP.RCPT(c, vv_to);     -- 받는사람

      UTL_SMTP.OPEN_DATA(c); -- 메일 본문 작성 시작, SMTP의 data 명령어 역할
      -- 각 메시지는 <CR><LF>로 분리한다. 이는 UTL_TCP.CRLF 함수를 이용한다.
      UTL_SMTP.WRITE_DATA(c,'From: ' || '"hong2" <charieh@hong.com>' || UTL_TCP.CRLF ); -- 보내는사람
      UTL_SMTP.WRITE_DATA(c,'To: ' || '"hong1" <charieh@hong.com>' || UTL_TCP.CRLF );   -- 받는사람
      UTL_SMTP.WRITE_DATA(c,'Subject: Test' || UTL_TCP.CRLF );                                                   -- 제목
      UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF );  -- 한 줄 띄우기
      UTL_SMTP.WRITE_DATA(c,'THIS IS SMTP_TEST1 ' || UTL_TCP.CRLF );  -- 본문

      UTL_SMTP.CLOSE_DATA(c); -- 메일 본문 작성 종료. SMTP 명령어의 “.” 역할

      -- 종료
      UTL_SMTP.QUIT(c);


    EXCEPTION
      WHEN UTL_SMTP.INVALID_OPERATION THEN
           dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
           dbms_output.put_line(sqlerrm);
           UTL_SMTP.QUIT(c);
      WHEN UTL_SMTP.TRANSIENT_ERROR THEN
           dbms_output.put_line(' Temporary e-mail issue - try again');
           UTL_SMTP.QUIT(c);
      WHEN UTL_SMTP.PERMANENT_ERROR THEN
           dbms_output.put_line(' Permanent Error Encountered.');
           dbms_output.put_line(sqlerrm);
           UTL_SMTP.QUIT(c);
      WHEN OTHERS THEN
         dbms_output.put_line(sqlerrm);
         UTL_SMTP.QUIT(c);
    END;

결과

    익명 블록이 완료되었습니다.

위 소스를 보면 SMTP 명령어를 사용해서 직접 메일을 전송했던 것과 별 차이가 없다. SMTP 명령어 자리에 이에 대응되는 UTL_SMTP의 서브 프로그램을 사용했을 뿐이다. 이제 Outlook으로 위 익명 블록을 실행해 전송한 메일을 열어 보자.

▼ 그림 18-4 간편 메일 전송

오류 없이 제대로 실행되었고 메일도 제대로 전송되었음을 알 수 있다.


한글 메일 전송

이전 예제에서는 메일 본문 내용을 전송할 때 WRITE_DATA를 사용했는데, 이 프로시저는 내부적으로 본문 내용을 US7ASCII로 변환하고 변환에 실패한 문자는 ‘?’로 변환한다고 했다. 실제로 그렇게 되는지 이전 예제의 본문 내용을 다음과 같이 한글로 작성 후 익명 블록을 실행해 보자.

입력

    ...
    ...
    UTL_SMTP.WRITE_DATA(c,'From: ' || '"hong2" <charieh@hong.com>' || UTL_TCP.CRLF );
    -- 보내는사람
      UTL_SMTP.WRITE_DATA(c,'To: ' || '"hong1" <charieh@hong.com>' || UTL_TCP.CRLF );
    -- 받는사람
      UTL_SMTP.WRITE_DATA(c,'Subject: Test' || UTL_TCP.CRLF );
    -- 제목
      UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF );
    -- 한 줄 띄우기
      UTL_SMTP.WRITE_DATA(c,'한글 메일 테스트' ' || UTL_TCP.CRLF ); -- 본문을 한글로...

      UTL_SMTP.CLOSE_DATA(c);  -- 메일 본문 작성 종료
    ...
    ...

결과

    익명 블록이 완료되었습니다.
▼ 그림 18-5 한글이 깨진 메일

‘한글 메일 테스트’라고 작성했던 본문 내용이 깨져 ‘?’로 표시된 것을 알 수 있다. 따라서 한글과 같은 다중 바이트 문자는 WRITE_RAW_DATA 프로시저를 사용해야 한다. 이때 매개변수로 들어오는 문자는 UTL_RAW.CAST_TO_RAW 함수를 사용해 VARCHAR2 타입을 RAW 타입으로 변환해 줘야 한다.

입력

    ...
    ...
    UTL_SMTP.WRITE_DATA(c,'From: ' || '"hong2" <charieh@hong.com>' || UTL_TCP.CRLF ); -- 보내는사람
    UTL_SMTP.WRITE_DATA(c,'To: ' || '"hong1" <charieh@hong.com>' || UTL_TCP.CRLF );   -- 받는사람
    UTL_SMTP.WRITE_DATA(c,'Subject: Test' || UTL_TCP.CRLF );                          -- 제목
    UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF );                                            -- 한 줄 띄우기
    -- 본문을 한글로 작성하고, 이를 RAW 타입으로 변환한다.
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW('한글 메일 테스트' || UTL_TCP.CRLF)  );
    UTL_SMTP.CLOSE_DATA(c); -- 메일 본문 작성 종료
    ...
    ...

결과

    익명 블록이 완료되었습니다.
▼ 그림 18-6 한글로 작성된 메일

보낸 사람, 받는 사람, 제목도 한글로 작성할 수 있다. 이런 경우 본문 내용 전체를 VARCHAR2 변수에 넣고 UTL_RAW.CAST_TO_RAW로 이 변수를 변환한 다음 WRITE_RAW_DATA 프로시저를 1번만 호출하면 된다.

입력

    DECLARE
      vv_host    VARCHAR2(30) := 'localhost'; -- SMTP 서버명
      vn_port    NUMBER := 25;                -- 포트번호
      vv_domain  VARCHAR2(30) := 'hong.com';  -- 도메인 명

      vv_from    VARCHAR2(50) := 'charieh@hong.com';  -- 보내는 주소
      vv_to      VARCHAR2(50) := 'charieh@hong.com';  -- 받는 주소
      vv_text    VARCHAR2(300);  -- 본문내용을 담을 변수

      c utl_smtp.connection;  -- SMTP 서버 연결 객체
    BEGIN
      c := UTL_SMTP.OPEN_CONNECTION(vv_host, vn_port);

      UTL_SMTP.HELO(c, vv_domain); -- HELO

      UTL_SMTP.MAIL(c, vv_from);   -- 보내는사람
      UTL_SMTP.RCPT(c, vv_to);     -- 받는사람

      UTL_SMTP.OPEN_DATA(c); -- 메일본문 작성 시작

      vv_text := 'From: ' || '"홍길동" <charieh@hong.com>' || UTL_TCP.CRLF;            -- 보내는사람
      vv_text :=  vv_text || 'To: ' || '"홍길동" <charieh@hong.com>' || UTL_TCP.CRLF;  -- 받는 사람
      vv_text :=  vv_text || 'Subject: 한글제목' || UTL_TCP.CRLF;                         -- 제목
      vv_text :=  vv_text || UTL_TCP.CRLF;                                            -- 한 줄 띄우기
      vv_text :=  vv_text || '한글 메일 테스트' || UTL_TCP.CRLF;                      -- 메일본문


      -- 본문 전체를 한번에 RAW 타입으로 변환 후 메일내용 작성
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW(vv_text)  );

      UTL_SMTP.CLOSE_DATA(c); -- 메일 본문 작성 종료
      UTL_SMTP.QUIT(c);  -- 종료


    EXCEPTION
      WHEN UTL_SMTP.INVALID_OPERATION THEN
           dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
           dbms_output.put_line(sqlerrm);
           UTL_SMTP.QUIT(c);
      WHEN UTL_SMTP.TRANSIENT_ERROR THEN
           dbms_output.put_line(' Temporary e-mail issue - try again');
           UTL_SMTP.QUIT(c);
      WHEN UTL_SMTP.PERMANENT_ERROR THEN
           dbms_output.put_line(' Permanent Error Encountered.');
           dbms_output.put_line(sqlerrm);
           UTL_SMTP.QUIT(c);
      WHEN OTHERS THEN
         dbms_output.put_line(sqlerrm);
         UTL_SMTP.QUIT(c);
    END;

결과

    익명 블록이 완료되었습니다.

성공적으로 실행되었다. Outlook에서 메일을 열어보면 모두 한글로 정상적으로 전송된 것을 알 수 있다.

▼ 그림 18-7 본문 전체가 한글로 작성된 메일

HTML 메일 보내기

지금까지는 간단한 텍스트로 이루어진 메일을 전송했지만 요즘 대부분의 메일은 HTML 형식으로 전송되므로 이번에는 HTML로 작성된 메일을 전송해 보자. HTML 메일이라고 해서 특별히 다른 점이 있는 것이 아니라 메일 본문 내용이 HTML로 작성되어 있다는 것인데 MIME 타입을 HTML 형태로 맞춰주기만 하면 된다.


MIME

HTML 형식의 메일을 보내기 전에 먼저 MIME에 대해 간단히 알아보자. MIME은 ‘Multipurpose Internet Mail Extensions’의 약자로 직역하면 “다목적 인터넷 메일 확장” 정도로 옮길 수 있다. 이전 절에서 설명했듯이 SMTP로는 7비트 ASCII 문자만 이메일로 보낼 수 있었다. 하지만 시간이 지나면서 이 외에도 그림, 음악 파일 등을 전송할 필요성이 생겼고 이를 위해 만든(확장한) 추가적인 표준이 MIME이다. 물론 기존 7비트 ASCII 문자로 표현할 수 없는 한국어, 중국어 등 7비트가 넘어가는 다른 문자들도 MIME을 사용해 이메일로 전송할 수 있다(이전 절에서 WRITE_RAW_DATA 프로시저를 사용해 한글로 작성된 메일을 보낸 것과 MIME을 혼동해서는 안 된다. WRITE_RAW_DATA 프로시저는 오라클에서 제공하는 UTL_SMTP 패키지 내의 프로시저고 MIME은 표준이다).

한 마디로 말해 MIME이란 인터넷 표준을 통해서 영문자 이외의 문자를 포함해 문자가 아닌 그림, 음악 파일까지도 이메일로 전송할 수 있게 된 것이다. MIME을 구성하는 기본 요소들은 다음과 같다.


MIME-Version

• 해당 메시지가 MIME 형식임을 나타내며 현재 버전은 1.0이다.

• 사용 예: MIME-Version: 1.0

Content-Type

• 해당 메시지가 어떤 형식인지 나타낸다. 기본값은 “text/plain” 이며 이 외에도 “text/html”, “img/gif”, “multipart/mixed” 등 사용할 수 있는 값의 종류가 매우 많다.

• 사용 예: Content-type: text/html; charset=euc-kr→ HTML을 사용하고 한글을 사용한다는 의미

Content-Disposition

• 메일 메시지의 프리젠테이션 스타일을 규정한다.

• 사용 예: Content-Disposition: attachment; filename=back.jpg;→back.jpg 라는 파일을 첨부한다는 의미

위 3가지 외에도 MIME을 구성하는 요소들과 각 요소에 대한 값의 종류는 매우 많다. MIME에 관해 좀더 자세한 내용은 관련 서적이나 자료를 참조하길 바라며, 이 장에서는 진행해가면서 필요한 내용만 간략히 설명하도록 하겠다.


HTML 메일 보내기

HTML 형식으로 작성된 메일을 전송해 볼 텐데 HTML 메일 본문 내용은 다음과 같다.

    <HTML>
      <HEAD>
        <TITLE>HTML 테스트</TITLE>
      </HEAD>
      <BDOY>
        <p>이 메일은 <b>HTML</b><i>버전</i>으로</p>
        <p>작성된 <strong>메일</strong>입니다. </p>
      </BODY>
    </HTML>


이제 위 내용을 전송하는 익명 블록을 만들어 보자.

입력

    DECLARE
      vv_host    VARCHAR2(30) := 'localhost'; -- SMTP 서버명
      vn_port    NUMBER := 25;                -- 포트번호
      vv_domain  VARCHAR2(30) := 'hong.com';  -- 도메인명
      vv_from    VARCHAR2(50) := 'charieh@hong.com';  -- 보내는 주소
      vv_to      VARCHAR2(50) := 'charieh@hong.com';  -- 받는 주소

      c utl_smtp.connection;
      vv_html    VARCHAR2(200); -- HTML 메시지를 담을 변수
    BEGIN
      c := UTL_SMTP.OPEN_CONNECTION(vv_host, vn_port);

      UTL_SMTP.HELO(c, vv_domain); -- HELO
      UTL_SMTP.MAIL(c, vv_from);   -- 보내는사람
      UTL_SMTP.RCPT(c, vv_to);     -- 받는사람

      UTL_SMTP.OPEN_DATA(c); -- 메일본문 작성 시작
      UTL_SMTP.WRITE_DATA(c,'MIME-Version: 1.0' || UTL_TCP.CRLF ); -- MIME 버전
      -- Content-Type: HTML 형식, 한글을 사용하므로 문자셋은 euc-kr
      UTL_SMTP.WRITE_DATA(c,'Content-Type: text/html; charset="euc-kr"' || UTL_TCP.CRLF );

      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW('From: ' || '"홍길동" <charieh@hong.com>' || UTL_TCP.CRLF) ); -- 보내는사람
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW('To: ' || '"홍길동" <charieh@hong.com>' || UTL_TCP.CRLF) );  -- 받는사람
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW('Subject: HTML 테스트 메일' || UTL_TCP.CRLF) );  -- 제목
      UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF );  -- 한 줄 띄우기

      -- HTML 본문을 작성
      vv_html := '<HEAD>
       <TITLE>HTML 테스트</TITLE>
     </HEAD>
     <BDOY>
        <p>이 메일은 <b>HTML</b> <i>버전</i> 으로 </p>
        <p>작성된 <strong>메일</strong>입니다. </p>
     </BODY>
    </HTML>';

      -- 메일 본문
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW(vv_html || UTL_TCP.CRLF)  );

      UTL_SMTP.CLOSE_DATA(c); -- 메일 본문 작성 종료
      UTL_SMTP.QUIT(c);  -- 메일 세션 종료

    EXCEPTION
      WHEN UTL_SMTP.INVALID_OPERATION THEN
           dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
           dbms_output.put_line(sqlerrm);
           UTL_SMTP.QUIT(c);
      WHEN UTL_SMTP.TRANSIENT_ERROR THEN
           dbms_output.put_line(' Temporary e-mail issue - try again');
           UTL_SMTP.QUIT(c);
      WHEN UTL_SMTP.PERMANENT_ERROR THEN
           dbms_output.put_line(' Permanent Error Encountered.');
           dbms_output.put_line(sqlerrm);
           UTL_SMTP.QUIT(c);
      WHEN OTHERS THEN
         dbms_output.put_line(sqlerrm);
         UTL_SMTP.QUIT(c);
    END;

결과

    익명 블록이 완료되었습니다.


성공적으로 실행되었다. 코드 중간중간 삽입된 주석을 보면 어렵지 않게 이해할 수 있을 것이다. 간단히 설명하면, 메일 본문을 작성 시작 지점에 MIME 버전과 Content-Type을 text/html, 그리고 문자셋을 euc-kr로 설정해서 HTML과 한글을 사용할 수 있게 했다. 그리고 메일 내용은 HTML로 작성해 vv_html이란 변수에 넣은 다음 WRITE_RAW_DATA 프로시저를 이용해 작성했다. 그럼 Outlook으로 발송된 메일을 열어 보자.

▼ 그림 18-8 HTML로 작성된 메일

위 그림처럼 MIME 속성을 추가해 적당한 값을 설정하면 HTML로 작성된 메일도 보낼 수 있다.


첨부파일 보내기

마지막으로 메일에 파일을 첨부해서 보내 보자. 텍스트나 HTML로 작성된 메일을 보내는 것과는 달리 파일을 첨부해 보내려면 처리할 내용이 더 있는데, 먼저 이메일에 파일을 첨부하기 위해 필요한 내용에 대해 하나씩 살펴 보자.


① 파일 처리

메일에 파일을 첨부하려면 첨부할 파일이 필요한데 문제는 PL/SQL을 통해 메일을 보낸다는 점이다. 즉 PL/SQL 상에서 어떤 식으로든 파일을 읽어와야 하는데 이것이 그리 간단한 문제가 아니다. PL/SQL은 오라클이라는 DB 상에 존재하는 반면 파일은 윈도우나 유닉스 같은 운영체제 상에 존재한다. 즉 PL/SQL에서 운영체제 관할의 파일을 읽어와야 한다는 말인데, 어떻게 해야 할까?

너무 고민할 필요가 없다. 친절하게도 오라클에는 PL/SQL에서 파일을 처리할 수 있도록 UTL_FILE이란 시스템 패키지를 제공하고 있다. UTL_FILE 패키지에는 운영체제 상에 있는 파일을 열고, 읽고, 쓰는데 필요한 각종 함수와 프로시저가 내장되어 있다. 그런데 이 패키지에 대해 살펴보기 전에 알아야 할 내용이 또 있다. 바로 그 주인공은 디렉토리다.

파일은 디렉토리 상에 존재한다. 따라서 UTL_FILE 패키지를 사용하기 전에 먼저 PL/SQL 상에서 운영체제의 디렉토리에 접근할 수 있어야 한다. 오라클에서는 디렉토리(DIRECTORY) 객체를 사용해 운영체제에 있는 실제 디렉토리에 접근할 수 있다. 먼저 C 드라이브 밑에 “ch18_file”이란 폴더(디렉토리)를 만든 다음, 다음과 같이 이 디렉토리를 가리키는 DIRECTORY 객체를 생성해 보자.

입력

    -- Directory 객체 생성
    CREATE OR REPLACE DIRECTORY SMTP_FILE AS 'C:\ch18_file';

결과

    directory SMTP_FILE이(가) 생성되었습니다.

이제 간단한 텍스트 파일을 만들어 “ch18_file” 폴더에 저장해 보자.

▼ 그림 18-9 첨부파일로 사용할 텍스트 파일

[그림 18-9]의 텍스트 파일은 위키피디아에서 MIME에 대한 내용 일부를 가져온 것이고 이를 ch18_txt_file’ 라는 이름으로 저장했다. 이제 PL/SQL 상에서 ‘SMTP_FILE’ 이라는 디렉토리 객체에 접근해 방금 저장한 파일에 접근할 수 있다.


② UTL_FILE 패키지

UTL_FILE 시스템 패키지에는 파일 입출력을 담당하는 여러 함수와 프로시저가 내장되어 있는데, 여기에서는 파일을 이메일에 첨부해야 하므로 파일 읽기에 관련된 서브 프로그램에 대해서만 살펴볼 것이다.


FILE_TYPE 레코드 타입
    TYPE file_type IS RECORD (
       id          BINARY_INTEGER,
       datatype    BINARY_INTEGER,
       byte_mode   BOOLEAN);

• id: 파일 핸들러를 가리키는 숫자 값

• datatype: CHAR, NCAHR, BINARY 파일인지를 가리키는 숫자 값

• byte_mode: 이진 파일로 열렸으면 TRUE, 그렇지 않으면 FALSE


FOPEN 함수

파일을 여는 함수로 파일 핸들을 가리키는 FILE_TYPE 레코드 타입을 반환한다.

    UTL_FILE.FOPEN (
        location     IN VARCHAR2,
        filename     IN VARCHAR2,
        open_mode    IN VARCHAR2,
        max_linesize IN BINARY_INTEGER DEFAULT 1024)
        RETURN FILE_TYPE;

• location: 파일이 위치한 디렉토리 객체명

• filename: 확장자를 포함한 파일명 (디렉토리명은 제외)

• open_mode: 오픈 모드 (r: 읽기, w: 쓰기, a: 덧붙이기, rb: 바이트모드로 읽기, wb: 바이트 모드로 쓰기, ab: 바이트 모드로 덧붙이기)

• max_linesize: 한 줄당 최대 문자 수로 디폴트 값은 1024, 최대치는 32767


FOPEN_NCHAR 함수

FOPEN 함수와 같지만 이 함수는 영어 이외의 언어로 작성된 파일을 연다.

    UTL_FILE.FOPEN_NCHAR (
    location     IN VARCHAR2,
    filename     IN VARCHAR2,
    open_mode    IN VARCHAR2,
    max_linesize IN BINARY_INTEGER DEFAULT 1024)
      RETURN FILE_TYPE;

• location: 파일이 위치한 디렉토리 객체명

• filename: 확장자를 포함한 파일명 (디렉토리명은 제외)

• open_mode: 오픈 모드 (r: 읽기, w: 쓰기, a: 덧붙이기, rb: 바이트 모드로 읽기, wb: 바이트 모드로 쓰기, ab: 바이트 모드로 덧붙이기)

• max_linesize: 한 줄당 최대 문자 수로 디폴트 값은 1024, 최대치는 32767


GET_LINE 프로시저

오픈한 파일의 텍스트를 읽어 이를 OUT 매개변수인 buffer 변수에 담는다. GET_LINE 프로시저를 사용하려면 FOPEN 함수에서 파일을 읽기( r ) 모드로 열어야 한다.

    UTL_FILE.GET_LINE (
      file   IN  FILE_TYPE,
      buffer OUT VARCHAR2,
      len    IN  PLS_INTEGER DEFAULT NULL);

• file: FOPEN으로 연 파일의 핸들 값

• buffer: 파일을 읽은 내용을 담는 버퍼, 한 줄 단위로 읽는다.

• len: 파일에서 읽을 최대 바이트 수


GET_LINE_NCHAR 프로시저

GET_LINE과 FOPEN이 한 쌍이듯, GET_LINE_NCHAR 프로시저와 FOPEN_NCHAR 함수가 한 쌍이다.

    UTL_FILE.GET_LINE_NCHAR (
      file   IN  FILE_TYPE,
      buffer OUT VARCHAR2,
      len    IN  PLS_INTEGER DEFAULT NULL);

• file: FOPEN으로 연 파일의 핸들 값

• buffer: 파일을 읽은 내용을 담는 버퍼, 한 줄 단위로 읽는다.

• len: 파일에서 읽을 최대 바이트 수


GET_RAW 프로시저

GET_LINE과 같은 기능을 하나 파일에서 읽은 내용을 담는 buffer 변수의 타입이 RAW 타입이다.

    UTL_FILE.GET_RAW (
      file   IN  FILE_TYPE,
      buffer OUT RAW
      len    IN  PLS_INTEGER DEFAULT NULL);

• file: FOPEN으로 연 파일의 핸들 값

• buffer: 파일을 읽은 내용을 담는 버퍼, 한 줄 단위로 읽는다.

• len: 파일에서 읽을 최대 바이트 수


FCLOSE 프로시저

파일 핸들을 이용해 연 파일을 닫는다.

    UTL_FILE.FCLOSE (
      file IN OUT FILE_TYPE );

• file: FOPEN이나 FOPEN_NCHAR로 연 파일 핸들러

그럼 지금까지 배운 내용을 토대로 매개변수로 디렉토리명과 파일명을 받아 이 파일을 읽어 RAW 타입으로 반환하는 함수를 만들어 보자. 굳이 RAW 타입으로 반환하는 이유는, 이메일에 첨부할 파일을 RAW 타입으로 넘겨야 하기 때문이다.

입력

    CREATE OR REPLACE FUNCTION fn_get_raw_file ( p_dir   VARCHAR2,
                                                 p_file  VARCHAR2)
        RETURN RAW
    IS
        vf_buffer RAW(32767);
        vf_raw    RAW(32767); --반환할 파일

        vf_type  UTL_FILE.FILE_TYPE;
    BEGIN
      -- 파일을 바이트모드로 읽는다.
      -- p_dir : 디렉토리명, p_file : 파일명, rb: 바이트모드로 읽기
      vf_type := UTL_FILE.FOPEN ( p_dir, p_file, 'rb');

      -- 파일이 오픈됐는지 IS_OPEN 함수를 이용해 확인.
      IF UTL_FILE.IS_OPEN ( vf_type ) THEN
         -- 루프를 돌며 파일을 읽는다.
         LOOP
         BEGIN
           -- GET_RAW 프로시저로 파일을 읽어 vf_buffer 변수에 담는다.
           UTL_FILE.GET_RAW(vf_type, vf_buffer, 32767);
           -- 반환할 RAW 타입 변수에 vf_buffer를 할당.
           vf_raw := vf_raw || vf_buffer;

         EXCEPTION
             -- 더 이상 가져올 데이터가 없으면 루프를 빠져나간다.
               WHEN NO_DATA_FOUND THEN
               EXIT;
         END;
         END LOOP;
      END IF;

      -- 파일을 닫는다.
      UTL_FILE.FCLOSE(vf_type);
      -- RAW 타입 변수를 반환
      RETURN vf_raw;
    END;

결과

    FUNCTION FN_GET_RAW_FILE이(가) 컴파일되었습니다.


③ 첨부파일 처리를 위한 MIME

파일을 다루는 작업까지 마쳤으니 마지막으로 알아야 할 것은 추가적인 MIME에 대한 내용이다.

    Content-Type: multipart/mixed; boundary

• 이전에 배웠던 Content-Type은 text/plain과 text/html이었는데, 이 외에도 사용할 수 있는 값은 매우 많다. 그 중에서 multipart/mixed는 보내는 메일 내용 중에 본문 메시지뿐만 아니라 첨부파일도 포함되어 있는, 즉 여러 종류의 Content-Type이 섞여 있을 때 사용된다. 또한 첨부파일을 메일 본문에 넣을 때도 파일에 대한 Content-Type을 별도로 지정해줘야 하는데, 디폴트 값은 ‘application/octet’ 이다.

• boundary: 여러 타입이 섞여 있으면 boundary 값을 통해 타입을 분리해야 한다. boundary의 값으로는 메일 본문의 내용과 겹치지 않는 유일한 문자를 사용해야 하는데, 보통 무작위로 충분히 긴 문자(예를 들어, IDEOWKJ989LWFEW 같은)를 만들어 사용한다. 또한 메일 본문에 boundary를 추가할 때 boundary 값 앞에 ‘–’를 붙여야 하며, 가장 마지막에 붙이는 boundary의 맨 끝에 ‘–’를 반드시 붙여야 한다.

    Content-Transfer-Encoding: base64

• 파일은 이진 데이터로 구성한다. SMTP에 따르면 메일은 ASCII 문자로 전송되어야 하는데, Content-Transfer-Encoding은 이진 데이터 파일을 ASCII로 변환하는 방법을 정의하는 내용이다. BASE64는 이진 데이터를 알파벳과 숫자, 특수문자로 변환하는 인코딩 방법 중 하나다. 즉 이메일을 보낼 때 첨부파일은 BASE64 방식으로 인코딩되어 전송된다.

    Content-Disposition: attachment; filename="파일명"

• 첨부파일 전송 시 해당 파일명을 명시하는 부분이다.

이상이 첨부파일 전송 시 알아야 할 내용이다. 메일을 보내고 파일을 첨부하는데 있어 이 정도 내용만 알고 있으면 사전 준비는 모두 끝난 셈이다.


④ 파일을 첨부해 메일 전송

이제 실제로 파일이 첨부된 이메일을 전송해 보자. 이번에도 익명 블록 형태로 예제를 선보일 텐데, 이 절에서 지금까지 다뤘던 모든 내용이 코드로 집약되어 있고 좀 복잡하므로 차근차근 살펴보자.

입력

    DECLARE
      vv_host    VARCHAR2(30) := 'localhost'; -- SMTP 서버명
      vn_port    NUMBER := 25;                -- 포트번호
      vv_domain  VARCHAR2(30) := 'hong.com';
      vv_from    VARCHAR2(50) := 'charieh@hong.com';  -- 보내는 주소
      vv_to      VARCHAR2(50) := 'charieh@hong.com';  -- 받는 주소

      c            utl_smtp.connection;
      vv_html      VARCHAR2(200); -- HTML 메시지를 담을 변수
      -- boundary 표시를 위한 변수, unique한 임의의 값을 사용하면 된다.
      vv_boundary  VARCHAR2(50) := 'DIFOJSLKDFO.WEFOWJFOWE';

      vv_directory  VARCHAR2(30) := 'SMTP_FILE'; --파일이 있는 디렉토리명
      vv_filename   VARCHAR2(30) := 'ch18_txt_file.txt';  -- 파일명
      vf_file_buff  RAW(32767);   -- 실제 파일을 담을 RAW타입 변수
      vf_temp_buff  RAW(54);
      vn_file_len   NUMBER := 0;  -- 파일 길이

      -- 한 줄당 올 수 있는 BASE64 변환된 데이터 최대 길이
      vn_base64_max_len  NUMBER := 54; --76 * (3/4);
      vn_pos             NUMBER := 1; --파일 위치를 담는 변수
      -- 파일을 한 줄씩 자를 때 사용할 단위 바이트 수
      vn_divide          NUMBER := 0;
    BEGIN
      c := UTL_SMTP.OPEN_CONNECTION(vv_host, vn_port);

      UTL_SMTP.HELO(c, vv_domain); -- HELO
      UTL_SMTP.MAIL(c, vv_from);   -- 보내는사람
      UTL_SMTP.RCPT(c, vv_to);     -- 받는사람

      UTL_SMTP.OPEN_DATA(c); -- 메일 본문 작성 시작
      UTL_SMTP.WRITE_DATA(c,'MIME-Version: 1.0' || UTL_TCP.CRLF ); -- MIME 버전
      -- Content-Type: multipart/mixed, boundary 입력
      UTL_SMTP.WRITE_DATA(c,'Content-Type: multipart/mixed; boundary="' || vv_boundary || '"' || UTL_TCP.CRLF);

      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW('From: ' || '"홍길동" <charieh@hong.com>' || UTL_TCP.CRLF) );
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW('To: ' || '"홍길동" <charieh@hong.com>' || UTL_TCP.CRLF) );
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW('Subject: HTML 첨부파일 테스트' || UTL_TCP.CRLF) );
      UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF );

      -- HTML 본문을 작성
      vv_html := '<HEAD>
       <TITLE>HTML 테스트</TITLE>
     </HEAD>
     <BDOY>
        <p>이 메일은 <b>HTML</b> <i>버전</i> 으로 </p>
        <p>첨부파일까지 들어간 <strong>메일</strong>입니다. </p>
     </BODY>
    </HTML>';

      -- 메일 본문, Content-Type이 바뀌므로 boundary 추가
      UTL_SMTP.WRITE_DATA(c, '--' || vv_boundary || UTL_TCP.CRLF );
      UTL_SMTP.WRITE_DATA(c, 'Content-Type: text/html;' || UTL_TCP.CRLF );
      UTL_SMTP.WRITE_DATA(c, 'charset=euc-kr' || UTL_TCP.CRLF );
      UTL_SMTP.WRITE_DATA( c, UTL_TCP.CRLF );
      UTL_SMTP.WRITE_RAW_DATA(c, UTL_RAW.CAST_TO_RAW(vv_html || UTL_TCP.CRLF)  );
      UTL_SMTP.WRITE_DATA( c, UTL_TCP.CRLF );

      -- 첨부파일 추가
      UTL_SMTP.WRITE_DATA(c, '--' || vv_boundary || UTL_TCP.CRLF );
      -- 파일의 Content-Type은 application/octet-stream
      UTL_SMTP.WRITE_DATA(c,'Content-Type: application/octet-stream; name="' || vv_filename || '"' || UTL_TCP.CRLF);
      UTL_SMTP.WRITE_DATA(c,'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
      UTL_SMTP.WRITE_DATA(c,'Content-Disposition: attachment; filename="' || vv_filename || '"' || UTL_TCP.CRLF);
      UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF);

      -- fn_get_raw_file 함수를 사용해 실제 파일을 읽어 온다
      vf_file_buff := fn_get_raw_file(vv_directory, vv_filename);
      -- 파일의 총 크기를 가져온다.
      vn_file_len := DBMS_LOB.GETLENGTH(vf_file_buff);

      -- 파일전체 크기가 vn_base64_max_len 보다 작다면, 분할단위수인 vn_divide 값은 파일크기로 설정
      IF vn_file_len <= vn_base64_max_len THEN
         vn_divide := vn_file_len;
      ELSE -- 그렇지 않다면 BASE64 분할단위인 vn_base64_max_len로 설정
         vn_divide := vn_base64_max_len;
      END IF;

      -- 루프를 돌며 파일을 BASE64로 변환해 한 쭐씩 찍는다.
      vn_pos := 0;
      WHILE vn_pos < vn_file_len
      LOOP

        -- (파일 전체 크기 - 현재 크기)가 분할 단위보다 크면
        IF (vn_file_len - vn_pos) >= vn_divide then
           vn_divide := vn_divide;
        ELSE -- 그렇지 않으면 분할단위 = (파일전체크기 - 현재크기)
           vn_divide := vn_file_len - vn_pos;
        END IF ;

        -- 파일을 54 단위로 자른다.
        vf_temp_buff := UTL_RAW.SUBSTR ( vf_file_buff, vn_pos, vn_divide);
        -- BASE64 인코딩을 한 후 파일내용 첨부
        UTL_SMTP.WRITE_RAW_DATA(c, UTL_ENCODE.BASE64_ENCODE ( vf_temp_buff));
        UTL_SMTP.WRITE_DATA(c,  UTL_TCP.CRLF );

        -- vn_pos는 vn_base64_max_len 값 단위로 증가
        vn_pos := vn_pos + vn_divide;
      END LOOP;

        -- 맨 마지막 boundary에는 앞과 뒤에 '--'를 반드시 붙여야 한다.
      UTL_SMTP.WRITE_DATA(c, '--' ||  vv_boundary || '--' || UTL_TCP.CRLF );

      UTL_SMTP.CLOSE_DATA(c); -- 메일 본문 작성 종료
      UTL_SMTP.QUIT(c);       -- 메일 세션 종료

    EXCEPTION
      WHEN UTL_SMTP.INVALID_OPERATION THEN
           dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
           dbms_output.put_line(sqlerrm);
           UTL_SMTP.QUIT(c);
      WHEN UTL_SMTP.TRANSIENT_ERROR THEN
           dbms_output.put_line(' Temporary e-mail issue - try again');
           UTL_SMTP.QUIT(c);
      WHEN UTL_SMTP.PERMANENT_ERROR THEN
           dbms_output.put_line(' Permanent Error Encountered.');
           dbms_output.put_line(sqlerrm);
           UTL_SMTP.QUIT(c);
      WHEN OTHERS THEN
         dbms_output.put_line(sqlerrm);
         UTL_SMTP.QUIT(c);
    END;

결과

    익명 블록이 완료되었습니다.


코드가 좀 복잡하지만 성공적으로 실행되었다. 지금까지 설명한 MIME에 대한 추가된 내용과 파일처리에 대해 설명한 내용을 이해했다면 이번 예제코드를 이해할 때 큰 무리는 없을 것이다. 따라서 이번 예제코드 중 앞 부분에서 다루지 않은 내용에 대해서만 추가로 알아 보자.

• vf_file_buff := fn_get_raw_file (vv_directory, vv_filename);
UTL_FILE 패키지를 이용한 fn_get_raw_file 함수를 통해 파일 내용을 RAW 타입으로 반환해 vf_file_buff 변수에 넣고 있다.

• vn_file_len := DBMS_LOB.GETLENGTH(vf_file_buff);
DBMS_LOB 시스템 패키지의 GETLENGTH 함수를 사용해 vf_file_buff 변수의 크기를 알아냈다.

• WHILE vn_pos < vn_file_len LOOP ~ END LOOP;
이진 데이터인 파일을 메일로 보내려면 문자형으로 변환해서 보내야 한다. 이를 위해 BASE64 인코딩을 해야 하는데 이는 UTL_ENCODE.BASE64_ENCODE 함수를 사용해 처리한다. 문제는 한 줄에 넣을 수 있는 최댓값이 76 바이트다. 따라서 76 바이트씩 끊어 입력하기 위해 WHILE문을 사용한 것이다. 그런데 이를 위해 사용한 vn_base64_max_len 변수 값을 54로 설정한 이유는 뭘까? 실제로 데이터를 끊는 부분의 코드는 vf_temp_buff := UTL_RAW.SUBSTR ( vf_file_buff, vn_pos, vn_divide); 이다. UTL_RAW 시스템 패키지의 SUBSTR 함수를 사용하고 있는데, vf_file_buff를 vn_pos부터 vn_divide 까지 잘라내 반환하라는 의미다. vn_divide는 vn_base64_max_len 값과 같이 54이며 결국 데이터를 76이 아닌 54 바이트씩 끊고 있다. 이렇게 하는 이유는 보통 BASE64 인코딩을 하면 인코딩된 데이터는 대략 원본 데이터의 (¾)만큼 커진다. 따라서 76 * (¾) = 54 바이트씩 끊어 이 결과를 BASE64 인코딩한 뒤 입력한 것이다.


그럼 메일이 제대로 전송되어 왔는지 확인해 보자.

▼ 그림 18-10 첨부파일 메일의 본문

[그림 18-10]을 보니 메일 본문은 HTML이 적용되었고 파일이 첨부됐다는 정보도 보인다. 첨부파일의 내용을 확인해 보자.

▼ 그림 18-11 첨부파일 내용 확인

[그림 18-9]에서 봤던 내용과 일치하니 첨부파일 전송도 성공했다.

지금까지 UTL_SMTP 시스템 패키지를 사용해 메일을 전송하는 방법을 살펴봤다. UTL_SMTP 패키지를 사용하는 방법이 그리 쉽지만은 않은데, 익숙하지 않기도 하지만 SMTP와 MIME에 대한 이해가 선행되어야 하기 때문이다. 이 부분에 대해서는 관련 서적이나 자료를 참조해 보길 바란다. 그리고 예제코드가 복잡해 보이긴 하지만 첨부파일을 포함해 UTL_SMTP 패키지로 메일을 보내는 로직을 하나의 패키지로 만들고, MIME 헤더 작성, 첨부파일 작성 등의 각 부분을 함수나 프로시저로 분할해 만들어 놓으면 이후에 유용하게 사용할 수 있을 것이다.


펌 : https://thebook.io/006696/part04/ch18/01/



저작자표시 비영리 변경금지

'Oracle > admin' 카테고리의 다른 글

Mview 마스터테이블 ,Mview Table 컬럼사이즈 check SQL  (0) 2017.03.12
UTL_MAIL  (0) 2017.02.26
Mview log 관리  (0) 2017.02.23
RAC 클러스터 환경을 안정화하기 위한 주요 11가지 방안 (문서 ID 1575936.1)  (0) 2016.12.13
Enabling and Disabling Maintenance Tasks for all Maintenance Windows  (0) 2016.11.17

'Oracle/admin' Related Articles

티스토리툴바