[ 쿼리 내용을 CSV로 첨부파일 만들어서 이메일 보내기 ]
CREATE OR REPLACE PROCEDURE PROC_MAGAM_EMAIL_SEND(P_WORK_DATE VARCHAR2,
P_TO_EMAIL VARCHAR2)
IS
BEGIN
DECLARE
i NUMBER := 1;
p_to VARCHAR2 (100) := P_TO_EMAIL; -- 받는사람
lv_smtp_server VARCHAR2 (100) := 'mail.xx.co.kr'; -- 메일서버주소
lv_from VARCHAR2 (100) := 'ppp@xx.co.kr'; -- 보내는사람
v_connection UTL_SMTP.connection;
c_mime_boundary CONSTANT VARCHAR2 (256) := '--AAAAA000956--';
v_clob CLOB;
ln_counter NUMBER := 0;
ln_cnt NUMBER;
ln_rowcnt NUMBER;
ld_date DATE;
BEGIN
ld_date := SYSDATE;
BEGIN
v_clob := '브랜드' || ',' || '매장'|| ',' || '명'|| ',' || '차수'|| ',' || '이월'|| ',' || '잔'|| UTL_TCP.crlf; -- 칼럼명
v_connection := UTL_SMTP.open_connection (lv_smtp_server); --To open the connection
UTL_SMTP.helo(v_connection, 'xx.co.kr'); -- 도메인주소
UTL_SMTP.mail(v_connection, lv_from);
UTL_SMTP.rcpt(v_connection, p_to); -- To send mail to valid receipent
UTL_SMTP.open_data (v_connection);
UTL_SMTP.write_data (v_connection, 'From: ' || lv_from || UTL_TCP.crlf);
IF TRIM(p_to) IS NOT NULL
THEN
UTL_SMTP.write_data (v_connection, 'To: ' || p_to || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data(v_connection,'Subject: 전월이월('||P_WORK_DATE||')차이내역' || UTL_TCP.crlf); -- 제목
UTL_SMTP.write_data(v_connection,'MIME-Version: 1.0' || UTL_TCP.crlf); -- 형식(수정 안함)
UTL_SMTP.write_data(v_connection,'Content-Type: multipart/mixed; boundary="'||c_mime_boundary||'"'|| UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,'This is a multi-part message in MIME format.'|| UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,'--' || c_mime_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection,'Content-Type: text/plain'||UTL_TCP.crlf);
ln_cnt := 1;
/*Condition to check for the creation of csv attachment*/
IF (ln_cnt <> 0)
THEN
UTL_SMTP.write_data(v_connection,
'Content-Disposition: attachment; filename="' -- 파일이름
|| 'DETAILS'
|| TO_CHAR (ld_date, 'dd-mon-rrrr hh:mi')
|| '.csv'
|| '"'
|| UTL_TCP.crlf
);
END IF;
UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
FOR i IN ( SELECT BRAND_CODE,
PART_CODE,
PART_NAME,
BILL_YM,
IWOL,
REMAIN
FROM TEMP ... -- 쿼리
)
LOOP
ln_counter := ln_counter + 1;
IF ln_counter = 1
THEN
UTL_SMTP.write_data (v_connection, v_clob); --To avoid repeation of column heading in csv file
END IF;
BEGIN
v_clob := '="' || i.BRAND_CODE || '"' || ',"' || i.PART_CODE || '"'|| ',"' || i.PART_NAME || '"'||',"' || i.BILL_YM || '"'||',"' ||
i.IWOL || '"'|| ',"' || i.REMAIN || '"' || UTL_TCP.crlf; -- 컬럼에 맞춰서 데이터 삽입
EXCEPTION
WHEN OTHERS THEN
null;
END;
UTL_SMTP.write_data (v_connection, v_clob); --Writing data in csv attachment.
ln_rowcnt := ln_rowcnt + 1;
END LOOP;
if ln_counter > 0 then
UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
UTL_SMTP.close_data (v_connection);
UTL_SMTP.quit (v_connection);
else
DBMS_OUTPUT.put_line ('내역없슴');
end if;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END;
END PROC_MAGAM_EMAIL_SEND;