오라클을 이용한 정기적인 메일 전송

오라클을 이용하여 정기적인 메일 전송 기능이 필요하여 간단히 만들어본 내용입니다.

​

참고하시기 바랍니다.

​

시나리오

• 고객별 전일의 업데이트 내역이 있을 경우 당일 새벽시간을 이용하여 메일 전송

​

필요한 내용

#1 조회 및 mail 전송용 Procedure

- 전일 변경 사항을 조회하여 통보할 procedure

- email정보가 포함되어 있는 내용

​

****SAMPLE

​

create or replace PROCEDURE SP_CHANGE_NOTICE IS

​

w_customer cust.customer%type;

w_email custemail.email%type;

w_return varchar2(255);

​

crlf varchar2(2):= CHR(13)||CHR(10);

mesg varchar2(4000);

mailhost VARCHAR2(100) := 'smtp.abcdefg.co.kr';

​

mail_conn utl_smtp.connection;

​

CURSOR change_cur IS

select a.customer,b.email

from cust a, custemail b

where a.cust_code = b.cust_code(+)

and a.change_date = sysdate - 1

order by a.customer ;

​

open change_cur;

​

loop

fetch change_cur into w_customer,w_email;

exit when change_cur ;

​

if nvl(w_email,'N') = 'N' then

--skip email sending

else

​

mail_conn :=utl_smtp.open_connection(mailhost,25);

utl_smtp.helo(mail_conn,mailhost);

utl_smtp.mail(mail_conn,'master@abcdefg.co.kr');

utl_smtp.rcpt(mail_conn,w_email);

utl_smtp.rcpt(mail_conn,w_email);

utl_smtp.open_data(mail_conn);

​

utl_smtp.write_data(mail_conn,'FROM:'||'master@abcdefg.co.kr'||crlf);

utl_smtp.write_data(mail_conn,'TO:'||w_email||crlf);

utl_smtp.write_data(mail_conn,'CC:'||w_email||crlf);

​

utl_smtp.write_data(mail_conn,'MIME-version: 1.0' || crlf);

utl_smtp.write_data(mail_conn,'Content-Type: text/html;charset=euc-kr'||crlf);

utl_smtp.write_data(mail_conn,'Content-Transfer-Encoding:8bit '||crlf);

utl_smtp.write_raw_data(mail_conn, utl_raw.cast_to_raw('Subject:'||w_customer||'님 확인요망'||crlf));

utl_smtp.write_data(mail_conn,crlf);

utl_smtp.write_raw_data(mail_conn,utl_raw.cast_to_raw(to_char(sysdate - 1)||' 업데이트 내역 존재') );

utl_smtp.write_data(mail_conn,crlf);

​

utl_smtp.close_data(mail_conn);

utl_smtp.quit(mail_conn);

​

dbms_output.put_line(' 전송OK' );

end if;

end loop;

​

----------------------------------

EXCEPTION

----------------------------------

WHEN UTL_SMTP.INVALID_OPERATION THEN

dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');

vreturn := SQLERRM;

​

WHEN UTL_SMTP.TRANSIENT_ERROR THEN

dbms_output.put_line(' Temporary e-mail issue - try again');

vreturn := SQLERRM;

​

WHEN UTL_SMTP.PERMANENT_ERROR THEN

dbms_output.put_line(' Permanent Error Encountered.');

vreturn := SQLERRM;

​

END ;

​

​

#2 스케쥴로 등록

--아래와 같이 스크립트를 이용하여도 되고 sql developer에서 직접 생성해도 됨

--아래 내용은 매일 새벽 5시에 전에 해당 procedure를 호출하게 됨.

--아래 처럼 생성하게되면 임의의 job number가 생성됨.

--DBMS_JOB은 다른 블로그에서도 쉽게 확인 하실 수 있습니다.

​

declare

x number;

begin

sys.dbms_job.submit

(job => X,

what => 'SP_CHANGE_NOTICE;',

next_date => to_date('20230729 05:00:00','yyyymmdd hh24:mi:ss'),

interval => 'trunc(sysdate+1)+5/24',

no_parse => TRUE);

sys.dbms_output.put_line('job number = '||to_char(x));

end;

/

commit;

​

--인터벌등의 조정이 필요하면 명령이나 sql developer에서 수정 가능

​

♥도움 되셨다면 ♥ 공감 부탁드려요~~