티스토리 뷰

sql

오라클 JOB 을 이용한 자동 메일 발송

개몽구리 2019. 5. 8. 16:42

오라클 job을 통한 자동 메일 발송 (일반적으론 Spring Batch를 활용하여 웹 서버를 통해 일괄 처리가 가능)

 

step 1) 메일 발송 프로시저 생성 

CREATE OR REPLACE PROCEDURE 프로시저명
IS
PG_CONNECTION UTL_SMTP.CONNECTION;
PG_REPLY UTL_SMTP.REPLY;
PG_REPLIES UTL_SMTP.REPLIES;
MAIL_HOST VARCHAR2(30) := '' ; --메일서버주소
MAIL_PORT NUMBER(2) := 25 ; --메일서버포트
SEND_MAILAD VARCHAR2(100) := 'test@naver.com'; --발신자 메일
SEND_NM VARCHAR2(100) := '발신자명'; --발신자명
RECV_MAILAD VARCHAR2(100) := NULL; --수신자 메일
MAIL_TITLE VARCHAR2(500) := NULL; --메일 제목
MAIL_CONT CLOB := NULL; --메일 내용
YEAR VARCHAR2(4) := NULL; --해당년도
MONTH VARCHAR2(2) := NULL; --해당 월
LASTDAY VARCHAR2(2) := NULL; --해당 월 마지막 일자
BEGIN
-- 해당 년 월 마지막 일자 추출 (생략 가능)
EXECUTE IMMEDIATE
'SELECT
TO_CHAR(SYSDATE,''YYYY''), --해당년도
TO_CHAR(SYSDATE,''MM''), --해당 월
TO_CHAR(LAST_DAY(SYSDATE),''DD'')
FROM DUAL'
INTO YEAR,MONTH,LASTDAY;
--메일 제목 지정
MAIL_TITLE := YEAR||'년'||MM||'월'||LAST_DAY||'일';
-- LOOP 시작
FOR MAIL_LIST IN (
SELECT
'test1@naver.com' AS MAILAD
,'test2@naver.com' AS MAILAD
FROM
DUAL
)
LOOP
MAIL_CONT := TO_CLOB(
'<div>이곳에 컨텐츠 입력</div>'
);
--IF 조건 메일 리스트 에서 MAILAD가 있을 경우 실행
IF MAIL_LIST.MAILAD IS NOT NULL THEN
RECV_MAILAD := send_list.MAILAD; --메일 주소 세팅
PG_REPLY := UTL_SMTP.OPEN_CONNECTION(MAIL_HOST, LN_MAIL_PORT, PG_CONNECTION);
PG_REPLIES := UTL_SMTP.HELP(PG_CONNECTION, 'HELP');
PG_REPLIES := UTL_SMTP.EHLO(PG_CONNECTION, MAIL_HOST);
PG_REPLY := UTL_SMTP.HELO(PG_CONNECTION, MAIL_HOST); -- HELO
PG_REPLY := UTL_SMTP.MAIL(PG_CONNECTION, SEND_MAILAD); --보내는사람
PG_REPLY := UTL_SMTP.RCPT(PG_CONNECTION, RECV_MAILAD); --받는사람
PG_REPLY := UTL_SMTP.OPEN_DATA(PG_CONNECTION); -- 메일본문 작성 시작
UTL_SMTP.WRITE_RAW_DATA(PG_CONNECTION, UTL_RAW.CAST_TO_RAW('FROM: ' || SEND_NM || ' <' || SEND_MAILAD || '>' || UTL_TCP.CRLF));
UTL_SMTP.WRITE_RAW_DATA(PG_CONNECTION, UTL_RAW.CAST_TO_RAW('TO: ' || RECV_ALL || UTL_TCP.CRLF));
UTL_SMTP.WRITE_RAW_DATA(PG_CONNECTION, UTL_RAW.CAST_TO_RAW('MIME-VERSION: 1.0' || UTL_TCP.CRLF)); -- MIME 버전
UTL_SMTP.WRITE_RAW_DATA(PG_CONNECTION, UTL_RAW.CAST_TO_RAW('CONTENT-TYPE: TEXT/HTML; CHARSET=EUC-KR' || UTL_TCP.CRLF));
-- Content-Type: HTML 형식, 한글을 사용하므로 문자셋은 euc-kr
UTL_SMTP.WRITE_RAW_DATA(PG_CONNECTION, UTL_RAW.CAST_TO_RAW('CONTENT-TRANSFER-ENCODING: 8BIT' || UTL_TCP.CRLF));
-- Content-Transfer-Encoding 필드가 생략되어 있으면 7bit 방식 || 한 줄 띄우기
UTL_SMTP.WRITE_RAW_DATA(PG_CONNECTION, UTL_RAW.CAST_TO_RAW('SUBJECT: ' || MAIL_TITLE || UTL_TCP.CRLF)); --메일 제목
UTL_SMTP.WRITE_RAW_DATA(PG_CONNECTION, UTL_RAW.CAST_TO_RAW(UTL_TCP.CRLF));
UTL_SMTP.WRITE_RAW_DATA(PG_CONNECTION, UTL_RAW.CAST_TO_RAW(MAIL_CONT)); --메일 내용
UTL_SMTP.WRITE_RAW_DATA(PG_CONNECTION, UTL_RAW.CAST_TO_RAW(UTL_TCP.CRLF));
PG_REPLY := UTL_SMTP.CLOSE_DATA(PG_CONNECTION); -- 메일 본문 작성 종료
PG_REPLY := UTL_SMTP.QUIT(PG_CONNECTION); -- 메일 세션 종료
END IF; -- if 조건 종료
END LOOP; -- 루프 종료
<>
NULL;
EXCEPTION
WHEN OTHERS THEN
--에러 확인 (주석제거)
--DBMS_OUTPUT.PUT_LINE('ERR CODE : ' || TO_CHAR(SQLCODE));
--DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
NULL;
COMMIT;
END;
/

 

step2) 오라클 잡 생성 

DECLARE
X NUMBER;
user_name varchar2(30);
BEGIN
select user into user_name from dual;
execute immediate 'alter session set current_schema = 스키마명';
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => '실행할 프로시저 명'
,next_date => ADD_MONTHS(TRUNC(SYSDATE,'MM'),1) -- job의 다음 수행시간
,interval => 'ADD_MONTHS(TRUNC(SYSDATE,''MM''),1)' -- job의 실행 cycle 지정
,no_parse => FALSE -- 기본값
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); -- job큐의 번호
execute immediate 'alter session set current_schema = ' || user_name ;
EXCEPTION
WHEN OTHERS THEN
execute immediate 'alter session set current_schema = ' || user_name ;
RAISE;
END;
COMMIT;
END;
/

 

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

 

오라클 SQL과 PL/SQL을 다루는 기술: 18장. 프로시저를 통한 이메일 전송

 

thebook.io

 

'sql' 카테고리의 다른 글

REGEXP_SUBSTR  (0) 2019.01.30
oracle 락 확인  (0) 2019.01.23
Oracle Date Format  (0) 2019.01.23
sysdba 접속 불가  (0) 2019.01.23
자원 대기중 교착 상태 검출 에러  (0) 2019.01.23
공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
링크
«   2025/07   »
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
글 보관함