| Oracle Email Application Developer's Guide Release 2 (9.0.4) Part Number B10721-01 |
|
|
View PDF |
| Oracle Email Application Developer's Guide Release 2 (9.0.4) Part Number B10721-01 |
|
|
View PDF |
The Oracle Email PL/SQL APIs are a set of application program interfaces that can be used to access and manage e-mail on the Oracle Email system.
This chapter contains the following topics:
The PL/SQL APIs described in this chapter expose Oracle Email system functionality that can be customized to suit your business and application requirements. They are organized into the following PL/SQL packages:
All the packages are part of the Oracle Collaboration Suite installation. The first three are public packages used to manipulate the e-mail user's mail account. The MAIL_RECOVERY package is a feature provided to the administrators to recovery e-mails that are accidentally deleted by the users.
The MAIL_SESSION package provides functions for authentication, logging out and user session related functionalities, such as fetching quota and usage information.
Mail folders are used to help organize e-mail messages. During e-mail user creation, the INBOX folder is created by default to contain all incoming messages. Once the messages are in the INBOX folder, the user can use mail folders to organize the messages. The MAIL_FOLDER package provides folder management and message list operations.
Folder management operations are:
Message list operations are:
The MAIL_MESSAGE package provides functions for accessing messages and creating new messages. The package enables users to do the following:
The mail_recovery package is provides functions to recover deleted e-mails, using the Oracle log miner feature. The mail_recovery package procedures are:
This section discusses the following e-mail related concepts:
When a folder is created, a validity value is assigned to the folder guaranteeing that as long as the value does not change, the message unique identifier (UID) assigned to the messages in the folder is valid. This value is called folder unique identifier validity (UIDL).
Each message in a folder is assigned a unique identifier validity (UID) value. The UID value persists across sessions, so application may cache these message UID values for message reference in the future. Message UID is always assigned in ascending order in the folder. As each message is added to the folder, it is assigned a higher UID value. The maximum UID permitted is 232 - 1 = 4 billion. Once the maximum has been reached the message UIDs in the folder are re-assigned. The holes in the message UID sequence created by messages deletion are compacted. To reflect this, the folder validity value (folder UIDL) is changed. When the folder validity value is changed, the application must discard any message UID cache for this folder and re-fetch the message UID values.
Message content cannot be modified, but mail users can change the flag property. When a message is delivered into the user's INBOX folder, the message is not marked with any flag. After the user read a message, the application marks the message with the seen flag.
The supported flags are described below. How they are used is up to the application:
New and recent messages only differ when there are multiple client sessions accessing the same mail folder at the same time. A message is considered recent when no other mail client session has retrieved the message. A message is considered new if the current mail user session has not seen the message.
A message MIME level is a string used to identify a specific part of a message. The application does not have to know anything about it. The MIME level is incorporated as part of the message and body-part objects that are passed around. It is for internal use to identify a specific message part.
There are three distinct mail objects in the Oracle Email Server:
A mail account contains many mail folders. A mail folder contains many mail messages. An INBOX mail folder is a special folder created when the mail user account is created. The INBOX folder cannot be deleted. All incoming messages are delivered into the INBOX folder.
A mail user must be authenticated before performing mail operations. The MAIL_SESSION package provides the functions. When a user is authenticated, a valid session identifier is returned from the authentication routine. This session identifier is passed around to other mail operations that require a valid authenticated session. The mail user session identifier is only valid in the same database session that it has been authenticated in. Multiple mail user sessions are supported so that multiple mail users can authenticate on the same database session. Each authenticated session is associated with a unique identifier.
To access any message in a folder, the user must open the folder. There is only one opened folder in a mail session at any given time. Once a folder is opened, all message accesses are done on the currently opened folder. The currently opened folder changes when the folder is closed or another folder is opened.
MAIL_FOLDER_OBJThe MAIL_FOLDER_OBJ object uniquely identifies a mail folder. It is defined as follows:
MAIL_FOLDER_OBJ( name VARCHAR2(1024), id NUMBER ); MAIL_FOLDER_LIST AS TABLE OF MAIL_FOLDER_OBJ;
name is the full path of the folder name.id is a unique folder identifier.MAIL_FOLDER_LIST is a nested table of MAIL_FOLDER_OBJ objects.MAIL_FOLDER_DETAILThe MAIL_FOLDER_DETAIL object contains information pertaining to a specific folder. It is defined as follows:
MAIL_FOLDER_DETAIL ( uidl NUMBER, num_voice_recent NUMBER, num_fax_recent NUMBER, total_recent NUMBER, num_voice_unseen NUMBER, num_fax_unseen NUMBER, total_unseen NUMBER, total_msgs NUMBER );
The MAIL_FOLDER_DETAIL object contains information on the folder UIDL, number of messages, total number of unseen and recent messages, and number of unseen and recent voice and fax messages.
MAIL_SORT_CRITERIA_ELEMENTThe MAIL_SORT_CRITERIA_ELEMENT object is used to represent a sort criteria. It is defined as follows:
MAIL_SORT_CRITERIA_ELEMENT( sort_header VARCHAR2(240), sort_order INTEGER ); MAIL_SORT_CRITERIA AS TABLE OF MAIL_SORT_CRITERIA_ELEMENT;
sort_header is the message header used to perform the sort. The supported header names are:
sort_order indicates whether to sort the header field in ascending or descending order. The values are:
MAIL_SORT_CRITERIA is a nested table of MAIL_SORT_CRITERIA_ELEMENT objects.MAIL_MESSAGE_OBJThe MAIL_MESSAGE_OBJ object uniquely identifies a mail message. It is defined as follows:
MAIL_MESSAGE_OBJ ( folder_id NUMBER, msg_uid NUMBER, mime_level VARCHAR2(240) ); MAIL_MESSAGE_LIST IS TABLE OF MAIL_MESSGAGE_OBJ;
folder_id is a unique folder identifier.msg_uid uniques identifies a message within the folder.mime_level either has the value 0 to indicate that this message object is a top-level message, or other value to indicate an included message object.MAIL_MESSAGE_LIST is a nested table of MAIL_MESSAGE_OBJ objects.MAIL_BODYPART_OBJThe MAIL_BODYPART_OBJ object uniquely identifes a mail message part. It is defined as follows:
MAIL_BODYPART_OBJ ( content_type VARCHAR2(240), mime_level VARCHAR2(240), folder_id NUMBER, msg_uid NUMBER, smime_ind NUMBER ); MAIL_BODYPART_LIST IS TABLE OF MAIL_BODYPART_OBJ;
folder_id is a unique folder identifier.msg_uid uniquely identifies a message within the folder.mime_level identifies the specific body-part of the message.content_type contains the main Content-Type header value for this body-part.smime_ind is a value of 1 that indicates that the body-part is from a decrypted message.mail_bodypart_list is a nested table of MAIL_BODYPART_OBJ objects.VARCHAR2_TABLEVARCHAR2_TABLE defines a nested table of a variable-length character string that has a maximum length of 2000 as follows:
VARCHAR2_TABLE IS TABLE OF VARCHAR2(2000);
MAIL_HEADER_OBJThe MAIL_HEADER_OBJ object is used to store a message header name and value pair. It is defined as follows:
MAIL_HEADER_OBJ ( header_prompt VARCHAR2(1000), header_value VARCHAR2_TABLE );
header_prompt is the name of the header.header_value is the value of the header. It is a VARCHAR2_TABLE, which is a table of 2000 variable-length character strings. When the length of header value is greater than 2000, it is broken into multiple strings of 2000 length.MAIL_HEADER_LIST is a nested table of MAIL_HEADER_OBJ objects:MAIL_HEADER_LIST is a table of MAIL_HEADER_OBJ;The mail_recovery package is provided to recover deleted e-mails, using the Oracle log miner feature.
The redo list file is used by the mail_recovery package to retrieve the list of redo logs. This file must be provided by anadministrator, and can be created manually through a text editor or by outputting a directory list command on UNIX or Windows NT.
The file contains redo log filenames with their full path, and must be listed in separate lines. The redo logs can either be online redo logs, archived logs or both.
It is important to verify that init.ora parameter UTL_FILE_DIR is set to access the redo list file. For example:
/oracle/database/redo01.log /oracle/database/redo02.log /oracle/database/redo03.log
The MAIL_RECOVERY package contains the following procedures:
The setup_logmnr procedure initializes the logminer for recovery.
PROCEDURE setup_logmnr( p_dictionary_filename IN VARCHAR2, p_redolist_location IN VARCHAR2, p_redolist_filename IN VARCHAR2 p_starttime IN DATE DEFAULT '01-jan-1988', p_endtime IN DATE DEFAULT '01-jan-2099');
The recover_messages procedure performs the recovery for a user and restores the messages in a specified folder.
PROCEDURE recover_messages( p_domainname IN VARCHAR2, p_username IN VARCHAR2, p_foldername IN VARCHAR2, p_autocommit IN BOOLEAN DEFAULT TRUE);
The MAIL_SESSION package provides user authentication and log out functionality. A user can create multiple mail sessions using the same database session by calling MAIL_SESSION.login() multiple times. Each session ID identifies one valid mail session.
The MAIL_SESSION package contains the following procedures:
This procedure authenticates a user by the user's user name and password.
mail_errors.login_err
PROCEDURE login ( user_name IN VARCHAR2, password IN VARCHAR2, domain IN VARCHAR2, ldap_host IN VARCHAR2, session_id OUT NUMBER, ldap_port IN NUMBER DEFAULT 389 ); PROCEDURE login ( user_address IN VARCHAR2, password IN VARCHAR2, ldap_host IN VARCHAR2, session_id OUT NUMBER, ldap_port IN NUMBER DEFAULT 389 );
This procedure releases all resources associated with this user session.
PROCEDURE logout ( session_id IN NUMBER );
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
This procedure returns the current user's usage amount.
mail_errors.unauthenticated_err
PROCEDURE get_current_usage ( session_id IN NUMBER, usage OUT NUMBER );
| Parameters | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
User's current usage in bytes |
This function returns true if the user is currently over-quota, and false otherwise.
mail_errors.unauthenticated_err
FUNCTION is_over_quota ( session_id IN NUMBER ) RETURN boolean; FUNCTION is_over_quota ( session_id IN NUMBER, quota OUT NUMBER, usage OUT NUMBER ) RETURN boolean;
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
User's quota in bytes |
|
|
User's current usage in bytes |
The MAIL_FOLDER package provides folder-related functionality. The validity of the session is checked before performing any operation. The search and sort features are based on the IMAP4 protocol. The search feature includes Oracle Text based searches.
The MAIL_FOLDER contains the following procedures and functions:
This procedure returns a folder object, given a folder name. If the folder does not exist on the mail store, a FOLDER_NOT_FOUND exception is raised.
mail_errors.unauthenticated_err mail_errors.folder_not_found_err
PROCEDURE get_folder_obj ( session_id IN NUMBER, folder_name IN VARCHAR2, folder_obj OUT MAIL_FOLDER_OBJ );
| Parameter | Description |
|---|---|
|
session_id |
An identifier that represents a user's authenticated session |
|
folder_name |
The full path of a folder name |
|
folder_obj |
The MAIL_FOLDER_OBJ returned |
This procedure returns a list of top-level folder objects.
mail_errors.unauthenticated_err
PROCEDURE list_toplevel_folders ( session_id IN NUMBER, folder_list OUT MAIL_FOLDER_LIST );
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
A list of top-level folder objects |
This procedure returns a list of direct child folder objects, given the parent folder.
mail_errors.unauthenticated_err mail_errors.folder_not_found_err
PROCEDURE list_folders ( session_id IN NUMBER, parent_name IN VARCHAR2, folder_list OUT MAIL_FOLDER_LIST ); PROCEDURE list_folders ( session_id IN NUMBER, parent_obj IN MAIL_FOLDER_OBJ, folder_list OUT MAIL_FOLDER_LIST );
This procedure returns a list of top-level subscribed folders.
mail_errors.unauthenticated_err
PROCEDURE list_toplevel_subdfldrs ( session_id IN NUMBER, foldername_list OUT DBMS_SQL.VARCHAR2_TABLE );
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
A list of top-level subscribed folders |
This procedure returns a list of subscribed child folders, given the parent folder.
mail_errors.unauthenticated_err
PROCEDURE list_subscribed_folders ( session_id IN NUMBER, parent_name IN VARCHAR2, foldername_list OUT DBMS_SQL.VARCHAR2_TABLE ); PROCEDURE list_subscribed_folders ( session_id IN NUMBER, parent_obj IN MAIL_FOLDER_OBJ, foldername_list OUT DBMS_SQL.VARCHAR2_TABLE );
This function tests to see if the folder is subscribed.
mail_errors.unauthenticated_err
FUNCTION is_folder_subscribed ( session_id IN NUMBER, folder_name IN VARCHAR2 ) return BOOLEAN;
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The full path of the folder name |
This procedure subscribes the specified folder. Errors are not returned if the folder has already been subscribed.
mail_errors.unauthenticated_err
PROCEDURE subscribe_folder ( session_id IN NUMBER, folder_name IN VARCHAR2 );
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The full path of the folder name |
This procedure unsubscribes the specified folder. Errors are not returned if the folder has not been subscribed at the time of the call.
mail_errors.unauthenticated_err
PROCEDURE unsubscribe_folder ( session_id IN NUMBER, folder_name IN VARCHAR2 );
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The full path of the folder name |
This function tests to see if any child folders exist.
mail_errors.unauthenticated_err
FUNCTION has_folder_children ( session_id IN NUMBER, folder_obj IN MAIL_FOLDER_OBJ ) return BOOLEAN;
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The folder object |
This procedure returns folder information, such as the folder UIDL identifier, total message count, number of unseen messages, and number of recent messages.
mail_errors.unauthenticated_err
PROCEDURE get_folder_details ( session_id IN NUMBER, folder_obj IN MAIL_FOLDER_OBJ, folder_detail_obj OUT MAIL_FOLDER_DETAIL );
This procedure creates a folder with the given name, returning a folder object representing it. A FOLDER_TYPE_ERR is returned if the parent folder does not enable any subfolder creation.
mail_errors.unauthenticated_err mail_errors.folder_already_exists_err mail_errors.folder_type_err
PROCEDURE create_folder ( session_id IN NUMBER, folder_name IN VARCHAR2, folder_obj OUT MAIL_FOLDER_OBJ );
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The full path of a folder name |
|
|
The |
This procedure deletes the specified folder. If a recursive flag is set to true, all the messages in the folder, all sub-folders and their messages, and the folder itself are removed. A recursive flag set to false performs the following actions:
OPEN_FOLDER operation on this folder can fail).DELETE_FOLDER procedure is called with a folder that is marked as not selectable and contains a subfolder, the MAIL_ERRORS.FOLDER_TYPE_ERR is thrown.mail_errors.unauthenticated_err mail_errors.operation_not_allowed mail_errors.folder_type_err mail_errors.folder_not_found_err
PROCEDURE delete_folder ( session_id IN NUMBER, folder_name IN VARCHAR2, recursive IN BOOLEAN DEFAULT false ); PROCEDURE delete_folder ( session_id IN NUMBER, folder_obj IN MAIL_FOLDER_OBJ, recursive IN BOOLEAN DEFAULT false );
This procedure renames the specified folder and returns the new folder object. Renaming the INBOX moves all of the messages in the INBOX to a new folder, leaving the INBOX folder empty.
mail_errors.unauthenticated_err mail_errors.folder_not_found_err mail_errors.folder_already_exists_err mail_errors.operation_not_allowed
PROCEDURE rename_folder ( session_id IN NUMBER, folder_name IN VARCHAR2, new_folder_name IN VARCHAR2, folder_obj OUT MAIL_FOLDER_OBJ ); PROCEDURE rename_folder ( session_id IN NUMBER, folder_obj IN OUT MAIL_FOLDER_OBJ, new_folder_name IN VARCHAR2 );
This procedure opens the specified folder and returns the folder object if the folder is opened successfully. If the folder is marked with a NOSELECT flag, a MAIL_ERRORS.FOLDER_TYPE_ERR is thrown. If a folder has already been opened prior to this call, it is closed without being expunged (messages with the DELETED message flags are not removed from that folder).
mail_errors.unauthenticated_err mail_errors.folder_not_found_err mail_errors.folder_type_err
PROCEDURE open_folder ( session_id IN NUMBER, folder_name IN VARCHAR2, folder_obj OUT MAIL_FOLDER_OBJ );
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The full path of the folder name |
|
|
The |
This procedure returns all of the messages with the specified message type in the current open folder.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
PROCEDURE get_folder_messages ( session_id IN NUMBER, message_list OUT MAIL_MESSAGE_LIST, message_type IN NUMBER DEFAULT MAIL_MESSAGE.GC_ALL_MAIL );
This procedure returns the message object corresponding to the message UID specified in the current open folder.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
PROCEDURE get _message ( session_id IN NUMBER, message_uid IN NUMBER, message_obj OUT MAIL_MESSAGE_OBJ );
This procedure closes the current open folder. If the EXPUNGE_FLAG is set to true, all messages in the folder that are marked with the Deleted flag are removed.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
PROCEDURE close_folder ( session_id IN NUMBER, expunge_flag IN BOOLEAN );
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
A flag indicating whether to expunge the folder before closing |
This procedure returns message flags belonging to the message list specified in the current open folder.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
PROCEDURE get_msg_flags ( session_id IN NUMBER, message_list IN MAIL_MESSAGE_LIST, message_flags OUT DBMS_SQL.NUMBER_TABLE ); PROCEDURE get_msg_flags ( session_id IN NUMBER, message_uid_list IN DBMS_SQL.NUMBER_TABLE, message_flags OUT DBMS_SQL.NUMBER_TABLE );
This procedure sets or unsets the message flags belonging to the list of messages specified in the current open folder.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
PROCEDURE set_msg_flags ( session_id IN NUMBER, message_list IN MAIL_MESSAGE_LIST, flags IN NUMBER, set_flag IN BOOLEAN ); PROCEDURE set_msg_flags ( session_id IN NUMBER, message_uid_list IN DBMS_SQL.NUMBER_TABLE, flags IN NUMBER, set_flag IN BOOLEAN );
This procedure deletes the list of messages specified in the current open folder. This is equivalent to marking the messages as deleted and performing an expunge operation on the folder.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
PROCEDURE delete_messages ( session_id IN NUMBER, message_uid_list IN DBMS_SQL.NUMBER_TABLE ); PROCEDURE delete_messages ( session_id IN NUMBER, message_list IN MAIL_MESSAGE_LIST );
This procedure removes all messages in the current open folder if the gc_deleted_flag flag is set.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
PROCEDURE expunge_folder ( session_id IN NUMBER, );
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
This function tests to see if the folder is the same folder currently selected in the user's session.
mail_errors.unauthenticated_err
FUNCTION is_folder_open ( session_id IN NUMBER, folder_obj IN MAIL_FOLDER_OBJ ) return BOOLEAN;
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The folder object |
This function tests to see if there are any new messages in the currently selected folder. New messages are messages not seen by the folder since the last GET_NEW_MESSAGES call. When the folder is first opened, the last message considered retrieved is the message last seen by any mail client. After that the last message is changed accordingly by calls to GET_FOLDER_MESSAGE and GET_NEW_MESSAGES.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
FUNCTION check_new_messages ( session_id IN NUMBER, message_type IN NUMBER DEFAULT MAIL_MESSAGE.GC_ALL_MAIL ) return BOOLEAN;
This function tests to see if there are any recent messages in the specified folder. Recent messages are messages that have not been retrieved by any mail client. This procedure can be called on a closed folder.
mail_errors.unauthenticated_err mail_errors.folder_not_found_err
FUNCTION check_recent_messages ( session_id IN NUMBER, folder_name IN VARCHAR2, message_type IN NUMBER DEFAULT MAIL_MESSAGE.GC_ALL_MAIL ) return BOOLEAN;
This procedure returns all the new messages in the currently selected folder. New messages are messages not seen by the folder since last message retrieval. When the folder is first opened, the last message considered retrieved is the last message seen by any mail client after the last message is changed accordingly by calls to GET_FOLDER_MESSAGES and GET_NEW_MESSAGES. If MESSAGE_TYPE is specified, then only messages in the specified type are returned.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
PROCEDURE get_new_messages ( session_id IN NUMBER, message_list OUT MAIL_MESSAGE_LIST, message_type IN NUMBER DEFAULT MAIL_MESSAGE.GC_ALL_MAIL );
This procedure copies messages in the currently selected folder to another folder. If the destination folder has the NOSELECT flag set, the MAIL_ERRORS.FOLDER_TYPE_ERR exception is thrown. If the specified message does not belong to the current open folder, the MAIL_ERRORS.PARAM_PARSE_ERR exception is thrown.
mail_errors.unauthenticated_err mail_errors.folder_closed_err mail_errors.folder_not_found_err mail_errors.folder_type_err mail_errors.param_parse_err
PROCEDURE copy_messages ( session_id IN NUMBER, message_list IN MAIL_MESSAGE_LIST, to_folder_name IN VARCHAR2 ); PROCEDURE copy_messages ( session_id IN NUMBER, message_uid_list IN DBMS_SQL.NUMBER_TABLE, to_folder_name IN VARCHAR2 );
This function tests to see if any messages in the currently selected folder have been modified from another session. A folder is modified if any changes in message flags or deletion of messages were made in the folder by another client or session. If the folder was modified, users have to re-issue the GET_NEW_MESSAGES procedure to be synchronized with the mail store.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
FUNCTION is_folder_modified ( session_id IN NUMBER );
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
This procedure sorts the folder given the sort criteria and returns an ordered list of message UIDs.
mail_errors.unauthenticated_err mail_errors.param_parse_err
PROCEDURE sort_folder ( session_id IN NUMBER, folder_obj IN MAIL_FOLDER_OBJ, sort_criteria IN MAIL_SORT_CRITERIA, message_uid_list OUT DBMS_SQL.NUMBER_TABLE ); PROCEDURE sort_folder ( session_id IN NUMBER, folder_obj IN MAIL_FOLDER_OBJ, sort_criteria IN MAIL_SORT_CRITERIA, message_list OUT MAIL_MESSAGE_LIST );
This procedure searches the folder and returns a list of message objects that meet the specified criteria. The format of the search criteria is the same as the format in the IMAP4 protocol [RFC 2060], except when specifying to search within a set of messages, the set is passed in as a parameter.
mail_errors.unauthenticated_err mail_errors.param_parse_err
PROCEDURE search_folder ( session_id IN NUMBER, folder_obj IN MAIL_FOLDER_OBJ, search_criteria IN VARCHAR2, message _list OUT MAIL_MESSAGE_LIST ); PROCEDURE search_folder ( session_id IN NUMBER, folder_obj IN MAIL_FOLDER_OBJ, search_criteria IN VARCHAR2, in_message_list IN MAIL_MESSAGE_LIST, message_list OUT MAIL_MESSAGE_LIST );
The MAIL_MESSAGE package provides message retrieval, message composition, and Oracle Text related functionality. The validity of the session is checked before performing any operation, except for composing send operations. Users can only compose one message at a time, and a MSG_COMPOSE_LIMIT_ERR is thrown if a violation occurs.
The MAIL_MESSAGE package contains the following message retreival procedures:
The MAIL_MESSAGE package contains the following message composition procedures:
The MAIL_MESSAGE package contains the following Oracle Text procedures:
This procedure returns a message object given the message UID in the current open folder.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
PROCEDURE get_message_obj ( session_id IN NUMBER, message_uid IN NUMBER, message_obj OUT MAIL_MESSAGE_OBJ);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The message UID |
|
|
The |
This procedure returns a message object representing the included message. The message type of the specified message or body-part object must be Content-Type; otherwise the MAIL_ERRORS.PARAM_PARSE_ERR exception is thrown.
mail_errors.unauthenticated_err mail_errors.param_parse_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_included_message ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, incl_message_obj OUT MAIL_MESSAGE_OBJ); PROCEDURE get_included_message ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, incl_message_obj OUT MAIL_MESSAGE_OBJ);
This procedure returns the corresponding header value given in the header prompt.
If a message object is passed in, the header value refers to the top-level message header.
If a body-part object is passed in, the header value refers to that specific body-part header.
When the header prompt is not found in the headers, a null value is returned. When the returned value is a VARCHAR2 type, it is truncated to 2000 in length if the length of the value is longer than 2000. If there are multiple headers with the same prompt, the returned value is the first header. When the returnedvalue is a MAIL_HEADER_LIST object list, all headers with the specified prompt name are returned. Each name and value pair is represented by a MAIL_HEADER_OBJ object.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_header ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, header_prompt IN VARCHAR2, header_value OUT VARCHAR2); PROCEDURE get_header ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, header_prompt IN VARCHAR2, header_value OUT VARCHAR2); PROCEDURE get_header ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, header_list OUT MAIL_HEADER_LIST); PROCEDURE get_header ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, header_list OUT MAIL_HEADER_LIST);
This procedure returns all of the header values of the given message part. If a message object is passed in, the header value refers to the top-level message header.
If a body-part object is passed in, the header value is referred to that specific body-part header.
When the returned value is a dbms_sql.varchar2_table type, all header values are truncated to 2000 in length if the length of the value is longer than 2000. When the headers are returned in a MAIL_HEADER_LIST object list, each name and value pair is represented by a MAIL_HEADER_OBJ object.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_headers ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, header_prompts IN dbms_sql.varchar2_table, header_values OUT dbms_sql.varchar2_table); PROCEDURE get_headers ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, header_prompts IN dbms_sql.varchar2_table, header_values OUT dbms_sql.varchar2_table); PROCEDURE get_headers ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, header_list OUT MAIL_HEADER_LIST); PROCEDURE get_headers ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, header_list OUT MAIL_HEADER_LIST);
This procedure is used to obtain the Content-Type header value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_content_type ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, content_type OUT VARCHAR2); PROCEDURE get_content_type ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, content_type OUT VARCHAR2);
This procedure is used to obtain the Reply-To header value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_reply_to ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, replyTo_str OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The message object |
|
|
The Reply-To header value |
This procedure is used to obtain the Date header value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_sent_date ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, sent_date OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The message object |
|
|
The Date header value |
This procedure is used to obtain the Subject header value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_subject ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, subject_str OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The message object |
|
|
The subject header value |
This procedure is used to obtain the From header value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_from ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, from_str OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The message object |
|
|
The From header value |
This procedure is used to obtain the Message-ID header value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_messageID ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, messageID_str OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The message object |
|
|
The Message-ID header value |
This procedure is used to obtain the Content-ID header value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_contentID ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, contentID_str OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The body-part object |
|
|
The Content-ID header value |
This procedure is used to obtain the Content-Language header value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_contentLang ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, language OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The body-part object |
|
|
The Content-Language header value |
This procedure is used to obtain the Content-MD5 header value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_contentMD5 ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, md5 OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The body-part object |
|
|
The Content-MD5 header value |
This procedure is used to obtain the Content-Type header value, and extract the CHARSET attribute value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_charset ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, charset OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The body-part object |
|
|
The character set attribute value |
This procedure is used to obtain the Content-Disposition header value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_contentDisp ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, disposition OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The body-part object |
|
|
The Content-Disposition header value |
This procedure is used to obtain the Content-Transfer-Encoding header value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_encoding ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, encoding OUT VARCHAR2); PROCEDURE get_encoding ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, encoding OUT VARCHAR2);
This procedure is used to obtain the Content-Disposition header value and extract the filename attribute value. It internally calls the GET_HEADER procedure with the specific header prompt.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_content_filename ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, filename OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The body-part object |
|
|
The filename attribute value |
This procedure returns the message size.
mail_errors.unauthenticated_err
PROCEDURE get_msg_id ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, message_size OUT NUMBER);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The message object |
|
|
The message size |
This procedure is used to obtain the time the message is received at the mail store.
mail_errors.unauthenticated_err mail_errors.bad_message_var mail_errors.bad_msgpart_var
PROCEDURE get_rcvd_date ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, date_format IN VARCHAR2, date_str OUT VARCHAR2); PROCEDURE get_rcvd_date ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, received_date OUT DATE);
This procedure returns the size of the body-part.
mail_errors.unauthenticated_err mail_errors.bad_message_var
PROCEDURE get_bodypart_size ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, bodypart_size OUT NUMBER);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The body-part object |
|
|
The body-part size |
This procedure returns the line count of the body-part.
mail_errors.unauthenticated_err mail_errors.bad_message_var
PROCEDURE get_content_linecount ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, line_count OUT NUMBER);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The body-part object |
|
|
The total number of lines in the body-part |
This procedure returns a list of body-parts that belong to the specified multipart message or body-part. If the message or body-part object passed in is not of a multipart MIME type, a PARAM_PARSE_ERR exception is raised.
mail_errors.unauthenticated_err mail_errors.param_parse_err mail_errors.bad_message_var
PROCEDURE get_multipart_bodyparts ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, bodypart_list OUT MAIL_BODYPART_LIST); PROCEDURE get_multipart_bodyparts ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, bodypart_list OUT MAIL_BODYPART_LIST);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The message object |
|
|
The body-part object |
|
|
A list of body-parts |
This procedure returns a BLOB locator to the entire encoded message. Storage does not needs to be allocated beforehand.
mail_errors.unauthenticated_err
PROCEDURE get_msg ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, message_source OUT BLOB);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The message object |
|
|
The whole message content in its original encoded form |
This procedure copies the message body into the specified BLOB locator. The locator must have enough storage for the data. If the message is not a simple MIME type, no data is returned. If the message body's Content-Transfer-Encoding header specifies that the data is encoded, using base64 or quoted-printable encodings, the content is decoded before returning.
mail_errors.unauthenticated_err mail_errors.bad_message_var
PROCEDURE get_msg_body ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, content OUT BLOB);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The message object |
|
|
The entire decoded message content |
This procedure copies the content of the body-part into the specified BLOB locator. If the body-part object is not a simple MIME type, no data is returned. If the body-part's Content-Transfer-Encoding header specifies that the data is encoded, using base64 or quoted-printable encodings, the content is decoded before returning.
mail_errors.unauthenticated_err mail_errors.bad_message_var
PROCEDURE get_bodypart_content ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, content OUT BLOB);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The body-part object |
|
|
The entire decoded message content |
This procedure returns the message's flags.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
PROCEDURE get_msgs_flags ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, message_flags OUT NUMBER);
This procedure sets and unsets the message flags for the specified message object.
mail_errors.unauthenticated_err mail_errors.folder_closed_err
PROCEDURE set_msgs_flags ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, message_flags IN NUMBER, set_flag IN BOOLEAN);
This procedure returns authenticated user information, if available. The authenticated user information is stored when a user authenticates before sending an e-mail.
mail_errors.unauthenticated_err
PROCEDURE get_auth_info ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, auth_info OUT VARCHAR2);
| Parameter | Description |
|---|---|
|
|
An identifier that represents a user's authenticated session |
|
|
The message object |
|
|
The authenticated user information |
This procedure initializes a message composition. There can be at most one message in composition at any given time.
mail_errors.msg_compose_limit_err mail_errors.param_parse_err
PROCEDURE compose_message ( message_obj OUT MAIL_MESSAGE_OBJ);
| Parameter | Description |
|---|---|
|
|
A message object |
This procedure sets a list of common message headers. If null is specified, the header is not included. If the sent date is null, it is set to the current time.
mail_errors.msg_compose_limit_err
PROCEDURE set_msgheader ( message_obj IN MAIL_MESSAGE_OBJ, to_str IN VARCHAR2, from_str IN VARCHAR2, cc_str IN VARCHAR2 DEFAULT null, replyto_str IN VARCHAR2 DEFAULT null, sent_date IN DATE DEFAULT null, subject_str IN VARCHAR2 DEFAULT null, mime_version IN VARCHAR2 DEFAULT `1.0', content_type IN VARCHAR2 DEFAULT `text/plain', charset IN VARCHAR2 DEFAULT `us-ascii', encoding IN VARCHAR2 DEFAULT `8bit');
This procedure sets a list of common body-part headers. If null is specified, the header is not included. All header values are limited to 2000 in length; if it exceeds the limit, a MAIL_ERRORS.PARAM_PARSE_ERR is thrown.
mail_errors.msg_compose_limit_err mail_errors.param_parse_err
PROCEDURE set_bpheader ( bodypart_obj IN MAIL_BODYPART_OBJ, content_type IN VARCHAR2 DEFAULT `text/plain', charset IN VARCHAR2 DEFAULT `us-ascii', encoding IN VARCHAR2 DEFAULT `8bit', contentID IN VARCHAR2 DEFAULT null, language IN VARCHAR2 DEFAULT null, contentMD5 IN VARCHAR2 DEFAULT null, description IN VARCHAR2 DEFAULT null, disposition IN VARCHAR2 DEFAULT `inline', filename IN VARCHAR2 DEFAULT null);
This procedure sets the header value, given the header prompt. This does not override any previous headers; it adds to them. All header values are limited to 2000 in length; if it exceeds the limit, a MAIL_ERRORS.PARAM_PARSE_ERR is thrown.
mail_errors.msg_compose_limit_err mail_errors.param_parse_err
PROCEDURE set_header ( message_obj IN MAIL_MESSAGE_OBJ, header_prompt IN VARCHAR2, header_value IN VARCHAR2); PROCEDURE set_header ( bodypart_obj IN MAIL_BODYPART_OBJ, header_prompt IN VARCHAR2, header_value IN VARCHAR2);
| Parameter | Description |
|---|---|
|
|
The message object |
|
|
The body-part object |
|
|
The message or body-part header |
|
|
The corresponding header value |
This procedure adds a child body-part to the specified parent message or body-part of Content-Type multipart. If the parent message or body-part object is not the same type of multipart message, a PARAM_PARSE_ERR exception is thrown.
mail_errors.msg_compose_limit_err mail_errors.param_parse_err
PROCEDURE add_bodypart ( parent_message_obj IN MAIL_MESSAGE_OBJ, bodypart_obj OUT MAIL_BODYPART_OBJ); PROCEDURE add_bodypart ( parent_bodypart_obj IN MAIL_BODYPART_OBJ, bodypart_obj OUT MAIL_BODYPART_OBJ);
| Parameter | Description |
|---|---|
|
|
The parent message object |
|
|
The parent body-part object |
|
|
The new child body-part object returned |
This procedure adds a new included message to the specified parent message or body-part of Content-Type message. If the parent message or body-part object is not the same type of message, a PARAM_PARSE_ERR exception is thrown.
mail_errors.msg_compose_limit_err mail_errors.param_parse_err
PROCEDURE add_inclmsg_bodypart ( parent_message_obj IN MAIL_MESSAGE_OBJ, message_obj OUT MAIL_MESSAGE_OBJ); PROCEDURE add_inclmsg_bodypart ( parent_bodypart_obj IN MAIL_BODYPART_OBJ, message_obj OUT MAIL_MESSAGE_OBJ);
| Parameter | Description |
|---|---|
|
|
The parent message object |
|
|
The parent body-part object |
|
|
The new included message object returned |
This procedure sets an included message to the specified parent message or body-part of Content-Type message. The included message must already exist in the mail store. If the parent message or body-part object is not the same type of message, a PARAM_PARSE_ERR exception is thrown.
mail_errors.msg_compose_limit_err mail_errors.param_parse_err
PROCEDURE set_inclmsg_bodypart ( parent_message_obj IN MAIL_MESSAGE_OBJ, message_obj IN MAIL_MESSAGE_OBJ); PROCEDURE set_inclmsg_bodypart ( parent_bodypart_obj IN MAIL_BODYPART_OBJ, message_obj IN MAIL_MESSAGE_OBJ);
| Parameter | Description |
|---|---|
|
|
The parent message object |
|
|
The parent body-part object |
|
|
An existing message in mail store |
This procedure sets the message or body-part content for the message in the composition. If the message or body-part is not a simple MIME type, a PARAM_PARSE_ERR is thrown. This procedure can be called multiple times. The data is connected together. The data should be in decoded form. When the composed message is sent or appended, the data is encoded according to the Content-Transfer-Encoding header specified for this part of the data.
mail_errors.msg_compose_limit_err mail_errors.param_parse_err
PROCEDURE set_content ( message_obj IN MAIL_MESSAGE_OBJ, content IN RAW); PROCEDURE set_content ( message_obj IN MAIL_MESSAGE_OBJ, content IN BLOB); PROCEDURE set_content ( bodypart_obj IN MAIL_BODYPART_OBJ, content IN RAW); PROCEDURE set_content ( bodypart_obj IN MAIL_BODYPART_OBJ, content IN BLOB);
| Parameter | Description |
|---|---|
|
|
The message object |
|
|
The body-part object |
|
|
The message or body-part content |
This procedure sends the message currently in composition. The message can also be sent encrypted, signed, or both.
mail_errors.msg_compose_limit_err mail_errors.smime_err
PROCEDURE send_message ( message_obj IN MAIL_MESSAGE_OBJ); PROCEDURE send_message ( message_obj IN MAIL_MESSAGE_OBJ, ceritificate IN RAW, private_key IN RAW, recipients IN MAIL_MESSAGE.RAW_TABLE, inclOrigCert IN BOOLEAN, inclOrigAsRecip IN BOOLEAN, digest_algorithm IN BINARY_INTEGER, sign_algorithm IN BINARY_INTEGER, encrypt_algorithm IN BINARY_INTEGER, send_option IN NUMBER);
This procedure appends the current message composition to the specified folder. The user must be authenticated and the folder must belong to the user.
mail_errors.unathenticated_err mail_errors.msg_compose_limit_err
PROCEDURE append_message ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, folder_name IN VARCHAR2, received_date IN DATE DEFAUL null, message_flags IN NUMBER DEFAULT 0); PROCEDURE append_message ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, folder_obj IN MAIL_FOLDER_OBJ, received_date IN DATE DEFAUL null, message_flags IN NUMBER DEFAULT 0);
This procedure decrypts a S/MIME message and returns a list of body-parts that belongs to the encrypted part.
mail_errors.unauthenticated_err mail_errors.smime_err
PROCEDURE decrypt_message ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, ceritificate IN RAW, private_key IN RAW, bodypart_list OUT MAIL_BODYPART_LIST ); PROCEDURE decrypt_message ( session_id IN NUMBER, bodypart_obj IN MAIL_MESSAGE_OBJ, ceritificate IN RAW, private_key IN RAW, bodypart_list OUT MAIL_BODYPART_LIST );
This procedure verifies a digitally signed message.
mail_errors.unauthenticated_err mail_errors.smime_err
PROCEDURE verify_message ( session_id IN NUMBER, ceritificate IN RAW, private_key IN RAW, original_content IN BLOB, certificate_list IN ES_CERT_LIST, signature IN BLOB, retruned_content OUT BLOB );
This procedure retrieves the theme for the message or body-part. If a message object is specified, the entire message is processed. If a body-part object is specified, the part must be a simple type and not contain any other body-parts.
mail_errors.unauthenticated_err mail_errors.sql_err mail_erros.imt_err mail_errors.bad_message_var mail_errors.bad_msgpart_var mail_errors.no_binary_err
PROCEDURE get_themes ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, flags IN INTEGER, incl_binary_parts IN BOOLEAN, theme_buffer OUT ES_OT_API.THEME_TABLE); PROCEDURE get_themes ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, flags IN INTEGER, incl_binary_parts IN BOOLEAN, theme_buffer OUT ES_OT_API.THEME_TABLE);
This procedure retrieves the highlights for the message or body-part. If a message object is specified, the entire message is processed. If a body-part object is specified, the part must be a simple type and not contain any other body-parts.
mail_errors.unauthenticated_err mail_errors.sql_err mail_erros.imt_err mail_errors.bad_message_var mail_errors.bad_msgpart_var mail_errors.no_binary_err
PROCEDURE get_highlight ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, flags IN INTEGER, text_query IN VARCHAR2, incl_binary_parts IN BOOLEAN, highlight_buffer OUT ES_OT_API.HIGHLIGHT_TABLE); PROCEDURE get_highlight ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, flags IN INTEGER, text_query IN VARCHAR2, incl_binary_parts IN BOOLEAN, highlight_buffer OUT ES_OT_API.HIGHLIGHT_TABLE);
This procedure retrieves the mark-up text for the message or body-part. If a message object is specified, the entire message is processed. If a body-part object is specified, the part must be a simple type and not contain any other body-parts.
mail_errors.unauthenticated_err mail_errors.sql_err mail_erros.imt_err mail_errors.bad_message_var mail_errors.bad_msgpart_var mail_errors.no_markup mail_errors.no_binary_err
PROCEDURE get_markuptext ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, flags IN INTEGER, text_query IN VARCHAR2, incl_binary_parts IN BOOLEAN, tag_set IN VARCHAR2 DEFAULT `TEXT_DEFUALT', start_tag IN VARCHAR2 DEFAULT NULL, end_tag IN VARCHAR2 DEFAULT NULL, prev_tag IN VARCHAR2 DEFAULT NULL, next_tag IN VARCHAR2 DEFAULT NULL, buffer OUT CLOB); PROCEDURE get_markuptext ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, flags IN INTEGER, text_query IN VARCHAR2, incl_binary_parts IN BOOLEAN, tag_set IN VARCHAR2 DEFAULT `TEXT_DEFUALT', start_tag IN VARCHAR2 DEFAULT NULL, end_tag IN VARCHAR2 DEFAULT NULL, prev_tag IN VARCHAR2 DEFAULT NULL, next_tag IN VARCHAR2 DEFAULT NULL, buffer OUT CLOB);
This procedure retrieves the filtered text for the message or body-part. If a message object is specified, the entire message is processed. If a body-part object is specified, the part must be a simple type and not contain any other body-parts.
mail_errors.unauthenticated_err mail_errors.sql_err mail_erros.imt_err mail_errors.bad_message_var mail_errors.bad_msgpart_var mail_errors.no_binary_err
PROCEDURE get_filtered_text ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, flags IN INTEGER, incl_binary_parts IN BOOLEAN, buffer OUT CLOB); PROCEDURE get_filtered_text ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, flags IN INTEGER, incl_binary_parts IN BOOLEAN, buffer OUT CLOB);
This procedure retrieves the tokens for the message or body-part. If a message object is specified, the entire message is processed. If a body-part object is specified, the part must be a simple type and not contain any other body-parts.
mail_errors.unauthenticated_err mail_errors.sql_err mail_erros.imt_err mail_errors.bad_message_var mail_errors.bad_msgpart_var mail_errors.no_binary_err
PROCEDURE get_tokens ( session_id IN NUMBER, message_obj IN MAIL_MESSAGE_OBJ, language IN VARCHAR2, incl_binary_parts IN BOOLEAN, token_buffer OUT ES_OT_API.TOKEN_TABLE); PROCEDURE get_tokens ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, language IN VARCHAR2, incl_binary_parts IN BOOLEAN, token_buffer OUT ES_OT_API.TOKEN_TABLE);
This section describes the following exceptions for the PL/SQL APIs:
Error No: 20001
Message: Error executing external rule
Cause: A rule defined as an external PL/SQL procedure failed during execution
Action: Check the correctness of the external PL/SQL procedure
Error No: 20002
Message: External condition failed
Cause: A condition defined as an external PL/SQL function failed during evaluation
Action: Check the correctness of the external PL/SQL function
Error No: 20003
Message: Too many rules fired, stop
Cause: Number of rules triggered for a message exceeded the maximum number of rules allowed. The default is 20
Action:
Error No: 20101
Message: Some SQL error occured: %s
Cause: An SQL error occured. See the error text returned for more information
Action: Refer to the Oracle9i Database Error Messages guide
Error No: 20102
Message: interMedia Text error: %s
Cause: Internal Oracle Text error occured. See the error text returned for more information
Action: Refer to the Oracle Text documentation
Error No: 20103
Message: No such message: %s
Cause: Exception is raised when the message or body-part object passed in is invalid. The message may have been removed by another mail session
Action: Ensure that the message or body-part object passed in is valid
Error No: 20104
Message: No such message part: %s
Cause: Exception is raised when the MIME-level of the message or body-part object passed in is invalid
Action: Ensure that the MIME level of the message or body-part object passed in is valid
Error No: 20106
Message: No binary part: %s
Cause: Exception is raised when the specified message part is binary, but the API option specifies that binary is false
Action: Set the withbinary parameter to True for Oracle Text
Error No: 20201
Message: User needs to be authenticated first!
Cause: User is not currently authenticated
Action: Call the mail_session.login() procedure to authenticate the user
Error No: 20202
Message: Folder needs to be opened first!
Cause: Certain operations require that the folder involved be opened first
Action: Call the mail_folder.open_folder() procedure to open the folder first
Error No: 20203
Message: Compose one message at a time!
Cause: Exception is raised if user tries to compose more than one message at a time
Action: Send or append the current message before starting to compose the second message
Error No: 20204
Message: Folder does not exist: %s
Cause: Exception is raised if user is trying to do an operation on a folder that does not exist on the mail store
Action: Ensure that the folder exists before performing the operation
Error No: 20205
Message: Folder already exists: %s
Cause: Exception is raised if user tries to create or rename as a folder name that already exists
Action: Choose a different folder name and retry
Error No: 20206
Message: Operation not allowed: %s
Cause: Possible causes for this exception are:
Action: Do not try to re-arrange a folder hierachy with the rename operation. Use the create and delete operations to achieve the desired folder hierachy
Error No: 20208
Message: Param parsing error: %s
Cause: Errors in the parameter passed into the API. Possible causes are:
Action: Correct the parameter passed to the API and try again
Error No: 20209
Message: Internal error: %s
Cause: An internal assertion has failed. Data is in an inconsistent state
Action: Contact Oracle Support
Error No: 20210
Message: Bad folder name: %s
Cause: Trying to create or rename a folder under another user's name space
Action: Correct the folder name and try again
Error No: 20211
Message: Oracle Internet Directory Login Error: %s
Cause: Exception is raised when an invalid username or password is specified
Action: Check the spelling and try again
Error No: 20212
Message: Folder type voilation: %s
Cause: Possible cause for this error includes:
Action: Avoid these types of folder violations
Error No: 20213
Message: S/MIME error: %s
Cause: Some error in calling S/MIME functions. The S/MIME error code reveals more details.
Action: Refer to the S/MIME documentation for the error code descriptions
This section gives examples of how to log in and use the create, list, search, fetch, compose, send functions on folders. It also has an example of how to use the GetTheme API.
This section contains the following topics:
This example shows how to log in, create, list, and search on a folder.
set serveroutput on size 1000000; DECLARE user_name VARCHAR2(50) := 'testuser1'; user_pswd VARCHAR2(50):= 'welcome'; user_domain VARCHAR2(50) := 'oracle.com'; ldap_server VARCHAR2(50) := 'test-sun.us.oracle.com'; ldap_port NUMBER := 389; sessionID NUMBER; my_folder_obj MAIL_FOLDER_OBJ; my_folder_list MAIL_FOLDER_LIST; inbox_obj MAIL_FOLDER_OBJ; search_string VARCHAR2(500); message_list MAIL_MESSAGE_LIST; -- declare and define two procedures to recursively print out all folders PROCEDURE print_folder(session_id IN NUMBER, folder_obj IN MAIL_FOLDER_OBJ); PROCEDURE print_folderlist(session_id IN NUMBER, flist IN MAIL_FOLDER_LIST); PROCEDURE print_folder(session_id IN NUMBER, folder_obj IN MAIL_FOLDER_OBJ) IS flist MAIL_FOLDER_LIST; BEGIN -- print out the folder name dbms_output.put_line(folder_obj.name); -- if there are child-folder, print them too if mail_folder.has_folder_children(session_id, folder_obj) then -- get all the child-folders and prin them mail_folder.list_folders(session_id, folder_obj, flist); print_folderlist(flist); end if; END; PROCEDURE print_folderlist(session_id IN NUMBER, flist IN MAIL_FOLDER_LIST) IS BEGIN for i in 1..flist.count loop print_folder(session_id, flist(i)); end loop; END; BEGIN -- login mail_session.login(user_name, user_pswd, user_domain, ldap_server, sessionID, ldap_port); dbms_output.put_line(user_name || ' logged-in'); -- create a new folder called my_folder mail_folder.create_folder(sessionID, 'my_folder', my_folder_obj); dbms_output.put_line('Created my_folder folder'); -- list all folders mail_folder.list_toplevel_folders(sessionID, my_folder_list); dbms_output.put_line('My folder list:'); print_folderlist(sessionID, my_folder_list); -- open INBOX mail_folder.open_folder(sessionID, 'INBOX', inbox_obj); dbms_output.put_line('Opened INBOX'); -- search on INBOX for mails from Crosby with subject containing the word -- White Christmas search_string := 'from Crosby subject White Christmas'; mail_folder.search_folder(sessionID, inbox_obj, search_string, message_list); dbms_output.put_line('Search for ' || search_string || ' returns:'); FOR i IN 1..message_list.COUNT LOOP dbms_output.put(message_list(i).msg_uid || ' '); END LOOP; dbms_output.new_line; -- logout mail_session.logout(sessionID); dbms_output.put_line('Elvis has left the building!'); -- commit commit; EXCEPTION WHEN mail_errors.login_err THEN dbms_output.put_line('Failed to log-in!!!'); dbms_output.put_line('SQLcode: ' || sqlcode); dbms_output.put_line('SQLerrm: ' || sqlerrm); rollback; WHEN mail_errors.folder_already_exists_err OR mail_errors.folder_type_err THEN dbms_output.put_line('Failed to create folder!!!'); dbms_output.put_line('SQLcode: ' || sqlcode); dbms_output.put_line('SQLerrm: ' || sqlerrm); rollback; WHEN OTHERS THEN dbms_output.put_line('SQLcode: ' || sqlcode); dbms_output.put_line('SQLerrm: ' || sqlerrm); rollback; END; /
This example shows how to log in and fetch all messages from a folder.
set serveroutput on size 1000000; DECLARE user_name VARCHAR2(50) := 'testuser1'; user_pswd VARCHAR2(50):= 'welcome'; user_domain VARCHAR2(50) := 'oracle.com'; ldap_server VARCHAR2(50) := 'test-sun.us.oracle.com'; ldap_port NUMBER := 389; sessionID NUMBER; inbox_obj MAIL_FOLDER_OBJ; message_list MAIL_MESSAGE_LIST; -- declare and define some sub-programs to print out a message PROCEDURE print_message (sessionId IN NUMBER, p_msg_obj IN MAIL_MESSAGE_OBJ); PROCEDURE print_bodypart (sessionId IN NUMBER, p_bp_obj IN MAIL_BODYPART_OBJ); PROCEDURE print_header (sessionId IN NUMBER, p_msg_obj IN MAIL_MESSAGE_OBJ); PROCEDURE print_header (sessionId IN NUMBER, p_bp_obj IN MAIL_BODYPART_OBJ); PROCEDURE print_content (sessionId IN NUMBER, p_msg_obj IN MAIL_MESSAGE_OBJ, p_content_type IN varchar2); PROCEDURE print_content (sessionId IN NUMBER, p_bp_obj IN MAIL_BODYPART_OBJ, p_content_type IN varchar2); PROCEDURE print_message (sessionId IN NUMBER, p_msg_obj IN MAIL_MESSAGE_OBJ) IS bp_list MAIL_BODYPART_LIST; content_type varchar2(500); incl_msg MAIL_MESSAGE_OBJ; BEGIN -- print out the message header print_header(sessionId, p_msg_obj); -- get the message's content-type mail_message.get_content_type(sessionId, p_msg_obj, content_type); content_type := upper(content_type); -- if it is a multipart type, get each of its body-parts and print -- it out one by one if content_type like 'MULTIPART/%' then mail_message.get_multipart_bodyparts(sessionId, p_msg_obj, bp_list); for i in 1..bp_list.count loop print_bodypart(sessionId, bp_list(i)); end loop; -- if it is a message type, get the included message and print -- the included message elsif content_type like 'MESSAGE/%' then mail_message.get_included_message(sessionId, p_msg_obj, incl_msg); print_message(sessionId, incl_msg); -- if it is a simple type, print the content else print_content(sessionId, p_msg_obj, content_type); end if; END; PROCEDURE print_bodypart (sessionId IN NUMBER, p_bp_obj IN MAIL_BODYPART_OBJ) IS bp_list MAIL_BODYPART_LIST; content_type varchar2(500); incl_msg MAIL_MESSAGE_OBJ; BEGIN -- print out the body-part header print_header(sessionId, p_bp_obj); -- get the body-part's content-type mail_message.get_content_type(sessionId, p_bp_obj, content_type); content_type := upper(content_type); -- if it is a multipart type, get each of its body-parts and print -- it out one by one if content_type like 'MULTIPART/%' then mail_message.get_multipart_bodyparts(sessionId, p_bp_obj, bp_list); for i in 1..bp_list.count loop print_bodypart(sessionId, bp_list(i)); end loop; -- if it is a message type, get the included message and print -- the included message elsif content_type like 'MESSAGE/%' then mail_message.get_included_message(sessionId, p_bp_obj, incl_msg); print_message(sessionId, incl_msg); -- if it is a simple type, print the content else print_content(sessionId, p_bp_obj, content_type); end if; END; -- -- private procedure to print header given a MAIL_HEADER_LIST object -- PROCEDURE print_hdrlist (hdr_list IN MAIL_HEADER_LIST) BEGIN dbms_output.put_line('[# message hdr: ' || hdr_list.count || ']'); for i in 1..hdr_list.count loop dbms_output.put(hdr_list(i).header_prompt || ': '); for j in 1..hdr_list(i).header_value.count loop dbms_output.put_line(hdr_list(i).header_value(j)); end loop; end loop; END; PROCEDURE print_header (sessionId IN NUMBER, p_msg_obj IN MAIL_MESSAGE_OBJ) IS hdr_list MAIL_HEADER_LIST; msg_size NUMBER; msg_flags NUMBER; msg_rcvd_date VARCHAR2(50); BEGIN if p_msg_obj.mime_level = '0' then -- top level message mail_message.get_msg_size(sessionId, p_msg_obj, msg_size); mail_message.get_msg_flags(sessionId, p_msg_obj, msg_flags); mail_message.get_received_date(sessionId, p_msg_obj, 'DD-Mon-YYYY HH24:MI:SS', msg_rcvd_date); dbms_output.put_line('[MSG_SIZE:' || msg_size || ']' || ' [MSG_FLAG:' || msg_flags || ']'); dbms_output.put_line('[message header]:'); else dbms_output.put_line('[included message header]:'); end if; -- get all the headers for this message and print them mail_message.get_headers(sessionId, p_msg_obj, hdr_list); print_hdrlist(hdr_list); if p_msg_obj.mime_level = '0' then -- top level message dbms_output.put_line('Received: '|| msg_rcvd_date); end if; dbms_output.new_line; END; PROCEDURE print_header (sessionId IN NUMBER, p_bp_obj IN MAIL_BODYPART_OBJ) IS hdr_list MAIL_HEADER_LIST; BEGIN -- get all the headers for this body-part and print them dbms_output.put_line('[bodypart header]:'); mail_message.get_headers(sessionId, p_bp_obj, hdr_list); print_hdrlist(hdr_list); dbms_output.new_line; END; PROCEDURE print_content (sessionId IN NUMBER, p_msg_obj IN MAIL_MESSAGE_OBJ, p_content_type IN varchar2) IS msg_data BLOB; l_data RAW(255); l_length NUMBER; l_offset NUMBER; l_size NUMBER; BEGIN -- create a temporary lob to hold the message body dbms_lob.createtemporary(msg_data, TRUE, dbms_lob.session); -- get the decoded message body mail_message.get_msg_body(sessionId, p_msg_obj, msg_data); l_length := dbms_lob.getlength(msg_data); dbms_output.put_line('[read ' || l_length || ']'); dbms_output.new_line; -- print the decoded message body if it is a text type if p_content_type like 'TEXT/%' then l_offset := 1; while l_offset <= l_length loop -- sqlplus has a limitation of 255 characters per line. -- break the content into 250-character chunks and print. if (l_length - l_offset + 1) <= 250 then l_size := l_length - l_offset + 1; else l_size := 250; end if; l_data := dbms_lob.substr(msg_data, l_size, l_offset); l_offset := l_offset + l_size; dbms_output.put_line(utl_raw.cast_to_varchar2(l_data)); end loop; end if; -- free the temporary lob allocated dbms_lob.freetemporary(msg_data); END; PROCEDURE print_content (sessionId IN NUMBER, p_bp_obj IN MAIL_BODYPART_OBJ, p_content_type IN varchar2) IS bp_data BLOB; l_data RAW(255); l_length NUMBER; l_offset NUMBER; l_size NUMBER; BEGIN -- create a temporary lob to hold the body-part content dbms_lob.createtemporary(bp_data, TRUE, dbms_lob.session); -- get the decoded body-part content mail_message.get_bodypart_content(sessionId, p_bp_obj, bp_data); l_length := dbms_lob.getlength(bp_data); dbms_output.put_line('[read ' || l_length || ']'); dbms_output.new_line; -- print the decoded data if it is a text type if p_content_type like 'TEXT/%' then l_offset := 1; while l_offset <= l_length loop -- sqlplus has a limitation of 255 characters per line. -- break the content into 250-character chunks and print. if (l_length - l_offset + 1) <= 250 then l_size := l_length - l_offset + 1; else l_size := 250; end if; l_data := dbms_lob.substr(bp_data, l_size, l_offset); l_offset := l_offset + l_size; dbms_output.put_line(utl_raw.cast_to_varchar2(l_data)); end loop; end if; -- free the temporary lob allocated dbms_lob.freetemporary(bp_data); END; -- define a bit_on function FUNCTION bit_on ( p_flag IN INTEGER, p_bit IN INTEGER ) RETURN INTEGER IS BEGIN IF bitand(p_flag, p_bit) = 0 THEN -- bit not turned on yet RETURN p_flag + p_bit; ELSE -- bit already on RETURN p_flag; END IF; END; BEGIN -- login mail_session.login(user_name, user_pswd, user_domain, ldap_server, sessionID, ldap_port); dbms_output.put_line(user_name || ' logged-in'); -- open INBOX mail_folder.open_folder(sessionID, 'INBOX', inbox_obj); dbms_output.put_line('Opened INBOX'); -- get all the messages from INBOX mail_folder.get_folder_messages(sessionId, message_list); dbms_output.put_line('Got ' || message_list.COUNT || ' messages'); FOR i IN 1..message_list.COUNT LOOP dbms_output.put_line('***** Message # ' || i || ' *****'); print_message(sessionId, message_list(i)); END LOOP; -- set all the messages flags to be seen and deleted mail_folder.set_msg_flags(sessionId, message_list, bit_on(MAIL_MESSAGE.GC_SEEN_FLAG, MAIL_MESSAGE.GC_DELETED_FLAG), true); -- expunge INBOX to remove all the messages we have fetched mail_folder.expunge_folder(sessionId); -- logout mail_session.logout(sessionID); dbms_output.put_line('Elvis has left the building!'); -- commit commit; EXCEPTION WHEN mail_errors.login_err THEN dbms_output.put_line('Failed to log-in!!!'); dbms_output.put_line('SQLcode: ' || sqlcode); dbms_output.put_line('SQLerrm: ' || sqlerrm); rollback; WHEN OTHERS THEN dbms_output.put_line('SQLcode: ' || sqlcode); dbms_output.put_line('SQLerrm: ' || sqlerrm); rollback; END; /
This example shows how to compose and send a multipart/alternative message.
set serveroutput on size 1000000; DECLARE to_addr VARCHAR2(100) := 'testuser2@oracle.com'; from_addr VARCHAR2(100) := 'testuser1@oracle.com'; subject_str VARCHAR2(100) := 'Example: send a multipart/alternative message'; msg_obj mail_message_obj; a_bodypart mail_bodypart_obj; b_bodypart mail_bodypart_obj; msg_data VARCHAR2(500); BEGIN -- start composing a message mail_message.compose_message(msg_obj); dbms_output.put_line('start composing...'); -- set the message's headers mail_message.set_msgheader( msg_obj, to_addr, from_addr, null, from_addr, sysdate, subject_str, '1.0', 'multipart/alternative', null, null ); dbms_output.put_line('set message header'); -- add the text/plain body-part mail_message.add_bodypart(msg_obj, a_bodypart); mail_message.set_bpheader( a_bodypart, 'text/plain', 'us-ascii', 'quoted-printable', null, null, null, null, null, null ); msg_data := 'text/plain data'; mail_message.set_content(a_bodypart, utl_raw.cast_to_raw(msg_data)); dbms_output.put_line('set text/plain body'); -- add the text/html body-part mail_message.add_bodypart(msg_obj, b_bodypart); mail_message.set_bpheader( b_bodypart, 'text/html', 'us-ascii', 'quoted-printable', null, null, null, null, null, null ); msg_data := '<html>text/html data</html>'; mail_message.set_content(b_bodypart, utl_raw.cast_to_raw(msg_data)); dbms_output.put_line('set text/html body'); -- now send this message mail_message.send_message(msg_obj); dbms_output.put_line('send message'); -- commit commit; EXCEPTION when OTHERS then dbms_output.put_line ('send failed!'); dbms_output.put_line('SQLcode: ' || sqlcode); dbms_output.put_line('SQLerrm: ' || sqlerrm); rollback; END; /
This example shows how to use the GetTheme API.
ACCEPT msgid NUMBER DEFAULT 1 PROMPT 'Input message id: '; set serveroutput on DECLARE themebuf CTXSYS.CTX_DOC.THEME_TAB; errmsg VARCHAR2(254); res INTEGER; i INTEGER; BEGIN res := ES_OT_API.GetThemes(&msgid, '0' , 1,false, themebuf, errmsg); IF res != ES_OT_API.ESOTAPI_OK THEN DBMS_OUTPUT.PUT_LINE('GetTheme error no: ' || res); DBMS_OUTPUT.PUT_LINE(errmsg); ELSE dbms_output.put_line('found '|| themebuf.count ||' themes'); FOR i in 1..themebuf.count LOOP DBMS_OUTPUT.PUT_LINE(' (' || themebuf(i).theme || ', ' || themebuf(i).weight || ') '); END LOOP; DBMS_OUTPUT.PUT_LINE('Get ' || themebuf.count || ' themes succeed'); END IF; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE('ERROR:' || errmsg); END; /
This procedure cleans up after any error during the composition and sending of a new message.
PROCEDURE abort_message;
This procedure copies the encoded body part content along with the part header into the specified BLOB locator. The locator must have enough storage for the data.
mail_errors.unauthenticated_err
PROCEDURE get_bp_content_with_hdrs ( session_id IN NUMBER, bodypart_obj IN MAIL_BODYPART_OBJ, content IN OUT BLOB ) RETURN boolean;
| Parameters | Description |
|---|---|
|
session_id |
An identifier that represents a user's authenticated session |
|
bodypart_obj |
The body-part object |
|
content |
The encoded body part content with its header |
