메일발송 샘플
CREATE OR REPLACE package body pkg_mail
as
/*******************************************************************************
oracle 에서 메일발송 기능 개발
*******************************************************************************/
procedure send_mail(oMailObj in r_mail_obj)
as
lv_mail_host varchar2(20) := 'xxx.xxx.xxx.xxx';
mail_con utl_tcp.connection;
ln_rtn integer;
ln_cur_cnt integer;
ln_col_cnt number;
oDescTbl dbms_sql.desc_tab;
lv_col_val varchar2(30000);
begin
--메일 처리
dbms_output.put_line('메일발송 시작');
mail_con := utl_tcp.open_connection(lv_mail_host, 25);
ln_rtn := utl_tcp.write_line(mail_con, 'HELO ' || lv_mail_host);
ln_rtn := utl_tcp.write_line(mail_con, 'HELO ' || lv_mail_host);
ln_rtn := utl_tcp.write_line(mail_con, 'MAIL FROM: ' || oMailObj.sender.addr);
for cn_in in 1 .. oMailObj.receiver.count loop
ln_rtn := utl_tcp.write_line(mail_con, 'RCPT TO: ' || oMailObj.receiver(cn_in).addr);
end loop;
for cn_in in 1..oMailObj.cc.count loop
ln_rtn := utl_tcp.write_line(mail_con, 'RCPT TO: ' || oMailObj.cc(cn_in).addr);
end loop;
ln_rtn := UTL_TCP.WRITE_LINE(mail_con, 'DATA');
ln_rtn := utl_tcp.write_line(mail_con, 'Date: ' || to_char(sysdate-(9/24), 'dd Mon yy hh24:mi:ss'));
ln_rtn := utl_tcp.write_line(mail_con, convert('From: ' || oMailObj.sender.name || ' <' || oMailObj.sender.addr || '>', 'KO16KSC5601'));
ln_rtn := utl_tcp.write_line(mail_con, 'MIME-Version: 1.0');
for cn_in in 1..oMailObj.receiver.count loop
ln_rtn := utl_tcp.write_line(mail_con, convert('To: ' || oMailObj.receiver(cn_in).name || ' <' || oMailObj.receiver(cn_in).addr || '>', 'KO16KSC5601'));
end loop;
for cn_in in 1..oMailObj.cc.count loop
ln_rtn := utl_tcp.write_line(mail_con, convert('CC: ' || oMailObj.cc(cn_in).name || ' <' || oMailObj.cc(cn_in).addr || '>', 'KO16KSC5601'));
end loop;
ln_rtn := utl_tcp.write_line(mail_con, 'Subject: ' || convert(oMailObj.subject, 'KO16KSC5601'));
ln_rtn := utl_tcp.write_line(mail_con, 'Content-Type: text/html; charset=euc-kr');
ln_rtn := utl_tcp.write_line(mail_con, 'Content-Transfer-Encoding:8bit');
ln_rtn := utl_tcp.write_line(mail_con, '');
ln_rtn := utl_tcp.write_line(mail_con, '<style type="text/css">');
ln_rtn := utl_tcp.write_line(mail_con, '..table_padding {height: 22px; padding: 2px 2px 1px 3px; background-color: #FFF;font-size:10pt;}');
ln_rtn := utl_tcp.write_line(mail_con, '..table_line_complex {background-color: #D0D0D0;;border-collapse:collapse;}');
ln_rtn := utl_tcp.write_line(mail_con, '..table_header_complex_c {height: 30px; background-color: #E1EDED; text-align: center; font-family: "Noto Sans CJK KR Regular";font-size: 10pt;color:#555;font-weight:bold;padding: 0px 2px 0px 2px;}');
ln_rtn := utl_tcp.write_line(mail_con, 'td {border:1px solid #aaaaaa;}');
ln_rtn := utl_tcp.write_line(mail_con, '</style>');
ln_rtn := utl_tcp.write_line(mail_con, convert(oMailObj.text, 'KO16KSC5601'));
ln_rtn := utl_tcp.write_line(mail_con, '<br><br>');
--조회 처리
if oMailObj.det_data.count > 0 then
ln_cur_cnt := dbms_sql.open_cursor;
dbms_sql.parse(ln_cur_cnt, oMailObj.det_data(1).qry, dbms_sql.native);
DBMS_SQL.DESCRIBE_COLUMNS(ln_cur_cnt, ln_col_cnt, oDescTbl);
for cn_in in 1..ln_col_cnt loop
dbms_sql.define_column(ln_cur_cnt, cn_in, lv_col_val, 30000);
end loop;
ln_rtn := dbms_sql.execute(ln_cur_cnt);
--테이블 생성
--타이틀
ln_rtn := utl_tcp.write_line(mail_con, '<table border="1" cellspacing="1" cellpadding="0" class="table_line_complex">');
ln_rtn := utl_tcp.write_line(mail_con, '<tr>');
for cn_in in 1..ln_col_cnt loop
ln_rtn := utl_tcp.write_line(mail_con, '<td class="table_header_complex_c" align="center" nowrap>' || oDescTbl(cn_in).col_name || '</td>');
end loop;
ln_rtn := utl_tcp.write_line(mail_con, '</tr>');
--내용
while(dbms_sql.fetch_rows(ln_cur_cnt) > 0) loop
ln_rtn := utl_tcp.write_line(mail_con, '<tr>');
for cn_in in 1..ln_col_cnt loop
dbms_sql.column_value(ln_cur_cnt, cn_in, lv_col_val);
ln_rtn := utl_tcp.write_line(mail_con, convert('<td>' || lv_col_val || '</td>', 'KO16KSC5601'));
end loop;
ln_rtn := utl_tcp.write_line(mail_con, '</tr>');
end loop;
DBMS_SQL.CLOSE_CURSOR(ln_cur_cnt);
ln_rtn := utl_tcp.write_line(mail_con, '</table>');
end if;
ln_rtn := utl_tcp.write_line(mail_con, '');
ln_rtn := utl_tcp.write_line(mail_con, '.');
ln_rtn := utl_tcp.write_line(mail_con, 'QUIT');
utl_tcp.close_connection(mail_con);
dbms_output.put_line('메일발송 완료');
exception
when others then
dbms_output.put_line('메일발송 실패');
if DBMS_SQL.IS_OPEN(ln_cur_cnt) then
DBMS_SQL.CLOSE_CURSOR(ln_cur_cnt);
end if;
utl_tcp.close_connection(mail_con);
end;
procedure test_mail
as
oMailObj r_mail_obj;
begin
--송신자
oMailObj.sender.name := 'LSK_SYSTEM';
oMailObj.sender.addr := 'LSK_SYSTEM';
--수신자
oMailObj.receiver(1).name := '이승규';
oMailObj.receiver(1).addr := 'lsk@lsk.com';
--CC
oMailObj.cc(1).name := '이승규';
oMailObj.cc(1).addr := 'lsk@lsk.com';
--Data
oMailObj.det_data(1).table_nm := 'ifup_wo_im_result';
oMailObj.det_data(1).qry := 'select ''이승규'' as user_nm, ''lsk'' as user_id, ''lsk@lsk.com'' as user_em from dual';
--제목
oMailObj.subject := '테스트 메일 입니다.';
--내용
oMailObj.text := '테스트 내용 입니다.';
pkg_mail.send_mail(oMailObj);
end;
end;
/