3 Custom Actions for Mail Rules

Oracle Collaboration suite provides the ability to customize Oracle Mail server-side rules to perform actions not provided by the Suite. These custom actions are program modules written in either PL/SQL, C, or Java. Regardless of the implementation language, developers must also write a PL/SQL "call specification" to provide the Oracle Mail server rules engine an interface to invoke the custom actions.

Implementing a Custom Rule Action in C

Rule action templates should be written as external C procedures, and stored in dynamic shared libraries (such as a Solaris .so library).

The steps for creating an external C procedure for use by a rule are:

Set up the environment for calling external procedures by adding entries to the files tnsnames.ora and listener.ora, and then starting a Listener process exclusively for external procedures. By default, the agent that handles external procedures is named extproc. This agent runs on the same database instance as the main application. The database server, the agent process, and the listener process that spawns the agent must all reside on the same host.
Identify the shared library, which is in this case a .so file (an operating system file that stores external procedures, and can be dynamically loaded). Using the CREATE LIBRARY statement, create a schema object called an alias library which represents the .so library. Specify the full path to the .so library in this object.
Publish the external procedure by using the PL/SQL call specification mechanism. Oracle Mailcan only use external procedures that are published through a call specification that maps names, parameter types, and return types for C external procedures to their SQL counterparts.

After creating the action, create a rule using the custom action by creating an XML file for the rule, and save the rule using the oesrl command Alternately, use the Rules Java API to create a new rule.

Please refer to the Email Java API Reference (Javadoc) for more information on this process.

A Custom Action Written in C and PL/SQL

The following sample custom action writes the input parameters (folder_id, msg_uid, param1, and param2) to an OS file.

Step 1

Grant execute permissions to the user that will run the custom action, as follows:

<SQL> connect es_mail/<password>;
<SQL> grant execute on mail_message_obj to <username>;

Step 2

Supply the following custom procedure to SQL Plus:

CREATE OR REPLACE PROCEDURE procwritefile (p_event IN NUMBER,
                                           p_sessionid IN NUMBER,
                                           p_msgobj IN es_mail.mail_message_obj,
                                           p_sessionid IN NUMBER,
                                           p_param2 IN VARCHAR2,
                                           p_status OUT NUMBER) AS
BEGIN
    p_status: = cs_writefile (p_event, p_sessionid, p_msgobj.folder_id,
                              p_msgobj.msg_uid, p_param1,p_param2);
END;

Step 3

The above procedure calls a function cs_writefile, which is implemented in C below. The following code writes the parameters to a file.

#include <stdio.h>
#include <stdlib.h>

int writefile (int event, int session_id, int fid,
               int muid, char *param1, char *param2) {
    FILE *fp = NULL;
    char buf[1024];
 
    /* open file for writing */
    if ((fp = fopen("/tmp/ruleaction.txt", "w")) == NULL) {
        return (-1);
    }

    /* write parameters and close the file */
    sprintf(buf, 
            "fid : %d, muid : %d, param1 : %s, param2 : %s",
            fid, muid, param1, param2);
    fputs(buf, fp);
    fclose(fp);
    return 0;
}

Step 4

Compile this C function and put it into a shared library called libesrule.so

% cc -G writefile.c -o libesrule.so

To load this shared library into the database, identify the shared library by using the CREATE library command in SQL Plus, and give the library a name (libesruleplsql in this case).

<SQL> connect system/<password>
<SQL> grant CREATE LIBRARY to <username>;
<SQL> connect <username>/<password>;
<SQL> CREATE OR REPLACE LIBRARY libesruleplsql AS '/absolute/path/libesrule.so';

Step 5

Publish the external C callout using a PL/SQL call specification. Create the specification in an application account, and issue the necessary grants issued for an es_mail database user to this account.

Note:

Call specifications for custom actions should not be created under the es_mail database user account itself. Instead, grant es_mail permissions to a regular user account.

CREATE OR REPLACE FUNCTION cs_writefile (p_event     IN BINARY_INTEGER,
                                         p_sessionid IN BINARY_INTEGER,
                                         p_folder_id IN BINARY_INTEGER,
                                         p_msgid     IN BINARY_INTEGER,
                                         p_param1    IN VARCHAR2,
                                         p_param2    IN VARCHAR2)
RETURN PLS_INTEGER
    AS LANGUAGE C
        NAME "writefile"
        LIBRARY libesruleplsql
        PARAMETERS (p_event      INT,
                    p_sessionid  INT,
                    p_folderid   INT,
                    p_msguid     INT,
                    p_param1     STRING,
                    p_param2     STRING,
                    RETURN INT);
 
SQL> connect <username>/<password>
SQL> grant execute on cs_writefile to es_mail
SQL> grant execute on procwritefile to es_mail

Custom actions for rules implemented as C callouts must support the following list of parameters:

p_event IN number

p_sessionid IN number

p_folder_id IN number

p_msguid IN number

p_param1 IN varchar2 /* custom parameter */

p_param2 IN varchar2 /* custom parameter */

Step 6

Now that a custom action has been created and implemented as a C callout, it can be called as part of a mail rule. To create a test rule for this action, use the following XML code.

<account qualifiedName="jdoe@orcltest.com">
  <rulelist event="deliver">
    <rule description="test">
      <action>
        <command tag="call">
        <parameter>username.procwritefile</parameter>
        <parameter>param1</parameter>
        <parameter>param2</parameter>
      </action>
    </rule>
  </rulelist>
</account>

Put the above XML into a file called rule.xml. Save the rule from the command line, using the oesrl command.

% oesrl -x rule.xml

Implementing a Custom Rule Action in Java

The process for implementing a rule in Java is similar to the C method, except instead of an external object, the Java code is placed in a Java stored procedure. The steps are as follows:

Write the action template for rule as a Java stored procedure and load the Java classes into the Oracle database.
Publish the Java classes. The methods of a Java class are not published automatically when they are loaded into the database. In order to be published, every method requires a call specification, which maps Java method names, parameter types, and return types to their SQL counterparts. You can declare a function or procedure call specification for a Java method using a SQL CREATE FUNCTION or CREATE PROCEDURE statement.

Once the classes have been published, you can create a rule that uses the custom action by creating a XML file for the rule. Save this rule using the oesrl command.

For further reading refer to the following Oracle Database documentation:

Database Application Developer's Guide - Fundamentals - Chapter 8: Calling External Procedures

Oracle Database Java Developer's Guide - Chapter 6: Publishing Java Classes With Call Specs

A Custom Action Written in Java

Example: A sample custom action for rule to write a set of parameters (folder_id, msg_uid, param1, and param2) to an OS file.

Step 1

Grant execute permissions to the user that will run the custom action, as follows:

<SQL> connect es_mail/<password>;
<SQL> grant execute on mail_message_obj to <username>;

Step 2

Supply the following custom procedure to SQL Plus:

CREATE OR REPLACE PROCEDURE procwritefile(p_event IN NUMBER,
    p_sessionid IN NUMBER,
    p_msgobj IN es_mail.mail_message_obj,
    p_param1 IN VARCHAR2,
    p_param2 IN VARCHAR2,
    p_status OUT NUMBER) AS
BEGIN
    p_status := cs_writefile (p_event, p_sessionid, p_msgobj.folder_id,
    p_msgobj.msg_uid, p_param1, param2);
END;

Step 3

The following Java code implements the action for the cs_writefile call in the above PL/SQL code. The code writes the contents of the param1 parameter to a local file.

import java.io.FileWriter;
import java.lang.StringBuffer;
 
public class filewrite {
 
public static int writeFile(int event, int session_id, int folder_id,
                            int msg_uid, String param1, String param2) {
    int retVal = 0; //failure 1, success 0

    try {
        StringBuffer strBuf = new StringBuffer();
        File outFile = new File("/tmp/ruleaction.txt");
        FileWriter out = new FileWriter(outFile);

        strBuf.append(folder_id);
        strBuf.append(msg_uid);
        strBuf.append(param1);
        strBuf.append(param1);
        out.write(strBuf.toString(),0,strBuf.length());
        out.close();
    } (Exception e) {
        e.printStackTrace();
        retVal = 1;
    }
    return retVal;
}

Compile the above program using the command:

% javac filewrite.java

Step 4

Load the compiled class file into the Oracle database using the loadjava utility.

% loadjava -u username/userpw@<connect_string> -v -r filewrite.class

Step 5

This sample program involves writing data to an OS file, so the OS file must be granted permission by issuing the dbms_java.grant_permission command from SQL Plus as a SYS user. The command is:

SQL> exec dbms_java.grant_permission ('SYSUSERNAME,'SYS:java.io.FilePermission','/tmp/ruleaction.txt','write');

Step 6

Provide the following call specification to SQL Plus:

CREATE OR REPLACE FUNCTION cs_writefile (p_event     IN NUMBER,
                                         p_sessionid IN NUMBER,
                                         p_folderid  IN NUMBER,
                                         p_msguid    IN NUMBER,
                                         p_param1    IN VARCHAR2,
                                         p_param2    IN VARCHAR2)
RETURN NUMBER
AS LANGUAGE JAVA
NAME filewrite.writeFile (int, int, int, int,
                          java.lang.String, java.lang.String)
                          return int);

This call specification should be created in an application account with es_mail permissions. Grant these permissions as follows:

SQL> connect <username>/<password>
SQL> grant execute on cs_writefile to es_mail
SQL> grant execute on procwritefile to es_mail

Note:

Call specifications for custom actions should not be created under the es_mail account itself. Instead, create a regular application account for this purpose.

Custom actions for rules implemented as Java callouts should support the following list of parameters:

p_event     IN NUMBER
p_sessionid IN NUMBER
p_folder_id IN NUMBER
p_msguid    IN NUMBER
p_param1    IN VARCHAR2        /* custom parameter */
p_param2    IN VARCHAR2        /* custom parameter */

Step 7

To use the new custom action in a rule, an XML file corresponding to the user's rule profile must be created manually:

<account qualifiedName="jdoe@orcltest.com">
  <rulelist event="deliver">
    <rule description="test">
      <action>
        <command tag="call">
        <parameter>username.procwritefile</parameter>
        <parameter>param1</parameter>
        <parameter>param2</parameter>
      </action>
    </rule>
  </rulelist>
</account>

Step 8

Save the rule with the oesrl command from the command line, as follows:

% oesrl -x rule.xml