By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,276 Members | 2,137 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Sending mail using UTL_SMTP.

debasisdas
Expert 5K+
P: 8,127
The follwing sample code uses predefined package UTL_SMTP to send mail.

This package was first available in the version 8.1.7 .

Expand|Select|Wrap|Line Numbers
  1. create or replace procedure sendmail(sender varchar2,recipient varchar2,subject varchar2, text 
  2.  
  3. varchar2)
  4. IS
  5. mailhost    VARCHAR2(64) := '192.168.1.32';
  6. --The name of the SMTP server host
  7. port constant number(2):=25;
  8. --The port number on which SMTP server is listening (usually 25).
  9. timeout number :=180;
  10. --The time in seconds that the UTL_SMTP package waits before giving up in a read or write 
  11.  
  12. operation in this connection. 
  13. --In read operations, this package gives up if no data is available for reading immediately. 
  14. --In write operations, this package gives up if the output buffer is full and no data is to be 
  15.  
  16. sent into the network without being blocked. 
  17. --Zero (0) indicates not to wait at all. 
  18. --NULL indicates to wait forever.
  19. mail_conn  utl_smtp.connection;
  20. BEGIN
  21. --dbms_output.put_line(UTL_SMTP.VRFY (mail_conn,recipient));
  22. mail_conn := utl_smtp.open_connection(mailhost, port,timeout);
  23. --Helo performs initial handshaking with SMTP server after connecting
  24. utl_smtp.helo(mail_conn, mailhost);
  25. --Mail Initiates a mail transaction with the server
  26. utl_smtp.mail(mail_conn, sender);
  27. --Specifies the recipient of an e-mail message
  28. utl_smtp.rcpt(mail_conn, recipient);
  29. -- open_data(), write_data(), and close_data() into a single call to data().
  30. --Sends the DATA command
  31. utl_smtp.open_data(mail_conn);
  32. utl_smtp.write_data(mail_conn,'From'||':'|| Sender || UTL_TCP.CRLF);
  33. utl_smtp.write_data(mail_conn,'To'||':'|| recipient || UTL_TCP.CRLF);
  34. utl_smtp.write_data(mail_conn,'Subject' ||':'|| subject || UTL_TCP.CRLF);
  35. --Writes a portion of the e-mail message
  36. utl_smtp.write_data(mail_conn, text);
  37. --Closes the data session
  38. utl_smtp.close_data(mail_conn);
  39. utl_smtp.quit(mail_conn);
  40. --dbms_output.put_line('Your message has been sent...!');
  41. EXCEPTION
  42. WHEN UTL_SMTP.PERMANENT_ERROR THEN
  43.   BEGIN
  44.     utl_smtp.quit(mail_conn);
  45.   END;
  46. RAISE_APPLICATION_ERROR(-20101,'This id has Permanent Error');
  47. WHEN UTL_SMTP.TRANSIENT_ERROR THEN
  48.   BEGIN
  49.     utl_smtp.quit(mail_conn);
  50.   END;
  51. RAISE_APPLICATION_ERROR(-20102,'SMTP transient error:');
  52. WHEN UTL_SMTP.INVALID_OPERATION THEN
  53.   BEGIN
  54.     utl_smtp.quit(mail_conn);
  55.   END;
  56. RAISE_APPLICATION_ERROR(-20103,'Invalid Operation in Mail using UTL_SMTP.');
  57. WHEN OTHERS THEN
  58. RAISE_APPLICATION_ERROR(-20104,'Some other Error ...!');
  59. end;
  60. /
  61.  
  62.  
To execute the above procedure try the following code.

Expand|Select|Wrap|Line Numbers
  1. exec sendmail('sender@sender.com','recipient@recipient.com','Hi','Test Mail');
  2.  
Feb 15 '08 #1
Share this Article
Share on Google+