Release 5.2
A86650-01
 Library |
 Solution Area |
 Index |
| Oracle eMail Server Developer's Guide Release 5.2 A86650-01 |
|
This chapter describes the PL/SQL APIs for Oracle eMail Server. Topics include the following:
A PL/SQL package named IOSend is installed as part of Oracle eMail Server to enable PL/SQL programmers to send Multipurpose Internet Mail Extension (MIME) messages using Oracle eMail Server. This package can be called to compose any messages that conform to the MIME standard, including simple text messages or messages with attachments.
An e-mail message is said to be MIME compliant if the message contains headers defined in the MIME standard. The MIME standard defines a mechanism to represent a complex message in plain text using headers, boundaries, and encoding methods. MIME enables messages that contain non-textual data, one or more message attachments, or messages written in a non-English character set to be transmitted using Simple Mail Transfer Protocol (SMTP), the standard for Internet e-mail transport.
In order to accommodate sending messages with complex MIME structures, this package exposes a calling interface that requires the caller to manually assemble a message part by part. Therefore, an understanding of MIME message structure is required for using this tool.
The IOSend package is loaded with installation of Oracle eMail Server. In addition, any application that uses to use this tool must perform the following initial setup steps:
IOSend package without owner qualification. This can be done in SQL*Plus for any database user as follows:
SQL>create synonym iosend for oo.iosend;
In order to send a MIME message, the following steps should be performed:
The following procedures are described in this section:
The AUTHENTICATE procedure should always be called first to start an IOSend session. You cannot send a message using IOSend without being authenticated as a valid Oracle eMail Server user.
PROCEDURE AUTHENTICATE(username IN VARCHAR2, domain IN VARCHAR2, password IN VARCHAR2, status OUT INTEGER);
The status is returned with one of the following values (all calls use the same set of output statuses):
The SUBMIT_HEADER procedure is called to submit message envelope information.
PROCEDURE SUBMIT_HEADER( tostr IN VARCHAR2, ccstr IN VARCHAR2, bccstr IN VARCHAR2, subj IN VARCHAR2, replyto IN VARCHAR2, fromstr IN VARCHAR2, defdate IN VARCHAR2, expdate IN VARCHAR2, priority IN INTEGER, sflags IN INTEGER, status OUT INTEGER);
The status returned is one of the values listed under "Output of the AUTHENTICATE Procedure".
This function enables you to add customized headers to the message. Conventionally, non-standard headers, as opposed to headers registered in the MIME standard, should have a prefix of "X-". If you want to add a header that includes the sender's phone number, the header should look like "X-Phone:" instead of "Phone:". Headers submitted that do not following this convention will be prefixed by "X-ORCL-APPLICATION:" automatically by the server.
PROCEDURE ADD_EXTRA_HEADER(lhs IN VARCHAR2, rhs IN VARCHAR2, ono IN INTEGER, status OUT INTEGER);
The status returned is one of the values listed under "Output of the AUTHENTICATE Procedure".
The most common use for the ADD_MIME_MARKER call is to establish a multipart MIME structure. For example, if you want to send messages with attachments, then you must call this procedure with marker_name being set to `multipart/mixed'.
PROCEDURE ADD_MIME_MARKER(marker_name IN VARCHAR2, mlevel IN VARCHAR2, status OUT INTEGER);
The status returned is one of the values listed under "Output of the AUTHENTICATE Procedure".
DESCRIBE_PART is used to describe an attachment. This is where you provide information such as content type, file name, and size of the attachment before you submit an attachment. This information is essential since MIME requires every attachment to have headers containing properties of the attachment.
PROCEDURE DESCRIBE_PART(filename IN VARCHAR2, descr IN VARCHAR2, mime_type IN VARCHAR2, pflags IN INTEGER, mlevel IN VARCHAR2, status OUT INTEGER);
| Input | Description |
|---|---|
|
|
The attachment filename |
|
|
A description of the attachment |
|
|
This parameter can be any of the valid MIME content types. The following are commonly used MIME content types:
|
|
|
The same range as |
|
|
The same meaning as |
The status returned is one of the values listed under "Output of the AUTHENTICATE Procedure".
This procedure is used to add additional attributes to the message part you added by calling ADD_CONTENT. Typically additional attributes include only MIME attributes like content-transfer-encoding or content-disposition.
PROCEDURE ADD_ATTRIBUTE(attr_name IN VARCHAR2, attr_value IN VARCHAR2, amarker IN INTEGER, aflags IN INTEGER, mlevel IN VARCHAR2, status OUT INTEGER);
| Input | Description |
|---|---|
|
|
The MIME attribute name |
|
|
The MIME attribute value. For example, one possible value for "Content-Disposition" can be "attachment." |
|
|
This parameter should be set to one of the following values:
Only values |
|
|
This parameter takes the same values as |
|
|
This parameter should be the same value passed in "ADD_MIME_MARKER Procedure". |
The status returned is one of the values listed under "Output of the AUTHENTICATE Procedure".
This call can be made several times to complete one large message part.
PROCEDURE ADD_CONTENT(vbuff IN VARCHAR2, rbuff IN RAW, buflen IN INTEGER, aflags IN INTEGER, status OUT INTEGER);
The status returned is one of the values listed under "Output of the AUTHENTICATE Procedure".
This procedure saves the current outgoing message to a folder. The folder must be owned by the user currently authenticated for the message composition.
PROCEDURE FOLDER_CARBON_COPY(fname IN VARCHAR2, fid IN INTEGER, status OUT INTEGER);
The status returned is one of the values listed under "Output of the AUTHENTICATE Procedure".
This is the final procedure to call before issuing a commit. This call makes the message available for transport.
PROCEDURE SUBMIT_MESSAGE(status OUT INTEGER);
The status returned is one of the values listed under "Output of the AUTHENTICATE Procedure".
This example demonstrates a simple PL/SQL script used to send a simple plain text message to a recipient. To run this script, modify the constant declaration section to make sure they contain valid values. The example can be accessed in the following directory:
$ORACLE_HOME/office/admin/samples/iosend_sample1.sql
DECLAREv_status INTEGER := 0; -- return status for the iosend procedures -- constant values used in this example c_user CONSTANT VARCHAR2(80) := 'scott'; c_password CONSTANT VARCHAR2(80) := 'tiger'; c_domain CONSTANT VARCHAR2(80) := 'acme.com'; c_tostr CONSTANT VARCHAR2(80) := 'jdoe@acme.com'; c_subject CONSTANT VARCHAR2(80) := 'Hello World'; c_body CONSTANT VARCHAR2(512) :='John,' || chr(10) || chr(10) || 'Just want to say hello!' || chr(10) || '-- Scott';BEGIN-- authentication is required to send a mail iosend.authenticate(c_user, c_domain, c_password, v_status); IF v_status != 0 THENdbms_output.put_line('AUTHENTICATE user error ' || v_status); RETURN;END IF;-- start the message by submitting the headers first -- non-applicable headers will just be NULL iosend.submit_header(c_tostr, NULL, NULL, c_subject, NULL, NULL,NULL, NULL, 0, 0, v_status);IF v_status != 0 THENdbms_output.put_line('Submit header error ' || v_status); rollback; RETURN;END IF;-- now submit the message bodyiosend.add_content(c_body, NULL, length(c_body), iosend.iosend_aflg_primary + iosend.iosend_aflg_done, v_status);IF v_status != 0 THENdbms_output.put_line('Submit content error ' || v_status); rollback; RETURN;END IF;-- finally submit the messageiosend.submit_message(v_status);IF v_status != 0 THENrollback; RETURN;END IF;-- time to commit;commit;END;
This script sends a message with its main body in HTML The content is loaded from an HTML file using DBMS_LOB package supplied by Oracle RDBMS. The message also has a GIF attachment that is loaded from another file using DBMS_LOB package.
Before running this example script, you need to perform the following steps:
This example can be accessed in the following directory:
$ORACLE_HOME/office/admin/samples/iosend_sample2.sql
DROP directory picdir; CREATE directory picdir AS '/tmp'; DECLAREv_status INTEGER; -- return status from iosend functions v_rawbuf RAW(2400); v_done BOOLEAN := false; v_flag INTEGER; v_bfile bfile; v_amt INTEGER; v_offset INTEGER := 1;c_buflen CONSTANT INTEGER := 2400; c_user CONSTANT VARCHAR2(80) := 'scott'; c_domain CONSTANT VARCHAR2(80) := 'acme.com'; c_password CONSTANT VARCHAR2(80) := 'tiger'; c_tostr CONSTANT VARCHAR2(80) := 'tom@acme.com'; c_cc CONSTANT VARCHAR2(80) := 'dick@acme.com'; c_subject CONSTANT VARCHAR2(80) := 'IOSEND Test Mail'; c_fromstr CONSTANT VARCHAR2(80) := 'Sample User'; c_replyto CONSTANT VARCHAR2(80) := 'harry@acme.com'; c_dirname CONSTANT VARCHAR2(40) := 'PICDIR'; c_bodytype CONSTANT VARCHAR2(40) := 'text/html'; c_txtname CONSTANT VARCHAR2(80) := 'message.html'; c_mimetype CONSTANT VARCHAR2(40) := 'image/jpg'; c_filename CONSTANT VARCHAR2(80) := 'picture.jpg';BEGIN-- authentication is required to send a mail iosend.authenticate(c_user, c_domain, c_password, v_status); IF v_status != 0 THENdbms_output.put_line('AUTHENTICATE error = ' || v_status); RETURN;END IF;-- start the message by submitting the header -- this mail has both TO and CC headers, with a customized Reply-To -- and FROM header. The expiration is set to be 3 weeks after it is-- sent, the header flags indicate that no Auto-replies are -- accepted.iosend.submit_header(c_tostr, c_cc, NULL, c_subject, c_replyto,c_fromstr, NULL, to_char(sysdate + 21, 'DD-MM-YYYY HH24:MI:SS'), 0, iosend.iosend_hflg_noareply + iosend.iosend_hflg_realatt, v_status);IF v_status != 0 THENdbms_output.put_line('Submit header error = ' || v_status); rollback; RETURN;END IF;-- now add a custom header --- this mail will have a custom header "X-Confidential-Level:" iosend.add_extra_header('X-Confidential-Level:','internal-circulation', 1, v_status);IF v_status != 0 THENdbms_output.put_line('Add header error = ' || v_status); rollback; RETURN;END IF;-- now copy it to a server side folder of the sender --- copy it to the folder "Sent" iosend.folder_carbon_copy('/Sent', 0, v_status); IF v_status != 0 THENdbms_output.put_line('FCC error = ' || v_status); rollback; RETURN;END IF;-- now add the MIME structural marker 'multipart/mixed' --- add this header because the message contains an attachment iosend.add_mime_marker('multipart/mixed', 0, v_status); IF v_status != 0 THENdbms_output.put_line('Add multipart/mixed header error = ' || v_status); rollback; RETURN;END IF;-- now desribe the message body in HTML iosend.describe_part(NULL, NULL, c_bodytype,iosend.iosend_aflg_primary, 1, v_status);IF v_status != 0 THENdbms_output.put_line('Describe body error = ' || v_status); rollback; RETURN;END IF;-- read the HTML body using DBMS_LOB from a file v_bfile := bfilename('PICDIR', c_txtname); dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly); LOOPv_amt := c_buflen; dbms_lob.read(v_bfile, v_amt, v_offset, v_rawbuf); v_offset := v_offset + v_amt;-- setup flags to tell IOSEND whether we have multiple trips v_flag := iosend.iosend_aflg_primary +iosend.iosend_aflg_mimeattr; IF v_amt < c_buflen THENv_flag := v_flag + iosend.iosend_aflg_done; END IF;-- now submit the main body iosend.add_content(utl_raw.cast_to_VARCHAR2(v_rawbuf), NULL, 0, v_flag, v_status); IF v_status != 0 THEN dbms_output.put_line('Add body error = ' || v_status); rollback; RETURN; END IF; EXIT WHEN v_amt < c_buflen; END LOOP; dbms_lob.fileclose(v_bfile); -- now describe the attachment -- note that mlevel here is 2 and pflag is 0 iosend.describe_part(c_filename, NULL, c_mimetype, 0, 2, v_status); IF v_status != 0 THEN dbms_output.put_line('Describe attachment error = ' || v_status); rollback; RETURN; END IF; -- now add MIME attribute content-disposition to attachment. -- Note that amarker is 4 for MIME attribute, aflag is 4 for -- attachment and mlevel is 2 as before iosend.add_attribute('Content-Disposition:', 'inline; filename="' || c_filename || '"', iosend.iosend_att_realatt + iosend.iosend_att_nonprint, iosend.iosend_aflg_mimeattr, 2, v_status); IF v_status != 0 THEN dbms_output.put_line('Add attribute error = ' || v_status); rollback; RETURN; END IF; -- read the attachment from a file v_bfile := bfilename(c_dirname, c_filename); dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly); v_offset := 1; LOOP v_amt := c_buflen; dbms_lob.read(v_bfile, v_amt, v_offset, v_rawbuf); v_offset := v_offset + v_amt; IF v_amt < c_buflen THEN v_flag := iosend.iosend_aflg_done; ELSE v_flag := 0; END IF; -- now submit the attachment. iosend.add_content(NULL, v_rawbuf, v_amt, v_flag, v_status); IF v_status != 0 THEN dbms_output.put_line('Add attachment error = ' || v_status); rollback; RETURN; END IF; EXIT WHEN v_amt < c_buflen; END LOOP; dbms_lob.fileclose(v_bfile); -- finally submit the message iosend.submit_message(v_status); IF v_status != 0 THEN dbms_output.put_line('Submit message error = ' || v_status); rollback; RETURN; END IF; -- time to commit; commit; END;
The Oracle eMail Server PL/SQL mail API (IM_API) is an interface to the Oracle eMail Server message store that enables PL/SQL programmers to retrieve, manage, and send e-mails with an Oracle eMail Server e-mail account.
The IM_API package is automatically loaded with the installation of Oracle eMail Server. In addition to this installation, any application that uses this package must perform the following initial setup steps:
IM_API without owner qualification. This can be done in SQL*Plus for any database user as follows:
SQL>create synonym im_api for oo.im_api;
IM_API functions are presented in groups with related functionality. The following groups are documented in this section:
The following functions are described in this section:
The AUTHENTICATE function authenticates a PL/SQL caller. Authentication is achieved by checking the supplied user name, password, and domain name against the local e-mail server. Authentication fails if no such user can be found. This function must be called before any other API functions in an IM_API session.
FUNCTION AUTHENTICATE(p_user IN VARCHAR2, password IN VARCHAR2, domain IN VARCHAR2 DEFAULT NULL)RETURN INTEGER;
This function changes the password on behalf of an authenticated user. This function requires AUTHENTICATE function being called already in the user session.
FUNCTION ChangePassword(newpass IN VARCHAR2)RETURN INTEGER;
| Input | Description |
|---|---|
|
newpass |
New unencrypted password |
| Returned Message | Description |
|---|---|
|
|
Success |
|
|
Not authenticated. This means that the |
The following functions are described in this section:
This function starts logging debug messages from within IM_API to a specified location.
FUNCTION EnableLogging( logdir IN VARCHAR2, logfile IN VARCHAR2, loglevel IN NUMBER) RETURN INTEGER;
| Returned Message | Description |
|---|---|
|
|
Success |
This function stops logging debug messages in the current session.
FUNCTION DisableLogging RETURN INTEGER;
| Returned Message | Description |
|---|---|
|
|
Success |
The following functions are described in this section:
This function creates a folder using a specified name.
FUNCTION CreateFolder( folder IN VARCHAR2) RETURN INTEGER;
| Returned Message | Description |
|---|---|
|
|
Success |
|
|
Not authenticated. This means that the |
This function deletes a specified folder.
FUNCTION DeleteFolder( folder IN VARCHAR2) RETURN INTEGER;
This function renames a specified folder.
FUNCTION RenameFolder( folder IN VARCHAR2, newname IN VARCHAR2) RETURN INTEGER;
This function retrieves the expiration property of a folder.
FUNCTION GetFolderExp( folder IN VARCHAR2, expiry OUT INTEGER) RETURN INTEGER;
| Input | Description |
|---|---|
|
folder |
The absolute path name of the folder to be queried |
This function sets the expiration property of a folder.
FUNCTION SetFolderExp( folder IN VARCHAR2, expiry IN INTEGER) RETURN INTEGER;
| Input | Description |
|---|---|
|
folder |
The absolute path name of the folder to be queried |
|
expiry |
The number of days before messages in this folder expires |
The following functions are described in this section:
This function generates a blind carbon copy (BCC) of the message to a recipient.
FUNCTION BlindCopyTo( message IN INTEGER, to_recip IN VARCHAR2) RETURN INTEGER;
| Input | Description |
|---|---|
|
message |
Message ID of the message to be blind carbon copied |
|
to_recip |
Recipient address that should received the blind carbon copy |
The ForwardTo function forwards a message to another recipient with additional simple comments.
FUNCTION ForwardTo( msgid IN INTEGER, recip IN VARCHAR2, notes IN VARCHAR2, subject IN VARCHAR2 DEFAULT NULL, fromstr IN VARCHAR2 DEFAULT NULL, replyto IN VARCHAR2 DEFAULT NULL) RETURN INTEGER;
The ForwardWithTemplate function forwards a message using an existing template for comments. For example, an auto-forward template can be attached to a forwarding message.
FUNCTION ForwardWithTemplate( msgid IN INTEGER, recip IN VARCHAR2, templid IN INTEGER) RETURN INTEGER;
| Input | Description |
|---|---|
|
|
Message ID of the message being replied to |
|
|
Recipient address that should receive the forwarded message |
|
|
Template ID of the template used to reply to the message |
The ReplyTo function replies to a message ID with additional simple comments.
FUNCTION ReplyTo( msgid IN INTEGER, notes IN VARCHAR2, subject IN VARCHAR2 DEFAULT NULL, fromstr IN VARCHAR2 DEFAULT NULL, replyto IN VARCHAR2 DEFAULT NULL) RETURN INTEGER;
The ReplyWithTemplate function replies to a message using an existing template for comments. For example, a vacation reply template can be attached to a replying message.
FUNCTION ReplyWithTemplate( msgid IN INTEGER, templid IN INTEGER) RETURN INTEGER;
|
|
Message ID of the message being replied to |
|
|
Template ID of the template used to reply to the message |
The SendSimpleMessage function sends a simple text message with no attachments.
FUNCTION SendSimpleMessage( recip IN VARCHAR2, comment IN VARCHAR2, subject IN VARCHAR2, fromstr IN VARCHAR2, replyto IN VARCHAR2) RETURN INTEGER;
This function lists all private templates for the user into a PL/SQL array. Every template in the array is an object containing its template ID and its message subject. This function can be used to retrieve template IDs to call the ReplyWithTemplate or ForwardWithTemplate functions.
FUNCTION ListTemplates( p_templates IN OUT api_template_c) RETURN INTEGER;
| Returned Message | Description |
|---|---|
|
|
Success |
|
|
Not authenticated. This means the |
The following functions are described in this section:
This function returns whether or not there is new e-mail for a user. This function can be used with no authentication if a valid user name and domain name is supplied.
FUNCTION HasNewMail( p_user IN VARCHAR2 DEFAULT NULL, p_domain IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN;
| Returned Message | Description |
|---|---|
|
TRUE |
There is new e-mail for the user defined in |
|
FALSE |
There is no new e-mail for the user defined in |
This function returns an array of message IDs of all of new mails.
FUNCTION GetNewMail( messages OUT msg_table) RETURN INTEGER;
| Returned Message | Description |
|---|---|
|
|
Success |
|
|
Not authenticated. This means the |
The following functions are described in this section:
Message navigation can be achieved by two different ways. You can either retrieve all message IDs into a PL/SQL array and then process the messages based on that array, or open a folder and repeatedly retrieve messages until the GetNextMessage function returns a value indicating no more messages are present in the folder.
This function lists all private folders for the user into a PL/SQL array. Every folder descriptor in the array is an object containing its folder ID and its hierarchical folder name.
FUNCTION ListFolders( p_folders IN OUT api_folder_c) RETURN INTEGER;
| Returned Message | Description |
|---|---|
|
|
Success |
|
|
Not authenticated. This means the |
This function retrieves all message IDs inside a folder in an PL/SQL index-by table.
FUNCTION OpenFolder( folder IN VARCHAR2, messages OUT msg_table) RETURN INTEGER;
| Input | Description |
|---|---|
|
folder |
The absolute path name of the folder to be opened. |
| Output | Description |
|---|---|
|
messages |
The list of message IDs inside the folder. The type TYPE msg_table IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; |
The OpenFolder function opens a named folder and returns the number of messages in that folder, the folder ID, the folder size in terms of total size of the messages residing in the folder, and folder type.
FUNCTION OpenFolder( fname IN VARCHAR2, count OUT INTEGER, fid OUT INTEGER, fsize OUT INTEGER, ftype OUT INTEGER) RETURN INTEGER;
| Input | Description |
|---|---|
|
fname |
Name of the folder to be opened |
| Output | Description |
|---|---|
|
|
Number of messages in the folder |
|
|
Folder ID of the opened folder |
|
|
Total size of the folder |
|
|
Folder type which takes one of the following values: |
The GetNextMessage function gets the next message in the currently opened folder. The API internally keeps a open cursor on the folder across iterative calls to this function.
FUNCTION GetNextMessage( msgid OUT INTEGER) RETURN INTEGER;
| Output | Description |
|---|---|
|
msgid |
Message ID of the next message in the opened folder |
The CloseFolder function closes the currently opened folder. There can only be one opened folder at a time. This call enables you to close an open folder without finishing retrieving all the messages from it.
FUNCTION CloseFolder RETURN INTEGER;
The following functions are described in this section:
Foldering operations include move, copy, and delete messages. Deleting a message is equivalent to moving a message from its original folder to the user's dedicated Wastebasket. For copy, move and delete operations, you can specify folders in either the hierarchical format or the non-hierarchical format using two different sets of functions. The former is recommended since it provides greater flexibility in organizing folders. The latter is limited to system with no folder hierarchy, or only the top-level folders in a system with hierarchical folders. Another key difference is that the first set of functions uses case-sensitive folder names and the latter uses case-insensitive folder names. For example, to specify the Inbox folder, its hierarchical name is /Inbox and the non-hierarchical name is Inbox.
This function copies a message from a source folder to a target folder.
FUNCTION CopyToFolder( message IN INTEGER, source IN VARCHAR2, target IN VARCHAR2) RETURN INTEGER;
This function moves a message from a source folder to a target folder.
FUNCTION MoveToFolder( message IN INTEGER, source IN VARCHAR2, target IN VARCHAR2) RETURN INTEGER;
This function deletes a message from a source folder.
FUNCTION DeleteFrom( message IN INTEGER, source IN VARCHAR2) RETURN INTEGER;
| Input | Description |
|---|---|
|
message |
Message ID of the message to be deleted |
|
source |
The absolute path name of the folder from which the message is to be deleted |
The CopyMessage function copies a message from one folder to another folder.
FUNCTION CopyMessage( msgid IN INTEGER, fromfld IN VARCHAR2, tofld IN VARCHAR2) RETURN INTEGER;
The MoveMessage function moves a message from one folder to another folder.
FUNCTION MoveMessage( msgid IN INTEGER, fromfld IN VARCHAR2, tofld IN VARCHAR2) RETURN INTEGER;
The DeleteMessage function deletes a message from one folder.
FUNCTION DeleteMessage( msgid IN INTEGER, fromfld IN VARCHAR2) RETURN INTEGER;
| Input | Description |
|---|---|
|
msgid |
Message ID of the message to be deleted |
|
fromfld |
Non-hierarchical source folder name of the message to be deleted |
The following functions are described in this section:
You can retrieve and save properties of a message instance either in one bundled function or do it one at a time for each property. The former is recommended if you need to manage more than one properties for a message. Another difference is that the bundled interface use hierarchical folder names and the non-bundled interfaces uses non-hierarchical folder names.
This function retrieves UID, priority, received date, expiration date, and read status for a message in a folder.
FUNCTION GetMessageProps( message IN INTEGER, folder IN VARCHAR2, muid OUT INTEGER, priority OUT INTEGER, received_date OUT DATE, expiration OUT DATE) read OUT INTEGER) RETURN INTEGER;
| Input | Description |
|---|---|
|
message |
Message ID of the message to be queried |
|
folder |
The absolute path name of the folder from which the message resides |
This function sets priority, expiration date and read status for a message in a folder.
FUNCTION SetMessageProps( message IN INTEGER, folder IN VARCHAR2, priority IN INTEGER, expiration IN DATE, read IN INTEGER DEFAULT -1) RETURN INTEGER;
The GetMessageProp function retrieves dynamic properties associated with a message instance; examples include the message priority and message expiration date. Static properties of the message are usually called messages headers, which are read-only properties. The caller can change dynamic properties.
FUNCTION GetMessageProp( msgid IN INTEGER, fname IN VARCHAR2, propflag IN INTEGER, valuen OUT INTEGER, valuev OUT VARCHAR2, valued OUT DATE) RETURN INTEGER;
| Input | Description |
|---|---|
|
msgid |
Message ID of the message to be retrieved |
|
fname |
Name of the folder containing the message to be retrieved |
|
propflag |
The following five properties can be used: |
The SetMessageProp function sets dynamic properties associated with a message instance.
FUNCTION SetMessageProp( msgid IN INTEGER, fname IN VARCHAR2, propflag IN INTEGER, valuen IN INTEGER, valuev IN VARCHAR2, valued IN DATE) RETURN INTEGER;
The following functions are described in this section:
You can retrieve a list of message attachments or MIME body parts either by using the new array interface GetPartList, or by using the iterative method to fetch one attachment ID at a time. The new function is recommended not only because it retrieves the attachment IDs and descriptors in one array, but also, because the function GetPartList is MIME knowledgeable, it will only return a list of unique body parts within a MIME message and discard duplicate entries inside a multipart/alternative block. You may still want to use the old function if you want to retrieve all body parts regardless of their MIME context, or if you want to retrieve "poorer" alternatives from a multipart/alternative MIME block. The new function GetPartList returns only the "richest" alternative if possible. The richness of a body part is defined as the relative detail contained in a particular presentation format for that body part. For example, a body part in HTML format is considered to be richer than the same body part in plain text format.
This function retrieves a list of unique body part descriptors from a MIME message. The list of descriptors is saved in a PL/SQL index-by table of PL/SQL records. The descriptor can then be used to retrieve the content the body part. If there's a "multipart/alternative" MIME block inside the message, only the best alternative is represented in the list of body part descriptors.
FUNCTION GetPartList( message IN INTEGER, attachments OUT att_table) RETURN INTEGER;
| Input | Description |
|---|---|
|
message |
Message ID of the message to be queried |
The GetMessageHdrs function retrieves the message headers.
FUNCTION GetMessageHdrs( msgid IN INTEGER, subject OUT VARCHAR2, sender OUT VARCHAR2, to_recip OUT VARCHAR2, cc_recip OUT VARCHAR2, from_str OUT VARCHAR2, sent_date OUT DATE, reply_to OUT VARCHAR2, msg_size OUT INTEGER) RETURN INTEGER;
| Input | Description |
|---|---|
|
|
Message ID of the message to be retrieved |
This function retrieves extended message headers from a message. Extended headers are message headers that are not part of the standard headers returned by function GetMessageHdrs. The extended headers retrieved are passed out in two arrays, one storing all the header names and one storing all the header values.
FUNCTION GetExtendedHdrs( message IN INTEGER, hdrnames OUT name_table, hdrvalues OUT value_table) RETURN INTEGER;
| Input | Description |
|---|---|
|
message |
Message ID of the message to be queried |
The GetMessageBody function retrieves the main message body. This function should be called in a loop to retrieve the e-mail message body one piece at a time. The parameter restart can be used to indicate the start of a new message retrieval. The return value can be used to determine whether the message has ended.
FUNCTION GetMessageBody( msgid IN INTEGER, restart IN BOOLEAN, buflen IN OUT INTEGER, msg_buf OUT VARCHAR2) RETURN INTEGER;
| Input | Description |
|---|---|
|
|
Message ID of the message to be retrieved |
|
|
Indicates whether this is the start of a new message |
|
|
Length of the buffer passed in |
| Output | Description |
|---|---|
|
|
The actual length returned in |
|
|
Contains the message body up to the value of |
The GetAttachmentBody function gets the attachment content if it is a textual attachment. It should be called in the same fashion as GetMessageBody function. The parameter part corresponds to the attachment ID returned of the function GetNextAttachment.
FUNCTION GetAttachmentBody( msgid IN INTEGER, part IN INTEGER, restart IN BOOLEAN, buflen IN OUT INTEGER, msg_buf OUT VARCHAR2) RETURN INTEGER;
| Output | Description |
|---|---|
|
|
The actual length returned in |
|
|
Contains the message body up to the value of |
The GetAttachmentData function retrieves the attachment content if it is a binary attachment. It should be called in the same fashion as GetMessageBody function. The parameter part corresponds to the attachment ID returned of the function GetNextAttachment.
FUNCTION GetAttachmentData( msgid IN INTEGER, part IN INTEGER, restart IN BOOLEAN, buflen IN OUT INTEGER, msg_buf OUT RAW) RETURN INTEGER;
| Output | Description |
|---|---|
|
|
The actual length returned in |
|
|
The raw buffer holding the attachment content |
The GetNextAttachment function gets the next attachment ID within the same message. The attachment ID can be used in conjunction with the message ID to retrieve the attachment content.
FUNCTION GetNextAttachment( msgid IN INTEGER, att_id OUT INTEGER, att_type OUT VARCHAR) RETURN INTEGER;
| Input | Description |
|---|---|
|
|
Message ID of the message to be retrieved |
| Output | Description |
|---|---|
|
att_id |
An order number within the message to represent a unique attachment |
|
att_type |
Indicate whether this attachment is binary or textual. |
The OpenMessageAttachment function opens a list of attachments for a message. This function must be called in conjunction with the GetNextAttachment function to iterate through the list of attachments.
FUNCTION OpenMessageAttachment( msgid IN INTEGER) RETURN INTEGER;
| Input | Description |
|---|---|
|
|
Message ID of the message to be retrieved |
The GetInclusionID function gets the included message ID in the current message.
FUNCTION GetInclusionID( msgid IN INTEGER, inc_msg OUT INTEGER) RETURN INTEGER;
| Input | Description |
|---|---|
|
|
Parent message |
| Output | Description |
|---|---|
|
|
Included message |
The following example uses IM_API to retrieve all of the messages and their attachments or included/forwarded messages in a user's Inbox folder. It then uses the DBMS_OUTPUT package supplied by the Oracle RDBMS to display all of the contents retrieved to the SQL*PLUS terminal.
The following example uses IM_API to retrieve all of the messages and their attachments or included/forwarded messages in a user's Inbox folder. It then uses the DBMS_OUTPUT package supplied by the Oracle RDBMS to display all of the contents retrieved to the SQL*PLUS terminal. It also forwards every message to a fixed address, generates an auto-reply, a notification, and finally deletes every message from Inbox. Before running the example, modify the constant declaration section to make sure all the values are valid.
This example can be accessed in the following directory:
$ORACLE_HOME/office/admin/samples/imapi_sample1.sql DECLARE -- sample data c_username CONSTANT VARCHAR2(30) := 'scott'; c_domain CONSTANT VARCHAR2(80) := 'acme.com'; c_password CONSTANT VARCHAR2(80) := 'tiger'; c_copyfolder CONSTANT VARCHAR2(30) := 'SENT'; c_fwdrecip CONSTANT VARCHAR2(30) := 'tom@acme.com'; c_fwdsubject CONSTANT VARCHAR2(30) := 'Forwarded mail'; c_fwdbody CONSTANT VARCHAR2(80) := 'The following is forwarded from oracle email Server'; c_resubject CONSTANT VARCHAR2(30) := 'Replied mail'; c_rebody CONSTANT VARCHAR2(80) := 'The following is received by oracle email server'; -- local variables restart BOOLEAN := true; folder_count INTEGER; folder_id INTEGER; folder_size INTEGER; folder_type INTEGER; msg_id INTEGER; top_id INTEGER; att_id INTEGER; inc_id INTEGER := 0; att_type CHAR; msg_buf VARCHAR2(240); raw_buf RAW(240); buf_len INTEGER; status INTEGER; subject VARCHAR2(240); from_str VARCHAR2(240); cc_str VARCHAR2(240); to_str VARCHAR2(240); replyto VARCHAR2(240); sender VARCHAR2(240); dummy VARCHAR2(240); sent_date DATE; msg_size INTEGER; expr_date DATE; prio INTEGER; BEGIN -- Login first status := im_api.authenticate(c_username, c_domain, c_password); dbms_output.put_line('Authentication Status : ' || status); -- Open INBOX status := im_api.openfolder('INBOX', folder_count, folder_id, folder_size, folder_type); dbms_output.put_line('Open Folder Status : ' || status); LOOP -- Navigate INBOX status := im_api.getnextmessage(msg_id); IF status = im_api.imapi_err_nomsg THEN EXIT; END IF; -- retrieve message dbms_output.put_line('=== Begin message ' || msg_id || ' ==='); -- Get and print the priority and expiration status := im_api.getmessageprop(msg_id, 'INBOX', im_api.imapi_prop_expr, prio, dummy, expr_date); status := im_api.getmessageprop(msg_id, 'INBOX', im_api.imapi_prop_prio, prio, dummy, expr_date); dbms_output.put_line('Expires : ' || expr_date); dbms_output.put_line('Priority: ' || prio); -- Parse the current mail message top_id := msg_id; LOOP -- Get and print the header status := im_api.getmessagehdrs(msg_id, subject, sender, to_str, cc_str, from_str, sent_date, replyto, msg_size); dbms_output.put_line('From : ' || from_str); dbms_output.put_line('To : ' || to_str); dbms_output.put_line('CC : ' || cc_str); dbms_output.put_line('Sender : ' || sender); dbms_output.put_line('Reply-To: ' || replyto); dbms_output.put_line('Subject : ' || subject); dbms_output.put_line('Date : ' || sent_date); dbms_output.put_line('Size : ' || msg_size); -- Get and print the main message body restart := true; dbms_output.put_line('=== Main message ==='); LOOP buf_len := 240; status := im_api.getmessagebody(msg_id, restart, msg_buf, buf_len); IF status = im_api.imapi_nomoredata AND buf_len = 0 THEN EXIT; END IF; restart := false; dbms_output.put_line(msg_buf); END LOOP; -- Open list of attachments status := im_api.openmessageattachment(msg_id); LOOP status := im_api.getnextattachment(msg_id, att_id, att_type); IF status = im_api.imapi_err_noatt THEN EXIT; END IF; dbms_output.put_line('=== Attachment ' || att_id || ' ==='); restart := true; LOOP buf_len := 240; IF att_type = 'Y' OR att_type = 'y' THEN status := im_api.getattachmentdata(msg_id, att_id, restart, raw_buf, buf_len); ELSE status := im_api.getattachmentbody(msg_id, att_id, restart, msg_buf, buf_len); END IF; IF status = im_api.imapi_nomoredata AND buf_len = 0 THEN EXIT; END IF; restart := false; IF att_type = 'Y' OR att_type = 'y' THEN dbms_output.put_line(substrb(raw_buf, 1, 240)); dbms_output.put_line(substrb(raw_buf, 241, 480)); ELSE dbms_output.put_line(msg_buf); END IF; END LOOP; END LOOP; -- Get the included message status := im_api.getinclusionid(msg_id, inc_id); IF inc_id = 0 OR inc_id IS NULL THEN EXIT; END IF; msg_id := inc_id; dbms_output.put_line('=== Included message ==='); END LOOP; dbms_output.put_line('=== End message ==='); -- forward and reply status := im_api.forwardto(msg_id, c_fwdrecip, c_fwdbody, c_fwdsubject, NULL, NULL); dbms_output.put_line('Forwarded with status ' || status); status := im_api.replyto(msg_id, c_rebody, c_resubject, NULL, NULL); dbms_output.put_line('Replied with status ' || status); -- copy and delete status := im_api.copymessage(msg_id, 'INBOX', c_copyfolder); dbms_output.put_line('Copied to folder ' || c_copyfolder || ' with status ' || status); status := im_api.deletemessage(msg_id, 'INBOX'); dbms_output.put_line('Deleted message with status ' || status); END LOOP; status := im_api.closefolder; END;
The following example uses IM_API to demonstrate some of the new features in 5.1 including folder management, array access, hierarchical foldering, file logging, etc. In order to run this script, make sure the following is performed first:
This example can be accessed in the following directory:
$ORACLE_HOME/office/admin/samples/imapi_sample.sql
DECLARE c_logdir CONSTANT VARCHAR2(80) := '/oracle/home/office/log'; c_logname CONSTANT VARCHAR2(80) := 'api_test.log'; c_user CONSTANT VARCHAR2(80) := 'scott'; c_domain CONSTANT VARCHAR2(80) := 'acme.com'; c_password CONSTANT VARCHAR2(80) := 'tiger'; c_newpass CONSTANT VARCHAR2(80) := 'welcome'; c_bcc CONSTANT VARCHAR2(80) := 'tom@acme.com'; ret INTEGER; v_exp INTEGER; v_acnt INTEGER; v_folders api_folder_c := api_folder_c(); v_templates api_template_c := api_template_c(); v_msgs im_api.msg_table; v_uid INTEGER; v_hdrnames im_api.name_table; v_hdrvalues im_api.value_table; v_atts im_api.att_table; v_prio INTEGER; v_rec DATE; v_ex DATE; v_inc INTEGER; v_sub VARCHAR2(240); v_sender VARCHAR2(240); v_to VARCHAR2(240); v_cc VARCHAR2(240); v_from VARCHAR2(240); v_reply VARCHAR2(240); v_sent DATE; v_size INTEGER; v_newmail BOOLEAN; v_read INTEGER; BEGIN -- this function is used to initialize logging in the -- $ORACLE_HOME/office/log directory. The first parameter should -- always be the expanded path to $ORACLE_HOME/office/log. Also -- passed are the log file name and log level. ret := im_api.enablelogging(c_logdir, c_logname, 1); -- Pass in username, fully qualified domain name and password to -- authenticate. ret := im_api.authenticate(c_user, c_domain, c_password); logpkg.log(1, 'Login as user ' || c_user || '@' || c_domain || ' with status ' || ret); -- Change to a new password ret := im_api.changepassword(c_newpass); logpkg.log(1, 'Changed password'); -- Create a hierarchical folder, the folder First does not have to -- exist. The format should always be absolute, with / in front and -- no / at the end ret := im_api.createfolder('/First/Second'); logpkg.log(1, 'Created folder /First/Second'); -- Rename a folder. It only renames the deepest level. The new -- name can only be a string without / in it. ret := im_api.renamefolder('/First/Second', 'Newname'); logpkg.log(1, 'Renamed folder to Newname'); -- Set folder expiration to 10 days ret := im_api.setfolderexp('/First/Newname', 10); logpkg.log(1, 'Set folder expiration'); -- Get folder expiration property ret := im_api.getfolderexp('/First/Newname', v_exp); logpkg.log(1, 'Folder /First/Newname expiration: ' || v_exp); -- Delete a folder, only the deepest level is deleted ret := im_api.deletefolder('/First/Newname'); logpkg.log(1, 'Deleted folder /First/Newname'); -- list all folders ret := im_api.listfolders(v_folders); logpkg.log(1, 'List of all folders:'); FOR ii IN 1..v_folders.last LOOP logpkg.log(1, v_folders(ii).folder_path); END LOOP; -- list all templates, template IDs -- can be used to reply and forward message using -- ReplyWithTemplate or ForwardWithTemplate functions ret := im_api.listtemplates(v_templates); logpkg.log(1, 'List of all private template IDs and subjects:'); FOR ii IN 1..v_templates.count LOOP logpkg.log(1, 'ID: ' || v_templates(ii).msg_id || ' Subject: ' || v_templates(ii).subject); END LOOP; -- check for new mail v_newmail := im_api.hasnewmail; IF v_newmail THEN logpkg.log(1, 'You''ve got mail'); ELSE logpkg.log(1, 'No New Mail'); END IF; -- Get new mail message IDs ret := im_api.getnewmail(v_msgs); logpkg.log(1, 'Got ' || v_msgs.count || ' new mails'); FOR ii IN 1..v_msgs.count LOOP logpkg.log(1, ' New mail message ID: ' || v_msgs(ii)); END LOOP; -- Open a folder and get a list of messages. Folder names -- are case sensitive. v_msgs.DELETE; ret := im_api.openfolder('/Inbox', v_msgs); logpkg.log(1, 'Opened folder /Inbox with ' || v_msgs.count || ' messages'); -- Loop through message list FOR ii IN 1..v_msgs.count LOOP logpkg.log(1, '======================'); logpkg.log(1, 'Message ID: ' || v_msgs(ii)); -- set each message to prioriy high and expiration to 3 weeks -- later also set message as read ret := im_api.setmessageprops(v_msgs(ii), '/Inbox', 50, sysdate+21, 1); logpkg.log(1, 'Set message properties'); -- get the same properties back plus received date and UID ret := im_api.getmessageprops(v_msgs(ii), '/Inbox', v_uid, v_prio, v_rec, v_ex, v_read); logpkg.log(1, 'UID: ' || v_uid); IF v_prio = 50 THEN logpkg.log(1, 'Priority: High'); ELSIF v_prio = 0 THEN logpkg.log(1, 'Priority: Normal'); ELSE logpkg.log(1, 'Priority: Low'); END IF; logpkg.log(1, 'Received: ' || v_rec); logpkg.log(1, 'Expire: ' || v_ex); IF v_read = 1 THEN logpkg.log(1, 'Status: Read'); ELSE logpkg.log(1, 'Status: Unread'); END IF; -- get standard message headers ret := im_api.getmessagehdrs(v_msgs(ii), v_sub, v_sender, v_to, v_cc, v_from, v_sent, v_reply, v_size); logpkg.log(1, 'Subject: ' || v_sub); logpkg.log(1, 'Sender: ' || v_sender); logpkg.log(1, 'TO: ' || v_to); logpkg.log(1, 'CC: ' || v_cc); logpkg.log(1, 'From: ' || v_from); logpkg.log(1, 'Sent: ' || v_sent); logpkg.log(1, 'Reply-To: ' || v_reply); logpkg.log(1, 'Size: ' || v_size); -- get messages headers that are not standard ret := im_api.getextendedhdrs(v_msgs(ii), v_hdrnames, v_hdrvalues); FOR jj IN 1..v_hdrnames.count LOOP logpkg.log(1, v_hdrnames(jj) || ' ' || v_hdrvalues(jj)); END LOOP; -- get a list of unique message parts/attachments ret := im_api.getpartlist(v_msgs(ii), v_atts); FOR jj IN 1..v_atts.count LOOP logpkg.log(1, '-----------------'); logpkg.log(1, 'Attachment part No: ' || v_atts(jj).part_number); logpkg.log(1, 'Content-Type: ' || v_atts(jj).content_type); IF v_atts(jj).is_binary = 'Y' THEN logpkg.log(1, 'Binary or Textual: Binary'); ELSIF v_atts(jj).is_binary = 'N' THEN logpkg.log(1, 'Binary or Textual: Textual'); END IF; logpkg.log(1, 'Attachment size: ' || v_atts(jj).att_size); logpkg.log(1, 'Filename: ' || v_atts(jj).att_name); END LOOP; logpkg.log(1, '-----------------'); -- BCC the message to another user ret := im_api.blindcopyto(v_msgs(ii), c_bcc); logpkg.log(1, 'BCCed this message to ' || c_bcc); -- copy to another folder, note the leading / ret := im_api.copytofolder(v_msgs(ii), '/Inbox', '/First'); logpkg.log(1, 'Copied this message to /First'); -- delete this message ret := im_api.deletefrom(v_msgs(ii), '/Inbox'); logpkg.log(1, 'Deleted this message'); END LOOP; -- turn off logging ret := im_api.disablelogging; END;
The server side rules feature in Oracle eMail Server is a mechanism for users to define automatic actions that act upon incoming messages when the messages meet certain custom-defined criteria. For example, you can set a rule to delete an e-mail when the subject contains the string "get rich fast." Actions are performed when the postman process is notifying users about new e-mail arrivals. The choices of actions and the choices of criteria to meet for a certain message are described in the following sections. Rule setup and management functionality is exposed through a set of PL/SQL functions.
A rule consists of two parts, a set of conditions to be met and an action to be performed when at least one of the conditions in the set is met. The set of conditions is therefore evaluated using a short-circuit OR operation, meaning the evaluation stops as soon as it encounters a true condition in the set. Rules are identified by their actions, therefore if multiple actions are to be carried out when certain condition is true, then multiple rules must be defined sharing the same set of conditions.
A condition is a combination of simple clauses. A clause is a simple relational operation between a message attribute and a scalar constant value. An example of a clause can be "message size is greater than 10K". Condition evaluation is implemented as a short-circuit AND operation upon all clauses; that is, the evaluation returns failure upon encountering the first failed clause.
Oracle eMail Server provides two sets of APIs to manage rules. One set of APIs is intended to be used by users or application integrators that act as users. This set of APIs are embedded in the IM_API package, taking advantage of facilities provided in the package such as authentication, logging, and access to folders and templates. The other set of APIs is intended to be used by administrators that have the privilege of directly accessing the e-mail database and monitoring users. The former is ideal to build applications that enables users to setup e-mail filters for themselves, or integrate with other applications. The latter is best suited for administrative scripting and setting up system-wide rules for the entire server.
User rules management API makes uses of Oracle object types to retrieve and store rules information. The following object types are described in this section:
The declaration for this type is:
CREATE OR replace TYPE api_rule_t AS object ( rule_id INTEGER, rule_name VARCHAR2(80), active VARCHAR2(1), action_id INTEGER, num_param INTEGER, vchar_param VARCHAR2(240), is_and INTEGER);
The object type represents a rule object.
The declaration for this type is:
CREATE OR replace TYPE api_rule_c AS TABLE OF api_rule_t;
This type is a nested table of API_RULE_T, representing a list of rules.
The declaration for this type is:
CREATE OR replace TYPE api_condition_t AS object ( rule_id INTEGER, attr_id INTEGER, op INTEGER, vchar_value VARCHAR2(255));
This object type represents a condition object. Every condition object has to be associated with a rule using its rule_id member variable.
The declaration for this type is:
CREATE OR replace TYPE api_condition_c IS TABLE OF api_condition_t;
This type is a nested table of API_CONDITION_T, representing a list of conditions.
The following functions are described in this section:
This function retrieves all the rule objects along with condition objects for the current user. You need to call IM_API.AUTHENTICATE before using these functions.
FUNCTION ListRules( p_rules IN OUT api_rule_c, p_conds IN OUT api_condition_c) RETURN INTEGER;
| Output | Description |
|---|---|
|
p_rules |
List of rule objects |
|
p_conds |
List of conditions objects that the rule object refers to |
| Returned Message | Description |
|---|---|
|
|
Success |
|
|
Not authenticated. This means the |
This function marks a given rule as enabled.
FUNCTION EnableRule( p_ruleid IN INTEGER) RETURN INTEGER;
| Input | Description |
|---|---|
|
p_ruleid |
Rule ID of the rule to be enabled |
| Returned Message | Description |
|---|---|
|
|
Success |
|
|
Not authenticated. This means the |
This function marks a given rule as disabled. A disabled rule still exists in the database but will not be executed.
FUNCTION DisableRule( p_ruleid IN INTEGER) RETURN INTEGER;
| Input | Description |
|---|---|
|
p_ruleid |
Rule ID of the rule to be disabled |
| Returned Message | Description |
|---|---|
|
|
Success |
|
|
Not authenticated. This means the |
This function deletes a given rule from the system.
FUNCTION DeleteRule( p_ruleid IN INTEGER) RETURN INTEGER;
| Input | Description |
|---|---|
|
p_ruleid |
Rule ID of the rule to be deleted |
| Returned Message | Description |
|---|---|
|
|
Success |
|
|
Not authenticated. This means the |
This function takes an ordered collection of rule objects in an array and reorders the rule execution orders in the system accordingly.
FUNCTION ListRules( p_rules IN api_rule_c) RETURN INTEGER;
| Input | Description |
|---|---|
|
p_ruleid |
Ordered list of rule objects |
| Returned Message | Description |
|---|---|
|
|
Success |
|
|
Not authenticated. This means the |
This function takes a rule object and its associated condition objects and saves it in the system. If the rule object already exists, then it modifies the existing rule according to the new input. If it does not exist, then it creates a new rule based on input.
FUNCTION SaveRuleDetail( p_rule IN OUT api_rule_t, p_conds IN api_condition_c) RETURN INTEGER;
| Output | Description |
|---|---|
|
p_rule |
If this is a new rule, then the modified rule object will be returned containing the new rule ID. This rule ID can be use later to perform more modifications. |
| Returned Message | Description |
|---|---|
|
|
Success |
|
|
Not authenticated. This means the |
The following functions are described in this section:
The start_condition function starts a new condition by returning a unique condition ID for you to make subsequent modifications to it.
FUNCTION start_condition RETURN INTEGER;
| Return | Description |
|---|---|
|
condid |
Condition ID of the condition to be used in subsequent calls |
The add_clause function adds a clause into an existing condition identified by the condition ID.
procedure add_clause( condid IN INTEGER, attrid IN INTEGER, compid IN INTEGER, valuen IN INTEGER, valuev IN VARCHAR2, valued IN DATE, orderno IN INTEGER, status IN OUT INTEGER);
| Output | Description |
|---|---|
|
status |
Stores the return status of this procedure. A non-zero value indicates an error. |
The create_rule function creates a rule based on a starting condition passed in by the caller.
FUNCTION create_rule( username IN VARCHAR2, domain IN VARCHAR2, name IN VARCHAR2, condid IN INTEGER, actnid IN INTEGER, infon1 IN INTEGER, infon2 IN INTEGER, infov IN VARCHAR2, status IN OUT INTEGER) RETURN INTEGER;
| Output | Description |
|---|---|
|
status |
The return status of this procedure. A non-zero value indicates an error. |
A unique rule ID that identifies the rule just created.
Syntax for the add_cond Function
procedure add_cond( ruleid IN INTEGER, ondid IN INTEGER, tatus IN OUT INTEGER);
The add_cond function adds a condition into an existing rule identified by the rule ID.
|
ruleid |
An existing rule ID, should be originally coming from a call to create_rule. |
|
condid |
An existing condition ID to be inserted in the rule. |
|
status |
The return status of this procedure. A non-zero value indicates an error. |
Syntax for delete_rule Function
procedure delete_rule( ruleid IN INTEGER, status IN OUT INTEGER);
The delete_rule function deletes a rule from the system on behalf of a user. If the condition IDs referenced by the rule no longer used in the system, delete the condition ID too. If deleting this rule results in this user having no rules at all, disable the user preference that specifies the user has rules.
|
ruleid |
An existing rule ID to be deleted |
|
status |
Return status of this procedure. A non-zero value indicates an error. |
Syntax for toggle_rule Function
procedure toggle_rule( ruleid IN INTEGER, active IN BOOLEAN, status IN OUT INTEGER);
The toggle_rule function toggles the active flag on this rule. A rule can be disabled or enabled by this call. Disabled rules stay on the system until the delete_rule function is called.
|
ruleid |
Existing rule ID to be disabled/enabled. |
|
active |
A Boolean value. When set to true, the rule is enabled, otherwise the rule is disabled. |
|
status |
The return status of this procedure. A non-zero value indicates an error. |
The view OM_USER_RULES can be queried to obtain a list of all the rules defined for users. For example, after running the sample rules script in the following section to create rules, the following SQL commands provide a list of the rules created:
SQL> select * from om_user_rules where name='SCOTT'; NAME RULE_ID Rule_State ------------------------------ ---------- ---------- COMPARISON ----------------------------------------------------------------- Rule_Action ----------------------------------------------------------------- SCOTT 1010 Active Subject contains (LIKE '%X%') 'get rich fast' Delete message SCOTT 1010 Active Message size greater than (>) '10000' Delete message SCOTT 1010 Active Subject contains (LIKE '%X%') 'test message' Delete message
If a rule is defined to call an external PL/SQL procedure as its action, the procedure must be written with the following restrictions:
msg_id IN integer, username IN varchar2, domain IN varchar2, ninfo1 IN integer, ninfo2 IN integer, rstatus OUT integer
The msg_id, username, and domain supply the basic information about the current message and its current recipient. The ninfo1 and ninfo2 is used to hold additional customized input to make a procedure reusable in different rules. The rstatus needs to be returned by the procedure and can be used subsequently in rules based on the attribute ATTR_RULE_STATUS.
The following example uses IM_API to setup two rules, and also to demonstrate other functionalities including rule retrieval, re-ordering and deletion. Before running the script, make sure the following is performed first:
This example can be accessed in the following directory:
$ORACLE_HOME/office/admin/samples/rules_sample.sql DECLARE c_user CONSTANT VARCHAR2(80) := 'scott'; c_domain CONSTANT VARCHAR2(80) := 'acme.com'; c_password CONSTANT VARCHAR2(80) := 'tiger'; c_logdir CONSTANT VARCHAR2(80) := '/oracle/home/office/log'; c_logname CONSTANT VARCHAR2(80) := 'rule_test.log'; c_folder CONSTANT VARCHAR2(80) := '/Mailing lists'; v_rules api_rule_c := api_rule_c(); v_folders api_folder_c := api_folder_c(); v_conds api_condition_c := api_condition_c(); v_res INTEGER; v_yes VARCHAR2(10); v_andor VARCHAR2(10); v_rule api_rule_t; v_jj INTEGER := 1; v_id INTEGER; BEGIN -- setup logging and login as a mail user v_res := im_api.enablelogging(c_logdir, c_logname, 1); v_res := im_api.authenticate(c_user, c_domain, c_password); -- create the first rule: -- if Subject: header contains 'for sale' and size > 10000 -- delete; v_conds.extend(2); v_conds(1) := api_condition_t(0, mail_rules.attr_subject, mail_rules.comp_contains, 'for sale'); v_conds(2) := api_condition_t(0, mail_rules.attr_msg_size, mail_rules.comp_gt, '10000'); v_rule := api_rule_t(0, 'Delete Junk Mail', 'A', mail_rules.action_delete, NULL, NULL, 1); v_res := im_api.saveruledetail(v_rule, v_conds); logpkg.log(1, 'Created rule ID ' || v_rule.rule_id); -- create another rule: -- if TO: header does not contain myself -- move to folder '/Mailing lists'; v_conds.DELETE(2); v_conds(1) := api_condition_t(0, mail_rules.attr_to_recip, mail_rules.comp_not_contain, c_user); -- find out folder ID for '/Mailing lists' v_res := im_api.listfolders(v_folders); FOR ii IN 1..v_folders.last LOOP IF v_folders(ii).folder_path = c_folder THEN v_id := v_folders(ii).folder_id; EXIT; END IF; END LOOP; v_rule := api_rule_t(0, 'Save list mails', 'A', mail_rules.action_move, v_id, NULL, 1); v_res := im_api.saveruledetail(v_rule, v_conds); logpkg.log(1, 'Created rule ID ' || v_rule.rule_id); v_rules.DELETE; -- now retrieve the rules v_res := im_api.listrules(v_rules, v_conds); logpkg.log(1, 'Retrieved ' || v_rules.count || ' rules'); FOR ii IN 1..v_rules.count LOOP logpkg.log(1, '-------------------------------------'); logpkg.log(1, 'Rule name: ' || v_rules(ii).rule_name); logpkg.log(1, 'Rule ID: ' || v_rules(ii).rule_id); IF v_rules(ii).active = 'A' THEN v_yes := 'yes'; ELSE v_yes := 'no'; END IF; logpkg.log(1, 'Active: ' || v_yes); -- list conditions IF v_rules(ii).is_and = 1 THEN v_andor := '(AND)'; ELSE v_andor := '(OR)'; END IF; logpkg.log(1, 'Condition' || v_andor || ':'); WHILE v_jj <= v_conds.count AND v_conds(v_jj).rule_id = v_rules(ii).rule_id LOOP logpkg.log(1, im_api.rule_attrtab(v_conds(v_jj).attr_id) || ' ' || im_api.rule_optab(v_conds(v_jj).op) || ' ' || v_conds(v_jj).vchar_value); v_jj := v_jj + 1; END LOOP; -- print action logpkg.log(1, 'Action: '); IF v_rules(ii).num_param IS NOT NULL THEN logpkg.log(1, im_api.rule_actiontab(v_rules(ii).action_id) || ' ' || v_rules(ii).num_param); ELSIF v_rules(ii).vchar_param IS NOT NULL THEN logpkg.log(1, im_api.rule_actiontab(v_rules(ii).action_id) || ' ' || v_rules(ii).vchar_param); ELSE logpkg.log(1, im_api.rule_actiontab(v_rules(ii).action_id)); END IF; END LOOP; logpkg.log(1, '-----------------------------------'); -- reorder the rules, make the first rule become the last v_rules.extend; v_rules(v_rules.count) := v_rules(1); FOR ii IN 1..v_rules.count - 1 LOOP v_rules(ii) := v_rules(ii+1); END LOOP; v_rules.DELETE(v_rules.count); v_res := im_api.orderrules(v_rules); logpkg.log(1, 'Re-ordered rules'); -- Delete the first rule v_res := im_api.deleterule(v_rules(1).rule_id); logpkg.log(1, 'Deleted a rule'); v_res := im_api.disablelogging; END;
The following example uses MAIL_RULES package to setup a number of rules, demonstrating the variety of ways to create rule actions and conditions. Before running the script, make sure the following is performed first:
This example can be accessed in the following directory:
$ORACLE_HOME/office/admin/samples/rules_sample.sql -- simple procedure used by custom PL/SQL rule callout CREATE OR replace PROCEDURE process_order(msg_id IN INTEGER, username IN VARCHAR2, domain IN VARCHAR2, ninfo1 IN INTEGER, ninfo2 IN INTEGER, rstatus OUT INTEGER) IS c_logdir CONSTANT VARCHAR2(80) := '/oracle/home/office/log'; c_logfile CONSTANT VARCHAR2(80) := 'rule_test.log'; BEGIN logpkg.init(c_logdir, c_logfile, 5); logpkg.log(1, 'Processed message detail:'); logpkg.log(1, '-------------------------'); logpkg.log(1, 'Message ID: ' || msg_id); logpkg.log(1, 'User name: ' || username); logpkg.log(1, 'Domain name: ' || domain); logpkg.log(1, 'Parameter 1: ' || ninfo1); logpkg.log(1, 'Parameter 2: ' || ninfo2); logpkg.log(1, '-------------------------'); logpkg.cleanup; rstatus := 0; END; / -- Setting up server side rules using MAIL_RULES package DECLARE c_user CONSTANT VARCHAR2(30) := 'scott'; c_domain CONSTANT VARCHAR2(80) := 'acme.com'; c_folder1 CONSTANT VARCHAR2(80) := 'Important Events'; c_folder2 CONSTANT VARCHAR2(80) := 'Announcement Records'; c_recip CONSTANT VARCHAR2(80) := 'tom@acme.com'; c_template CONSTANT VARCHAR2(80) := 'ORDER RECEIVED'; ruleid INTEGER; condid INTEGER; status INTEGER; folderid INTEGER; templateid INTEGER; BEGIN -- create rule 1: -- Delete message if subject contains 'for sale' -- and size > 10000 bytes, -- or if subject contains 'get paid to surf'. condid:= mail_rules.start_condition; -- subject contains 'for sale' mail_rules.add_clause(condid, MAIL_RULES.ATTR_SUBJECT, MAIL_RULES.COMP_CONTAINS, 0, 'for sale', NULL, 1, status); -- and message size > 10000 bytes; or .. mail_rules.add_clause(condid, MAIL_RULES.ATTR_MSG_SIZE, MAIL_RULES.COMP_GT, 10000, NULL, NULL, 2, status ); -- create a rule ruleid := mail_rules.create_rule(c_user, c_domain, 'Delete Junk Mail', condid, MAIL_RULES.ACTION_DELETE, 0, 0, NULL, status); -- create a new condition, needs a new condid condid:= mail_rules.start_condition; -- .. or, subject contains 'get paid to surf' mail_rules.add_clause(condid, MAIL_RULES.ATTR_SUBJECT, MAIL_RULES.COMP_CONTAINS, 0, 'get paid to surf', NULL, 1, status); -- add an 'OR'ed condition to the existing rule mail_rules.add_cond(ruleid, condid, status); -- Create rule 2: Send message to alternative recipient if subject -- contains 'Meeting anouncement'. condid:= mail_rules.start_condition; -- subject contains 'Meeting anouncement' mail_rules.add_clause(condid, MAIL_RULES.ATTR_SUBJECT, MAIL_RULES.COMP_CONTAINS, 0, 'Meeting announcement', NULL, 1, status); ruleid := mail_rules.create_rule(c_user, c_domain, 'BCC announcements', condid, MAIL_RULES.ACTION_ALT_RECIP, 0, 0, c_recip, status); -- Create rule 3: Move message to folder 'Important Events' -- if message priority is high (the number 50) condid:= mail_rules.start_condition; -- Message priority = 50 mail_rules.add_clause(condid, MAIL_RULES.ATTR_PRIORITY, MAIL_RULES.COMP_EQ, 50, NULL, NULL, 1, status); -- this rule requires folder ID that we can either use IM_API -- or directly query the mail database. Since this is a privileged -- script, we'll directly query the database select FOLDER_ID into folderid from OM_folder where disp_name = c_folder1 AND creator = (SELECT objectid FROM ds_account WHERE name=upper(c_user)); ruleid := mail_rules.create_rule(c_user, c_domain, 'Prioritize mail', condid, MAIL_RULES.ACTION_MOVE, folderid, 0, NULL, status); -- Create rule 4: Copy message to folder 'Announcement Records' -- if message subject contains 'Announcement' condid:= mail_rules.start_condition; -- subject contains 'Announcement' mail_rules.add_clause(condid, MAIL_RULES.ATTR_SUBJECT, MAIL_RULES.COMP_CONTAINS, 0, 'Announcement', NULL, 1, status); select FOLDER_ID into folderid from OM_folder where disp_name = c_folder2 AND creator = (SELECT objectid FROM ds_account WHERE name=upper(c_user)); ruleid := mail_rules.create_rule(c_user, c_domain, 'Keep a record', condid, MAIL_RULES.ACTION_COPY, folderid, 0, NULL, status); -- Create rule 5: Generate reply with template 'ORDER RECEIVED' if -- the subject of the original message contains 'ORDER ENTERED: ' condid:= mail_rules.start_condition; -- Subject contains 'ORDER ENTERED: ' mail_rules.add_clause(condid, MAIL_RULES.ATTR_SUBJECT, MAIL_RULES.COMP_CONTAINS, 0, 'ORDER ENTERED:', NULL, 1, status); -- get the template ID from the database. This example uses -- direct query to the database for simplicity select MSG_ID into templateid from OM_template where TEMPL_NAME = c_template AND templ_owner = (SELECT objectid FROM ds_account WHERE name=upper(c_user)); ruleid := mail_rules.create_rule(c_user, c_domain, 'Auto-reply', condid, MAIL_RULES.ACTION_REPLY, templateid, 0, NULL, status); -- Create rule: Invoke PL/SQL procedure 'PROCESS_ORDER' if the -- subject of the original message contains 'ORDER : ' condid:= mail_rules.start_condition; -- subject contains 'ORDER ENTERED: ' mail_rules.add_clause(condid, MAIL_RULES.ATTR_SUBJECT, MAIL_RULES.COMP_CONTAINS, 0, 'ORDER ENTERED:', NULL, 1, status); ruleid := mail_rules.create_rule(c_user, c_domain, 'Process order', condid, MAIL_RULES.ACTION_RUN_PROC, 0, 0, 'PROCESS_ORDER', status); -- Create rule: Alter priority of message to high (the number 50) -- if subject contains 'Meeting' -- create a new condition, needs a new condid condid:= mail_rules.start_condition; -- Subject contains 'Meeting' mail_rules.add_clause(condid, MAIL_RULES.ATTR_SUBJECT, MAIL_RULES.COMP_CONTAINS, 0, 'Meeting', NULL, 1, status); ruleid := mail_rules.create_rule(c_user, c_domain, 'Mark meetings', condid, MAIL_RULES.ACTION_SET_PRIO, 50, 0, NULL, status); -- By default rules are created enabled. Disable the last rule mail_rules.toggle_rule(ruleid, FALSE, status); -- Enable the last rule mail_rules.toggle_rule(ruleid, TRUE, status); -- To delete last rule mail_rules.delete_rule(ruleid, status); END;
A PL/SQL package named DAPLS is installed as part of Oracle eMail Server to enable PL/SQL programmers to create user accounts and perform other administrative tasks. This package supports some but not all IOFCMGR commands.
The DAPLS package is automatically loaded with the installation of Oracle eMail Server. This package works together with the external procedure call feature of Net8. To use the DAPLS package you must have your Net8 listener running and configured to listen for external procedure calls.
|
See Also:
Oracle Net8 Administrator's Guide for more information on Configuring Net8 for External Procedures |
This packages defines the following PL/SQL exceptions:
invalid_admin_credential EXCEPTION; invalid_messaging_domain EXCEPTION; invalid_command EXCEPTION;
This procedure is deprecated. The functionality of this procedure is available from the ADMIN_EXEC procedure.
This procedure is deprecated. The functionality of this procedure is available from the ADMIN_EXEC procedure.
This procedure is deprecated. The functionality of this procedure is available from the ADMIN_EXEC procedure.
This procedure is deprecated. The functionality of this procedure is available from the ADMIN_SETPWD procedure.
The SET_ADMIN_ID procedure sets the administrator ID that will be used for authentication by subsequent calls to the ADMIN_EXEC and ADMIN_SETPWD procedures. This ID is typically "admin" but may also be the account ID of any messaging account that has administrative privileges for the target administrative messaging domain.
PROCEDURE SET_ADMIN_ID (admin_id_in IN VARCHAR2)
| Input | Description |
|---|---|
|
admin_id_in |
contains the administrator ID that will be used for authentication by subsequent calls to the ADMIN_EXEC and ADMIN_SETPWD procedures |
The SET_ADMIN_PASSWORD procedure sets the administrator password that will be used for authentication by subsequent calls to the ADMIN_EXEC and ADMIN_SETPWD procedures. This password must be the correct password for the administrator account specified by calling SET_ADMIN_ID.
PROCEDURE SET_ADMIN_PASSWORD(admin_password_in IN VARCHAR2)
| Input | Description |
|---|---|
|
admin_password_in |
contains the administrator password that will be used for authentication by subsequent calls to the ADMIN_EXEC and ADMIN_SETPWD procedures |
The SET_SERVICE_ADDRESS procedure sets the TNS service address used for establishing TNS connections by subsequent calls to the ADMIN_EXEC and ADMIN_SETPWD procedures. This service address should refer to the eMail Server database and it should be possible for the TNS listener to resolve the address by examining the tnsnames.ora file or by using any other appropriate service name resolution facility, e.g. Oracle Names.
PROCEDURE SET_SERVICE_ADDRESS(service_address_in IN VARCHAR2)
| Input | Description |
|---|---|
|
service_address_in |
The service address of the eMail Server database |
The SET_MESSAGING_DOMAIN procedure sets the eMail Server domain used by subsequent calls to the ADMIN_EXEC and ADMIN_SETPWD procedures. The messaging domain typically has the form "<company-name>.com"
PROCEDURE SET_MESSAGING_DOMAIN(messaging_domain_in IN VARCHAR2)
| Input | Description |
|---|---|
|
messaging_domain_id_in |
The eMail Server domain used by subsequent calls to the ADMIN_EXEC and ADMIN_SETPWD procedures |
The ADMIN_EXEC procedure allows most IOFCMGR command lines to be performed. Before ADMIN_EXEC is called, the procedures SET_ADMIN_ID, SET_ADMIN_PASSWORD, SET_SERVICE_ADDRESS, and SET_MESSAGING_DOMAIN must be called to initialized the administrative credentials and service address information that will be used when the command line is performed. After this procedure is called, the status_inout parameter will contain a status code indicating whether the command line was performed successfully or not.
The status_inout parameter MUST be initialized to 0 before admin_exec() is called.
An example command line is "insert person username=chad uanode=esnode"
Not all OOMGR command lines are supported by the admin_exec() procedure.
PROCEDURE ADMIN_EXEC( command IN VARCHAR2, status_inout IN OUT BINARY_INTEGER)
| Input | Description |
|---|---|
|
command |
OOMGR command line |
| Output | Description |
|---|---|
|
status_inout |
command return code; 0 for success and not 0 for failure |
The ADMIN_SETPWD procedure allows the password for an eMail Server user account to be changed. Before ADMIN_SETPWD is called, the procedures SET_ADMIN_ID, SET_ADMIN_PASSWORD, SET_SERVICE_ADDRESS, and SET_MESSAGING_DOMAIN must be called to initialized the administrative credentials and service address information that will be used when the user account password is set. After this procedure is called, the status_inout parameter will contain a status code indicates whether the password was changed successfully or not.
The status_inout parameter MUST be initialized to 0 before admin_exec() is called.
The service address must resolve to the user's home node database. This is only of concern in a multi-server installation.
PROCEDURE ADMIN_SETPWD( user_id IN VARCHAR2, user_pass IN VARCHAR2, status_inout IN OUT BINARY_INTEGER)
| Input | Description |
|---|---|
|
user_id |
ID of an eMail Server user account |
|
user_pass |
new password for the user |
| Output | Description |
|---|---|
|
status_inout |
command return code; 0 for success and not 0 for failure |
|
 Copyright © 2001 Oracle Corporation. All Rights Reserved. |
|