PL/SQL Email Client


Package mail_t

The object type MAIL_T represents a message in the mailbox folder. A MAIL_T object is obtained either by the MAIL_CLIENT.GET_MESSAGE ... functions.
 SELECT MAIL_CLIENT.GET_MESSAGE([msgnum]) FROM DUAL
 
... or MAIL_CLIENT.GET_MAIL_HEADERS ...
 SELECT VALUE(o) MAIL_OBJECT FROM TABLE(MAIL_CLIENT.GET_MAIL_HEADERS())
 


Field Summary
 char(1)answered
           Indicates whether the message is marked as "answered".
 varchar2(100)content_type
           Content Type of the message.
 char(1)deleted
           Indicates whether the message is marked as "deleted".
 numbermessage_size
           Message size in bytes.
 number(10)msg_number
           A Message object's message number is the relative position of this Message in its Folder.
 char(1)read
           Indicates whether the message is marked as "read".
 char(1)recent
           Indicates whether the message is marked as "recent".
 varchar2(100)sender
           Sender name of the message
 varchar2(100)sender_email
           Sender email of the message
 datesent_date
           Date on which the message was sent.
 varchar2(4000)subject
           Subject of the message

Method Summary
 copy_message(p_target_folder varchar2)
           Copies the message to another mailbox folder.
 blobdump_blob()
           Dumps the message "as it is"; i.e. does not interpret the message strcuture.
 clobdump_clob()
           Dumps the message "as it is"; i.e. does not interpret the message strcuture.
 blobget_bodypart_content_blob(p_partindexes varchar2)
           Returns the content of the messages' body part as BLOB.
 clobget_bodypart_content_clob(p_partindexes varchar2)
           Returns the content of the messages' body part as CLOB.
 varchar2get_bodypart_content_type(p_partindexes varchar2)
           Returns the messages' content type
 varchar2get_bodypart_content_varchar2(p_partindexes varchar2)
           Returns the content of the messages' body part as VARCHAR2.
 numberget_bodypart_multipart_count(p_partindexes varchar2)
           Returns for a messages' body part (which is of type "multipart") the number of child body parts.
 clobget_content_clob()
           Tries to retrieve the text content of simple or multipart messages as a CLOB.
 varchar2get_content_type()
           Returns the messages' content type
 varchar2get_content_varchar2()
           Tries to retrieve the text content of simple or multipart messages as VARCHAR2.
 mail_header_tget_headers(p_partindexes varchar2)
           Lists all email headers of the specified body part.
 mail_header_tget_headers()
           Lists all message headers
 numberget_multipart_count()
           Returns for multipart messages the content type of a specific body part.
 varchar2get_priority()
           Returns the messages' priority flag
 dateget_receivedate()
           Returns the datetime at which the message has been received by the mail server.
 mail_addr_tget_recipients()
           Returns all message recipients as table of MAIL_ADDR_T
 clobget_simple_content_clob()
           Retrieves the content of a simple text message as a CLOB.
 varchar2get_simple_content_varchar2()
           Retrieves the content of a simple text message as VARCHAR2.
 numberget_size(p_partindexes varchar2)
           Returns the size of the specified body part in bytes.
 numberget_size()
           Returns the size of the whole message in bytes.
 mail_part_tget_structure()
           Returns (for multipart messages) the information about the contained body part hierarchy as table of MAIL_PART_T.
 varchar2get_uid()
           Returns the message UID (if available)
 mark_deleted()
           Mark the message as "deleted".
 mark_read()
           Mark the message as "read".
 mark_undeleted()
           Removed the "deleted" mark from the message.
 mark_unread()
           Mark the message as "unread", therefore "new".
 move_message(p_target_folder varchar2)
           Moves the message to another mailbox folder; i.e. copies the message and marks it as deleted.

Field Detail

msg_number

  public number(10) msg_number
A Message object's message number is the relative position of this Message in its Folder. Note that the message number for a particular Message can change during a session if other messages in the Folder are deleted and expunged. Valid message numbers start at 1. Messages that do not belong to any folder (like newly composed or derived messages) have 0 as their message number.

subject

  public varchar2(4000) subject
Subject of the message

sender

  public varchar2(100) sender
Sender name of the message

sender_email

  public varchar2(100) sender_email
Sender email of the message

sent_date

  public date sent_date
Date on which the message was sent.

deleted

  public char(1) deleted
Indicates whether the message is marked as "deleted".

read

  public char(1) read
Indicates whether the message is marked as "read".

recent

  public char(1) recent
Indicates whether the message is marked as "recent".

answered

  public char(1) answered
Indicates whether the message is marked as "answered".

content_type

  public varchar2(100) content_type
Content Type of the message.

message_size

  public number message_size
Message size in bytes.

Method Detail

get_simple_content_varchar2

public varchar2 get_simple_content_varchar2()
Retrieves the content of a simple text message as VARCHAR2. Throws an exception when the content is too big.
Returns:
The message content, if it is a "text/plain" message, NULL otherwise.


get_simple_content_clob

public clob get_simple_content_clob()
Retrieves the content of a simple text message as a CLOB. The function creates a temporary LOB for this - this might has to be freed with DBMS_LOB.FREETEMPORARY after usage.
Returns:
The message content, if it is a "text/plain" message, NULL otherwise.


get_content_varchar2

public varchar2 get_content_varchar2()
Tries to retrieve the text content of simple or multipart messages as VARCHAR2. If the message is a multipart message the function looks in the first two body parts and returns the first body part which is a text. Throws an exception when the returned text is too big for VARCHAR2.
Returns:
The message content, if one of the first two body parts is "text/plain", NULL otherwise.


get_content_clob

public clob get_content_clob()
Tries to retrieve the text content of simple or multipart messages as a CLOB. If the message is a multipart message the function looks in the first two body parts and returns the first body part which is a text.
Returns:
The message content, if one of the first two body parts is "text/plain", NULL otherwise.


get_bodypart_content_varchar2

public varchar2 get_bodypart_content_varchar2(p_partindexes varchar2)
Returns the content of the messages' body part as VARCHAR2. Throws an exception, if ...
Parameters:
p_partindexes - the identifier for the selected body part - can be determined with the get_structure function.
Returns:
the body part content as VARCHAR2.


get_bodypart_content_clob

public clob get_bodypart_content_clob(p_partindexes varchar2)
Returns the content of the messages' body part as CLOB. Throws an exception, if ...
Parameters:
p_partindexes - the identifier for the selected body part - can be determined with the get_structure function.
Returns:
the body part content as CLOB.


get_bodypart_content_blob

public blob get_bodypart_content_blob(p_partindexes varchar2)
Returns the content of the messages' body part as BLOB.
Parameters:
p_partindexes - the identifier for the selected body part - can be determined with the get_structure function.
Returns:
the body part content as BLOB.


get_content_type

public varchar2 get_content_type()
Returns the messages' content type
Returns:
The messages' content type e.g. "text/plain", "text/html" or "multipart/ALTERNATIVE"


get_priority

public varchar2 get_priority()
Returns the messages' priority flag
Returns:
The messages' priority flag (Normal, High, Highest)


get_bodypart_content_type

public varchar2 get_bodypart_content_type(p_partindexes varchar2)
Returns the messages' content type
Returns:
The messages' content type e.g. "text/plain", "text/html" or "multipart/ALTERNATIVE"


get_multipart_count

public number get_multipart_count()
Returns for multipart messages the content type of a specific body part.
Returns:
The body parts content type e.g. "text/plain", "text/html" or others.


get_bodypart_multipart_count

public number get_bodypart_multipart_count(p_partindexes varchar2)
Returns for a messages' body part (which is of type "multipart") the number of child body parts.
Parameters:
p_partindexes - the identifier for the selected body part - can be determined with the get_structure function.
Returns:
the number of child body parts.


get_structure

public mail_part_t get_structure()
Returns (for multipart messages) the information about the contained body part hierarchy as table of MAIL_PART_T.
 SELECT * FROM TABLE(MAIL_CLIENT.GET_MESSAGE([msgnum]).GET_STRUCTURE())
 
Example:
 PARTINDEX  CONTENT_TYPE                   CONTENT_DISPOSITION   PART_SIZE
 ---------- ------------------------------ -------------------- ----------
 0          TEXT/PLAIN                                                 330
 1          multipart/RELATED                                           -1
 1,0        TEXT/HTML                                                  668
 1,1        IMAGE/JPEG                     INLINE                    10122
 
The PARTINDEX column contains the identifier for a specific body part.
Returns:
The message body part hierarchy


mark_read

public  mark_read()
Mark the message as "read".


mark_unread

public  mark_unread()
Mark the message as "unread", therefore "new".


mark_deleted

public  mark_deleted()
Mark the message as "deleted". Messages marked as "deleted" will be removed in the next "expunge" operation of the mailbox folder.


mark_undeleted

public  mark_undeleted()
Removed the "deleted" mark from the message.


get_headers

public mail_header_t get_headers(p_partindexes varchar2)
Lists all email headers of the specified body part.
Parameters:
p_partindexes - the identifier for the selected body part - can be determined with the get_structure function.
Returns:
the headers of the specified body part as table of MAIL_HEADER_T.


get_headers

public mail_header_t get_headers()
Lists all message headers
Returns:
the message headers as table of MAIL_HEADER_T.
 SELECT * FROM TABLE(MAIL_CLIENT.GET_MESSAGE([msgnum]).GET_HEADERS())
 


get_size

public number get_size(p_partindexes varchar2)
Returns the size of the specified body part in bytes.
Parameters:
p_partindexes - the identifier for the selected body part - can be determined with the get_structure function.
Returns:
the size of the specified body part in bytes.


get_size

public number get_size()
Returns the size of the whole message in bytes.
Returns:
the size of the message in bytes.


dump_clob

public clob dump_clob()
Dumps the message "as it is"; i.e. does not interpret the message strcuture.
Returns:
message dump as a CLOB


dump_blob

public blob dump_blob()
Dumps the message "as it is"; i.e. does not interpret the message strcuture.
Returns:
message dump as a BLOB


copy_message

public  copy_message(p_target_folder varchar2)
Copies the message to another mailbox folder.
Parameters:
p_target_folder - the absolute path of the target folder


move_message

public  move_message(p_target_folder varchar2)
Moves the message to another mailbox folder; i.e. copies the message and marks it as deleted.
Parameters:
p_target_folder - the absolute path of the target folder


get_uid

public varchar2 get_uid()
Returns the message UID (if available)
Returns:
the message UID, null of not available


get_receivedate

public date get_receivedate()
Returns the datetime at which the message has been received by the mail server.
Returns:
the receive date of the message


get_recipients

public mail_addr_t get_recipients()
Returns all message recipients as table of MAIL_ADDR_T
Returns:
table containing all message recipients (TO and CC)


PL/SQL Email Client