Browse DevX
Sign up for e-mail newsletters from DevX


Real-time Messaging in Oracle: Send E-mails Programmatically from the Database-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

The EmailUtils Package Specification
My EmailUtils package includes the following APIs:
  • SetSender/GetSender - Sets/gets the sender
  • SetRecipient/GetRecipient - sets/gets the recipient
  • SetCcrecipient/GetCcrecipient - sets/gets the cc: recipient
  • SetMailHost/GetMailHost - sets/gets the mail host
  • SetSubject/GetSubject - sets/gets the subject
  • Send - Sends mail

Listing 1 shows the EmailUtils package specification:

create or replace package EmailUtils as procedure SetSender(pSender in varchar2); function GetSender return varchar2; procedure SetRecipient(pRecipient in varchar2); function GetRecipient return varchar2; procedure SetCcRecipient(pCcRecipient in varchar2); function GetCcRecipient return varchar2; procedure SetMailHost(pMailHost in varchar2); function GetMailHost return varchar2; procedure SetSubject(pSubject in varchar2); function GetSubject return varchar2; procedure Send(pMessage in varchar2); procedure Send(pSender in varchar2, pRecipient in varchar2, pMailHost in varchar2, pCcRecipient in varchar2 := null, pSubject in varchar2 := null, pMessage in varchar2 := null); end EmailUtils; /

As you see, the Send procedure is overloaded: the package specification includes two versions of this procedure. One version will be invoked when at least three mandatory parameters are specified, pSender, pRecipient and pMailHost:

procedure Send(pSender in varchar2, pRecipient in varchar2, pMailHost in varchar2, pCcRecipient in varchar2 := null, pSubject in varchar2 := null, pMessage in varchar2 := null);

The other version will execute only when the pMessage parameter value is provided:

procedure Send(pMessage in varchar2);

This second version is the one that I use for debugging. All the e-mail messages share the same sender, recipient, mail host, cc recipient, and subject information, which I set at the beginning of the session with "setters" procedures. Here is an example of the PL/SQL block that does that:

begin EmailUtils.SetSender('BMilrud@MyCompany.com'); EmailUtils.SetRecipient('milrud@hotmail.com'); EmailUtils.SetCcRecipient('BMilrud@MyCompany.com'); EmailUtils.SetMailHost('MyServer.MyCompany.com'); EmailUtils.SetSubject('DeleteClassifications procedure: Run 1'); end; /

An actual e-mail message would be specified in each Send procedure invocation. You would insert all the EmailUtils.Send() calls inside the code you are debugging, the same way I utilized the DBMS_OUTPUT.PUT_LINE() calls previously, i.e.:

vMessage := 'Point 1.' || utl_tcp.crlf || 'Rows processed: ' || to_char(vRows) || utl_tcp.crlf || 'Elapsed time: ' || vTime; EmailUtils.Send(vMessage); vMessage := 'Point 3.' || utl_tcp.crlf || 'Rows processed: ' || to_char(vRows) || utl_tcp.crlf || 'Elapsed time: ' || vTime; EmailUtils.Send(vMessage);

Listing 2 shows the EmailUtils specification body with overloaded Send procedures. As you can see, the code in the Send procedure is rather straightforward. The UTL_SMTP package does not provide an API to format the content of the message according to the RFC 822 standard (like, for example, setting the subject). The user is responsible for formatting the message. That's why the following block is included inside each Send procedure to format the e-mail's header:

vMessage := 'Date: ' || to_char(sysdate, 'fmDy, DD Mon YYYY fxHH24:MI:SS') || utl_tcp.crlf || 'From: ' || pSender || utl_tcp.crlf || 'Subject: ' || pSubject || utl_tcp.crlf || 'To: ' || pRecipient || utl_tcp.crlf;

Also, you may get an error (ORA-06502: PL/SQL: numeric or value error) if the message length exceeds the 2,000-character limit. You can avoid this error with the following block, which won't allow messages to go over 2,000 characters:

if length(vMessage) > 2000 then vMessage := substr(vMessage, 1, 2000); end if;

If you need to send long e-mails with more than 2,000 characters, use the other three UTL_SMTP APIs, which provide a more fine-grain control than the DATA() procedure does. First, OPEN_DATA() sends the DATA command. Then WRITE_DATA() adds data to the string you're sending. You can call WRITE_DATA() as many time as needed, so you could write 2,000 characters at a time to overcome the character limit. Finally, CLOSE_DATA() ends the e-mail message by sending a period enclosed in the CRLF combination.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date