How to send mail using PLSQL

[vaira muthu]

I tried sending mail using the inbuilt packages available in PLSQL like UTL_SMTP and UTL_mail. But using both the packages I m not able to send any mail, Can anyone please explain how to send mail from PLSQL directly??

Thanks in advance...

 

[DoDo]

This works form me. Simple for text e-mails:

CREATE OR REPLACE Procedure Send_mail(in_receiver Varchar2, in_subject In Varchar2, in_text In Varchar2) Is
-- Author : DoDo
c utl_smtp.connection;

Procedure Send_Header(Name In Varchar2, Header In Varchar2) As
Begin
Utl_Smtp.Write_Data(c, Name || ': ' || Header || Utl_Tcp.Crlf);
End;
Begin
Utl_Tcp.CRLF := chr(13)||Chr(10);
c := Utl_Smtp.Open_Connection('000.000.000.000'); -- IP ADDRESS OF MAIL SERVER
Utl_Smtp.Helo(c, 'acme.com');
Utl_Smtp.Mail(c, 'acme@acme.com'); -- From
Utl_Smtp.Rcpt(c, in_receiver);
Utl_Smtp.Open_Data(c);
Send_Header('From', '"OracleServer" ');
Send_Header('To', '');
Send_Header('Subject', in_subject);
Utl_Smtp.Write_Data(c, Utl_Tcp.Crlf || in_text);
Utl_Smtp.Close_Data(c);
Utl_Smtp.Quit(c);
Exception When Utl_Smtp.Transient_Error Or Utl_Smtp.Permanent_Error
Then Utl_Smtp.Quit(c); Raise_Application_Error(-20000, 'Failed to send mail due to the following error: ' || Sqlerrm);
End;

 

[vaira muthu]

Hi boss,
I m getting ora 29278 SMTP transient error 421 service not available.

 

[DoDo]

The ORA-29278 error may signify that, the same server where you have your Oracle database running, does not have an SMTP server up and running.

Is there SMTP server?

Have you put correct IP addres instead of 000.000.000.000 ?

Firewall blocking port 25?

Try this:http://www.ora00600.com/scripts/databaseconfig/ORA-29278.html

 

[vaira muthu]

I have given the system's IP as the SMTP server address, and I dont know how to find the SMTP server name and port, using show parameters I gave the system ip as smtp server ip and the port as 1521 which is used by my oracle database to the smtp_out_server. Can you please help me how to resolve my problem.....and thanks a lot for the response