Home » RDBMS Server » Server Administration » problem in sending mails through oracle
problem in sending mails through oracle [message #227170] Tue, 27 March 2007 08:16 Go to next message
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
Re: problem in sending mails through oracle [message #227178 is a reply to message #227170] Tue, 27 March 2007 08:23 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have a look -> here

Also -> demo
Previous Topic: Oracle 9i & 10g on Windows
Next Topic: Value of parameter from V$parameter
Goto Forum:
  


Current Time: Thu Dec 08 05:57:55 CST 2016

Total time taken to generate the page: 0.20126 seconds