February 15th, 2008, 12:07 PM
|
 | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,485
| |
The follwing sample code uses predefined package UTL_SMTP to send mail.
This package was first available in the version 8.1.7 . -
create or replace procedure sendmail(sender varchar2,recipient varchar2,subject varchar2, text
-
-
varchar2)
-
IS
-
mailhost VARCHAR2(64) := '192.168.1.32';
-
--The name of the SMTP server host
-
port constant number(2):=25;
-
--The port number on which SMTP server is listening (usually 25).
-
timeout number :=180;
-
--The time in seconds that the UTL_SMTP package waits before giving up in a read or write
-
-
operation in this connection.
-
--In read operations, this package gives up if no data is available for reading immediately.
-
--In write operations, this package gives up if the output buffer is full and no data is to be
-
-
sent into the network without being blocked.
-
--Zero (0) indicates not to wait at all.
-
--NULL indicates to wait forever.
-
mail_conn utl_smtp.connection;
-
BEGIN
-
--dbms_output.put_line(UTL_SMTP.VRFY (mail_conn,recipient));
-
mail_conn := utl_smtp.open_connection(mailhost, port,timeout);
-
--Helo performs initial handshaking with SMTP server after connecting
-
utl_smtp.helo(mail_conn, mailhost);
-
--Mail Initiates a mail transaction with the server
-
utl_smtp.mail(mail_conn, sender);
-
--Specifies the recipient of an e-mail message
-
utl_smtp.rcpt(mail_conn, recipient);
-
-- open_data(), write_data(), and close_data() into a single call to data().
-
--Sends the DATA command
-
utl_smtp.open_data(mail_conn);
-
utl_smtp.write_data(mail_conn,'From'||':'|| Sender || UTL_TCP.CRLF);
-
utl_smtp.write_data(mail_conn,'To'||':'|| recipient || UTL_TCP.CRLF);
-
utl_smtp.write_data(mail_conn,'Subject' ||':'|| subject || UTL_TCP.CRLF);
-
--Writes a portion of the e-mail message
-
utl_smtp.write_data(mail_conn, text);
-
--Closes the data session
-
utl_smtp.close_data(mail_conn);
-
utl_smtp.quit(mail_conn);
-
--dbms_output.put_line('Your message has been sent...!');
-
EXCEPTION
-
WHEN UTL_SMTP.PERMANENT_ERROR THEN
-
BEGIN
-
utl_smtp.quit(mail_conn);
-
END;
-
RAISE_APPLICATION_ERROR(-20101,'This id has Permanent Error');
-
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
-
BEGIN
-
utl_smtp.quit(mail_conn);
-
END;
-
RAISE_APPLICATION_ERROR(-20102,'SMTP transient error:');
-
WHEN UTL_SMTP.INVALID_OPERATION THEN
-
BEGIN
-
utl_smtp.quit(mail_conn);
-
END;
-
RAISE_APPLICATION_ERROR(-20103,'Invalid Operation in Mail using UTL_SMTP.');
-
WHEN OTHERS THEN
-
RAISE_APPLICATION_ERROR(-20104,'Some other Error ...!');
-
end;
-
/
-
-
To execute the above procedure try the following code. -
exec sendmail('sender@sender.com','recipient@recipient.com','Hi','Test Mail');
-
|
