Database üzerinden mail göndermek için öncelikle ilgili package in create edilmesi gerekir.
CREATE OR REPLACE PACKAGE Unitec_Mail_API IS
TYPE ARRAY IS TABLE OF VARCHAR2(255);
PROCEDURE send(p_sender_email IN VARCHAR2,
p_from IN VARCHAR2,
p_to IN ARRAY DEFAULT ARRAY(),
p_cc IN ARRAY DEFAULT ARRAY(),
p_subject IN VARCHAR2,
p_body IN LONG);
END Unitec_Mail_API;
CREATE OR REPLACE PACKAGE BODY Unitec_Mail_API IS
g_crlf CHAR(2) DEFAULT chr(13) || chr(10);
g_mail_conn utl_smtp.connection;
g_mailhost VARCHAR2(255) := 'smtp server ip si'; --smtp server
FUNCTION address_email(p_string IN VARCHAR2, p_recipients IN ARRAY)
RETURN VARCHAR2 IS
l_recipients LONG;
BEGIN
FOR i IN 1 .. p_recipients.COUNT LOOP
utl_smtp.rcpt(g_mail_conn, p_recipients(i));
IF (l_recipients IS NULL) THEN
l_recipients := p_string || p_recipients(i);
ELSE
l_recipients := l_recipients || ', ' || p_recipients(i);
END IF;
END LOOP;
RETURN l_recipients;
END;
PROCEDURE send(p_sender_email IN VARCHAR2,
p_from IN VARCHAR2,
p_to IN ARRAY DEFAULT ARRAY(),
p_cc IN ARRAY DEFAULT ARRAY(),
p_subject IN VARCHAR2,
p_body IN LONG) IS
l_to_list LONG;
l_cc_list LONG;
l_date VARCHAR2(255) DEFAULT to_char(SYSDATE,'dd MON yy hh24:mi:ss');
PROCEDURE writeData(p_text IN VARCHAR2) IS
BEGIN
IF (p_text IS NOT NULL) THEN
utl_smtp.write_data(g_mail_conn, p_text || g_crlf);
END IF;
END;
BEGIN
g_mail_conn := utl_smtp.open_connection(g_mailhost, 25); --get conn. to smtp server
utl_smtp.helo(g_mail_conn, g_mailhost);
utl_smtp.mail(g_mail_conn, p_sender_email);
l_to_list := address_email('To: ', p_to);
l_cc_list := address_email('Cc: ', p_cc);
utl_smtp.open_data(g_mail_conn);
writeData('Date: ' || l_date);
dbms_output.put_line(l_date);
writeData('From: ' || nvl(p_from, p_sender_email));
writeData('Subject: ' || nvl(p_subject, '(no subject)'));
writeData(l_to_list);
writeData(l_cc_list);
utl_smtp.write_data(g_mail_conn, '' || g_crlf);
utl_smtp.write_data(g_mail_conn, p_body);
utl_smtp.close_data(g_mail_conn);
utl_smtp.quit(g_mail_conn);
END send;
END Unitec_Mail_API;
Şimdi sıra test de
begin
Unitec_Mail_API.send(
'test@test.com',
'test@test.com',Unitec_Mail_API.array(
'test@test.com'),Unitec_Mail_API.array(
'test@test.com'),
'This is a subject',
'Hello, this is the mail you need' );
end;
Bu aşamadan sonra database trigger lar ile otomatik mail kurgusunu oluşturabilirsiniz.
