Jeff Vannest's Weblog - How to create an Email Interface in SQL*LIMS

« "J & R Productivity Tips" | Main | "How to create a Memo Interface in SQL*LIMS" »

September 17, 2005

How to create an Email Interface in SQL*LIMS

In SQL*LIMS, a PL/SQL package can be installed as an event and attached as a status action in any template or instance object level. This offers the LIMS developer a robust environment in which to execute a wide variety of custom actions. For example, most versions of SQL*LIMS come with an example PL/SQL procedure for executing cross-task calculations. However, in order to make this example code robust enough for regular use, a developer would need to supplement it with things like...error handling! This article will demonstrate how to create a simple interface that will allow emails to be sent to a LIMS user when a PL/SQL package encounters an error.

The most important piece of an interface that sends emails is the SMTP server. Fortunately, the Oracle database has a built-in server that is made accessible through the core utl_smtp package. Using this package and a few system defaults, we can create an email interface that is called from a PL/SQL package to deliver emails to LIMS users.

To setup the system, we'll create two system defaults, EMAIL - SMTP SERVER and EMAIL - SMTP PORT. The server default indicates the name of the SMTP server used by your company to send email, and the port default indicates the TCP port number on which the server is listening. These values are typically something like "mail.jandrconsult.com" and "25", so these will be the example values. We also need to store a user's email address, so use the Define User Classes form and the Address tab in SQL*LIMS to create a new class called EMAIL. Next, use the Define Users form and User Information block on the Names tab to store the LIMS Owner's email address. For example:

Seq    Address Class      Address Line

1      EMAIL              Jeff.Vannest@jandrconsult.com

Now to make the design as modular as possible, create a function and procedure for the collection of email addresses and system defaults. Here are some very simple examples:

CREATE OR REPLACE PROCEDURE create_email_list(notify_in IN VARCHAR2,
	send_to_name_out OUT VARCHAR2, send_to_noname_out OUT VARCHAR2)
AS
BEGIN
	SELECT '"' || last || ', ' || first ||
	'" ' || addr_line, addr_line
	INTO send_to_name_out, send_to_noname_out
	FROM nais_addresses a, nais_usernames u
	WHERE u.userid = a.userid
		AND a.userid = REPLACE(UPPER(notify_in), 'OPS$', NULL)
		AND a.class = 'EMAIL'
		AND rownum = 1;
END create_email_list;
/

CREATE OR REPLACE FUNCTION sys_default(name_in VARCHAR2)
	RETURN VARCHAR2
AS
	v_response nais_system_defaults.value%TYPE;
BEGIN
	SELECT VALUE
	INTO v_response
	FROM nais_system_defaults
	WHERE UPPER(name) = UPPER(name_in);
	RETURN v_response;
END sys_default;
/

Of course, these examples need things like good error handling, code comments, and input parameter checking, but you get the idea. Here is the send email procedure with line numbers added for clarity:

1  CREATE OR REPLACE PROCEDURE send_email(body_in IN VARCHAR2,
2        subject_in IN VARCHAR2, notify_in IN VARCHAR2)
3  AS
4     v_full_body VARCHAR2(10000);
5     v_owner_name VARCHAR2(1000);      
6     v_owner_noname VARCHAR2(1000);      
7     v_mail_conn utl_smtp.connection;   
8     v_reply utl_smtp.reply;      
9     v_send_to_name VARCHAR2(1000);      
10    v_send_to_noname VARCHAR2(1000);      
11    v_smtp_server nai_system_defaults.value%TYPE;
12 BEGIN
13    create_email_list(notify_in, v_send_to_name, v_send_to_noname);
14    create_email_list(USER, v_owner_name, v_owner_noname);
15
16    v_smtp_server := sys_default('EMAIL - SMTP SERVER');
17    v_mail_conn := utl_smtp.open_connection(v_smtp_server,
sys_default('EMAIL - SMTP PORT'));
18
19    v_reply := utl_smtp.helo(v_mail_conn, v_smtp_server);
20    v_reply := utl_smtp.mail(v_mail_conn, v_send_to_noname);
21    v_reply := utl_smtp.rcpt(v_mail_conn, v_owner_noname);
22
23    v_full_body := 'To: ' || v_send_to_name || CHR(10) ||
24       'From: "' || USER || '" ' || v_owner_noname || CHR(10) ||
25       'Subject: ' || subject_in || CHR(10) ||
26       'Content-Type: text/html' || CHR(10) || CHR(10) ||
27       '<HTML><BODY><P><PRE>' || CHR(10) ||
28       body_in ||
29       '</PRE></P></BODY></HTML>';
30
31    v_reply := utl_smtp.data(v_mail_conn, v_full_body);
32    v_reply := utl_smtp.quit(v_mail_conn);
33
34 END send_email;
35 /

Lines 13 and 14 use the create_email_list created earlier to create the proper email address lines that the SMTP server expects. You'll notice that each call to the procedure returns the user's name and email address twice, once with the name included, and once without. These are intentional and will help the user see the name and email address of the sending and receiving users normally when receiving an email message. Line 14 uses the Oracle keyword "USER", which indicates that we assume this procedure will be executed by the LIMS Owner.

Lines 16 and 17 retrieved the name of the SMTP server name stored as a system default, and opened a connection to the mail server using the port specified by the appropriate system default.

Lines 19, 20 and 21 are used to introduce ourselves to this particular SMTP server, who comes from a long line of aristocratic servers and appreciates the formality.

Lines 23 through 29 construct the email message. Notice that simple HTML tags are used in lines 27 and 29 to wrap the body of the email message, which is consistent with the content type shown in line 26. If all of this technical detail sounds foreign don't be concerned; simply know that using pre-formatted HTML in the message body allows the text to be formatted for easy reading.

Finally, line 31 sends the full message text and line 32 says, "Good day, sir, I had a wonderful chat. My regards to your lovely wife."

So, we've reviewed the code; let's give this a test run. Log into SQL*Plus using the LIMS Owner account for the SQL*LIMS instance. This account is the user that owns the LIMS instance, for example ops$penlims. At the SQL*Plus prompt enter:

exec send_email(USER, 'Hello LIMSWorld!',
'Your tests look terrific, you get a raise.');

SQL*Plus responds "PL/SQL procedure successfully completed", and in a few minutes, the LIMS Owner will receive an email message at the address defined in the Define Users form. If this is not the response, then review the values of the SMTP system defaults.

Using the new procedure is simple: when an event occurs in a status action or PL/SQL event that should notify a user, execute a call to the send_email procedure and supply the LIMS User to which the message should be sent, the subject of the email, and the text body of the email.

In my next article, I'll describe how to create a simple PL/SQL interface to the SQL*LIMS memo system.

Posted by Jeff Vannest at September 17, 2005 09:40 PM