Oracle database üzerinden mail gönderme

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.