Chủ Nhật, 6 tháng 9, 2015

Send Email in Oracle using Gmail

1- We have to install this small software that wil use for as a small exchange server for GMAIL, it is third party tool but very usefull tool for gmail. It is STUNNEL
2- After installing this software got to the installed location like C:\Program Files\stunnel and open this file in notepad “stunnel.conf” and set this configuration.
[SSMTP]
CLIENT = YES
ACCEPT  = 1925
CONNECT = SMTP.GMAIL.COM:465
3- Start stunnel application (stunnel.exe in C:\Program Files\stunnel)

4- Check that Gmail is Pinging with this command in MS Dos
telnet localhost 1925
ping smtp.gmail.com
5- Create a package in Oracle Database

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
CREATE OR REPLACE PACKAGE email_gmail
IS
    g_smtp_host            VARCHAR2 (256) := 'localhost';
    g_smtp_port            PLS_INTEGER := 1925;
    g_smtp_domain          VARCHAR2 (256) := 'gmail.com';
    g_mailer_id   CONSTANT VARCHAR2 (256) := 'Mailer by Oracle UTL_SMTP';

    -- SEND MAIL USING UTL_SMTP
    PROCEDURE send (p_sender      IN VARCHAR2,
                    p_recipient   IN VARCHAR2,
                    p_subject     IN VARCHAR2,
                    p_message     IN VARCHAR2);
END;
/

-- Package Body

CREATE OR REPLACE PACKAGE BODY email_gmail
IS
    --  write A MIME HEADER
    PROCEDURE write_mime_header (p_conn    IN OUT NOCOPY UTL_SMTP.connection,
                                 p_name    IN            VARCHAR2,
                                 p_value   IN            VARCHAR2)
    IS
    BEGIN
        UTL_SMTP.write_data (p_conn,
                             p_name || ': ' || p_value || UTL_TCP.crlf);
    END;

    PROCEDURE send (p_sender      IN VARCHAR2,
                    p_recipient   IN VARCHAR2,
                    p_subject     IN VARCHAR2,
                    p_message     IN VARCHAR2)
    IS
        l_conn        UTL_SMTP.connection;
        nls_charset   VARCHAR2 (255);
    BEGIN
        -- GET CHARACTERSET
        SELECT VALUE
          INTO nls_charset
          FROM nls_database_parameters
         WHERE parameter = 'NLS_CHARACTERSET';

        -- ESTABLISH CONNECTION AND AUTHETICATE
        l_conn := UTL_SMTP.open_connection (g_smtp_host, g_smtp_port);
        UTL_SMTP.ehlo (l_conn, g_smtp_domain);
        UTL_SMTP.command (l_conn, 'auth login');
        UTL_SMTP.command (
            l_conn,
            UTL_ENCODE.text_encode ('your_gmail_account@gmail.com',
                                    nls_charset,
                                    1));
        UTL_SMTP.command (
            l_conn,
            UTL_ENCODE.text_encode ('your_password', nls_charset, 1));
        -- SET FROM/RECIPIENT
        UTL_SMTP.command (l_conn, 'MAIL FROM: <' || p_sender || '>');
        UTL_SMTP.command (l_conn, 'RCPT TO: <' || p_recipient || '>');
        --WRITE MIME HEADERS
        UTL_SMTP.open_data (l_conn);
        write_mime_header (l_conn, 'From', p_sender);
        write_mime_header (l_conn, 'To', p_recipient);
        write_mime_header (l_conn, 'Subject', p_subject);
        write_mime_header (l_conn, 'Content-Type', 'text/plain');
        write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
        UTL_SMTP.write_data (l_conn, UTL_TCP.crlf);
        -- WRITE MESSAGE BODY
        UTL_SMTP.write_data (l_conn, p_message);
        UTL_SMTP.close_data (l_conn);
        -- END CONNECTION
        UTL_SMTP.quit (l_conn);
    EXCEPTION
        WHEN OTHERS
        THEN
            BEGIN
                UTL_SMTP.quit (l_conn);
            EXCEPTION
                WHEN OTHERS
                THEN
                    NULL;
            END;

            raise_application_error (
                -20000,
                'Failed to send mail due to the following error: ' || SQLERRM);
    END;
END;

Remember changing your own info of 2 lines below:

1
2
UTL_ENCODE.text_encode ('your_gmail_accoount@gmail.com', nls_charset, 1));
UTL_SMTP.command (l_conn, UTL_ENCODE.text_encode ('your_password', nls_charset, 1));

6- Create a new ACL (Access Control List) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'acl_for_gmail.xml', 
    description  => 'ACL functionality',
    principal    => 'YOUR_SCHEMA',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;

Change line 5 with your schema name.

7- Add a new access point







1
2
3
4
5
6
7
8
9


BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl => 'acl_for_gmail.xml',
    host => 'localhost', 
    lower_port => 1925,
    upper_port => 1925); 
END;

COMMIT;














8- Run this statement in oracle database.






1


EXEC EMAIL_GMAIL.SEND ('sender@gmail.com','recepient@gmail.com','Send mail from Oracle','Email sent from Oracle Database');










When you run this then an email will send to that address and you can also check that sending email in your gmail in send items.



Also you can check what is the status of sending email in “stunnel.exe” application.













Người đăng:





vào lúc




















Nhãn:

















Không có nhận xét nào:















Đăng nhận xét


















        

      









Đăng ký:
Đăng Nhận xét (Atom)