Compared to creating an email interface, the memo interface is very simple. Here is the send memo procedure with line numbers added for clarity:

1  CREATE OR REPLACE PROCEDURE send_memo(notify_in IN VARCHAR2,
body_in IN VARCHAR2) AS
2     v_document_id NUMBER;
3     v_r_status NUMBER;
4  BEGIN
5     naip_utility.counter(v_document_id, 'SERIAL', v_r_status);
6  
7     INSERT INTO nai_documents
8        (document_id, document_version, file_class,
9       document_clob, status, userstamp, timestamp)
10    VALUES
11       (v_document_id, 1, 'INTERNAL',
12       body_in, 'NEW', USER, SYSDATE);
13 
14    INSERT INTO nai_memos (userstamp, receiver, timestamp,
15       document_id, status, origin)
16    VALUES (REPLACE(USER, 'OPS$'), notify_in, SYSDATE,
17       v_document_id, 'NEW', 'Send Memo Procedure');
18    commit;
19 EXCEPTION
20    WHEN OTHERS THEN
21       dbms_output.put_line('ERROR: ' || SQLERRM);   
22 END send_memo;
23 /

Line 7 returns a document ID from the serial counter and uses the core SQL*LIMS Utility package to do so. This ID associates a master memo record with a detail record.

Lines 7-12 insert the memo into the Documents table, use the standard file_class of INTERNAL, and a status of NEW. When the user selects the memo using the Read Memos form, the status automatically changes to READ. This feature sets the SQL*LIMS memo apart from internet email because the storage and receipt of the notification is guaranteed unlike internet email, whose storage and delivery is not guaranteed.

Lines 14-17 insert the user notification records into the Memos table. This will cause the SQL*LIMS status bar to display a message to the user that a notification is waiting to be read. The origin column is set to “Send Memo Procedure”, although any value may be used.

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_memo(REPLACE(USER, 'OPS$'),
'Your tests look terrific, you get a raise.');

SQL*Plus responds "PL/SQL procedure successfully completed", and the LIMS Owner will immediately receive a SQL*LIMS memo that can be reviewed using the Read Memo form.

Using the new procedure in the application is simple: when an event occurs in a status action or custom program that should result in a user notification, execute the send_memo procedure and supply the LIMS User and the text of the notification.

We now have SQL*LIMS email and memo interfaces. For a more robust solution, the two procedures may be combined into a single messaging interface that sends notifications to either email or the memo system based on either programmatic input or a system flag such as a System Default.In my next article, I will describe how to create a simple FTP interface that can be used in a SQL*LIMS status action to send a file to another server.