Q

PL/SQL program for auto-replying to users

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?

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?
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

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close