OraFAQ Forum: Server Administration » problem in sending mails through oracle

Home » RDBMS Server » Server Administration » problem in sending mails through oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem in sending mails through oracle [message #227170]

Tue, 27 March 2007 08:16

grk28
Messages: 38
Registered: June 2006

Member

Hi frnds,

I am facing a problem in sending mails thru oracle.I m using the following code and getting the following error.

09:05:18 SQL> create or replace
09:05:31 2 PROCEDURE send_mail (p_sender IN VARCHAR2,
09:05:31 3 p_recipient IN VARCHAR2,
09:05:31 4 p_message IN VARCHAR2)
09:05:31 5 as
09:05:31 6 -- Note that you have to use a host
09:05:31 7 -- that supports SMTP and that you have access to.
09:05:31 8 -- You do not have access to this host and must change it
09:05:31 9 l_mailhost VARCHAR2(255) := 'pop.something.com';
09:05:31 10 l_mail_conn utl_smtp.connection;
09:05:31 11 BEGIN
09:05:31 12 l_mail_conn := utl_smtp.open_connection(l_mailhost,25);
09:05:31 13 utl_smtp.helo(l_mail_conn, l_mailhost);
09:05:31 14 utl_smtp.mail(l_mail_conn, p_sender);
09:05:31 15 utl_smtp.rcpt(l_mail_conn, p_recipient);
09:05:31 16 utl_smtp.open_data(l_mail_conn );
09:05:31 17 utl_smtp.write_data(l_mail_conn, p_message);
09:05:31 18 utl_smtp.close_data(l_mail_conn );
09:05:31 19 utl_smtp.quit(l_mail_conn);
09:05:31 20 end;
09:05:31 21 /

Procedure created.

Elapsed: 00:00:00.03
09:05:31 SQL>
09:05:31 SQL> set serveroutput on
09:05:31 SQL> declare
09:05:31 2 l_start number := dbms_utility.get_time;
09:05:31 3 begin
09:05:31 4 send_mail( 'something@something.com',
09:05:31 5 'something@gmail.com', 'Hello world' );
09:05:31 6 dbms_output.put_line
09:05:31 7 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
09:05:31 8 ' seconds' );
09:05:31 9 end;
09:05:31 10 /
declare
*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 97
ORA-06512: at "SYS.UTL_SMTP", line 139
ORA-06512: at "SCOTT.SEND_MAIL", line 11
ORA-06512: at line 4

I have used all the valid ids(viz mail server,from address,to address) in the code.

I have executed the following scripts before running this procedure.

1) initjvm.sql
2) as i m using oracle 10g i didnt load plsql.jar
3) i couldnt find the initplsj.sql in the specified path.Where can i get this file ?

Is this error because of the 3 point above or something related to network ?

How can we ensure tht SMTP service is available on the machine ?

Immediate help will be appreciated

Regards,
G

Report message to a moderator