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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: