All,
I am on Oracle Database 11g Express Edition Release 11.2.0.2.0
Could anyone please help me on this. I am trying to get a sample code to send an email from DB to gmail/yahoo ( any mail client).
I have followed the instructions as said in the Send mail from PL/SQL - Oracle FAQ. But not able to send an email to gmail.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> @ D:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\prvtmail.plb
Package created.
Package body created.
Grant succeeded.
Package body created.
No errors.
SQL> @ D:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\utlmail.sql
Package created.
Synonym created.
SQL> select name, value from v$parameter where name like '%smtp%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
smtp_out_server
SQL> alter session set smtp_out_server='127.0.0.1';
Session altered.
SQL> select name, value from v$parameter where name like '%smtp%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
smtp_out_server
127.0.0.1
SQL> BEGIN
2 --EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
3 UTL_MAIL.send(sender => 'infochandu12@gmail.com',
4 recipients => 'infochandu12@gmail.com',
5 subject => 'Test Mail',
6 message => 'Hello World',
7 mime_type => 'text; charset=us-ascii');
8 END;
9 /
BEGIN
*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 3
SQL>
Also i have tried another way as the below, but the same no result. Could anyone please help me out to send an email from DB to gmail / yahoomail. And also please tell me where can i find the settings for SMTP of gmail / ymail.
SQL> DECLARE
2 v_c UTL_SMTP.connection;
3 v_mailserver VARCHAR2(40);
4 v_from VARCHAR2 (40);
5 v_to VARCHAR2 (40);
6 v_cc VARCHAR2 (40);
7 v_subject VARCHAR2 (40);
8 v_resp UTL_SMTP.reply;
9 BEGIN
10 v_mailserver:='smtp.gmail.com';
11 v_from :='infochandu12@gmail.com';
12 v_to :='infochandu12@gmail.com';
13 v_cc :='infochandu12@gmail.com';
14 v_subject :='TEST MAIL';
15 v_resp :=UTL_SMTP.open_connection (v_mailserver, c=>v_c);
16 v_resp :=UTL_SMTP.helo (v_c, 'helo_text');
17 v_resp :=UTL_SMTP.mail (v_c, v_to);
18 v_resp :=UTL_SMTP.rcpt(v_c, v_from);
19 v_resp :=UTL_SMTP.open_data(v_c);
20 UTL_SMTP.write_data(v_c, 'From: ' || v_from || UTL_TCP.crlf);
21 UTL_SMTP.write_data (v_c,'To: ' || v_to || UTL_TCP.crlf);
22 UTL_SMTP.write_data (v_c,'Cc: ' || v_cc || UTL_TCP.crlf);
23 UTL_SMTP.write_data (v_c,'Subject: ' || v_subject || UTL_TCP.crlf);
24 UTL_SMTP.write_data (v_c, 'Test Body');
25 v_resp:=UTL_SMTP.close_data(v_c);
26 UTL_SMTP.quit (v_c);
27 END;
28 /
DECLARE
*
ERROR at line 1:
ORA-29279: SMTP permanent error: 502 5.5.1 Unrecognized command.
jh13sm1540092bkb.13 - gsmtp
ORA-06512: at "SYS.UTL_SMTP", line 54
ORA-06512: at "SYS.UTL_SMTP", line 140
ORA-06512: at "SYS.UTL_SMTP", line 699
ORA-06512: at line 26
Also, is it possible to send the email from db to an email id which is on microsoft exchange server. if so, what are setting needed.
Thank you all.