How can I write and install a program in PL/SQL for auto-replying to my users over the Internet by fetching their e-mail IDs from my online database?

    Requires Free Membership to View

    Login

The UTL_SMTP package can be used to send e-mail from PL/SQL code to any user. All you need is the proper code and an SMTP gateway to send e-mail from. I have some sample PL/SQL code which shows you how to send an e-mail using UTL_SMTP. You just need to modify it for your purposes. 
DECLARE
   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
  -- Open connection to SMTP gateway
  c := UTL_SMTP.OPEN_CONNECTION('smtp.server.acme.com');
  UTL_SMTP.HELO(c, 'acme.com');
  UTL_SMTP.MAIL(c, 'userA@acme.com');
  UTL_SMTP.RCPT(c, 'userB@acme.com');
  UTL_SMTP.OPEN_DATA(c);
  send_header('From',    '"Oracle Admin" ');
  send_header('To',      '"Bob Smith" ');
  send_header('Subject', 'Automated Database Email');
  UTL_SMTP.WRITE_DATA(c, utl_tcp.CRLF || 'This is an automated email from the Oracle database.');
  UTL_SMTP.WRITE_DATA(c, utl_tcp.CRLF || 'The database is working for you!');
  UTL_SMTP.CLOSE_DATA(c);
  UTL_SMTP.QUIT(c);
END;
/
Feel free to modify this code sample for your needs. More information can be found in the Oracle documentation for the UTL_SMTP supplied PL/SQL package.

This was first published in December 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top