PL/SQL Email Client

PL/SQL Email Client

SUMMARY: FIELD | TYPE | METHOD

Package mail_client

This package provides procedures and functions to connect to a mail server, open a folder and retrieve the messages. The first step is always to connect to the mailserver ...

mail_client.connect_server(
  p_hostname => 'mailserver.mydomain.com',
  p_port     => 143,
  p_protocol => mail_client.protocol_imap,
  p_userid   => 'user.name@mailserver.com',
  p_passwd   => '***',
  p_ssl      => false
);

After that the folder has to be opened ...

mail_client.open_inbox;

... now we can list the messages ...

select * from table(mail_client.get_mail_headers())

... read a message ...

select mail_client.get_message(1).get_content_clob() from dual

... "multipart" messages have a complex structure ...

select * from table(mail_client.get_message(1).get_structure())

... we can mark a message as "deleted"

declare
  v_msg mail_t;
begin
  v_msg := mail_client.get_message(1);
  v_msg.mark_deleted();
end;

... "expunge" the folder (permanently delete the message)

mail_client.expunge_folder;

... close the folder ...

mail_client.close_folder;

... and finally disconnect from the server.

mail_client.disconnect_server;

Note for SSL users:
SSL users might encounter an error message ORA-29532: Java call terminated by uncaught Java exception: javax.mail.MessagingException: java.security.ProviderException: nextBytes() failed; after successfully doing some operations via SSL. This most often occurs on server or virtual machines with no keyboard or mouse attached. The reason is that the /dev/random device has not enough "entropy" to generate the random numbers. A workaround is the have the java engine use "/dev/urandom" instead. This can be achieved with ....

declare
  v_property varchar2(32767);
begin
  c_property := dbms_java.set_property('java.security.egd', '/dev/urandom');
end;

The database user needs a privilege in order to execute this call:

begin
  dbms_java.grant_permission( 
    grantee           => '{Database Schema}', 
    permission_type   => 'SYS:java.util.PropertyPermission',
    permission_name   => 'java.security.egd', 
    permission_action => 'read,write' 
  );
end;
/

Field Summary
`varchar2(4)`	`PROTOCOL_IMAP` Constant for procedure CONNECT_SERVER: Use the IMAP protocol
`varchar2(4)`	`PROTOCOL_POP3` Constant for procedure CONNECT_SERVER: Use the POP3 protocol

Method Summary
	`close_folder()` closes the currently open mailbox folder.
	`connect_server(p_hostname varchar2, p_port number, p_protocol varchar2, p_userid varchar2, p_passwd varchar2, p_ssl boolean)` Connects to a mailserver.
	`create_folder(p_foldername varchar2)` Creates a folder under the current folder
	`delete_folder(p_foldername varchar2)` Deletes a subfolder under the current folder
	`disconnect_server()` disconnects from the server.
	`expunge_folder()` performs the "expunge" operation on the mailbox folder: permanatly deletes all messages marked as "deleted".
`number`	`get_fetch_limit()` Returns the current Fetch Limit for GET_MAIL_HEADERS.
`varchar2`	`get_folder_fullname()` Returns the full name of the current folder
`varchar2`	`get_folder_name()` Returns the short name of the current folder
`mail_array_ct`	`get_folders()` Retrieves the current folders' subfolder list
`mail_t`	`get_mail_headers()` Retrieve all messages in the folder as TABLE OF MAIL_T.
`mail_t`	`get_mail_headers_p()` Retrieve all messages in the folder as TABLE OF MAIL_T.
`mail_t`	`get_message(p_message_number number)` Get the Message object corresponding to the given message number.
`number`	`get_message_count()` Returns the message count within the current folders
`number`	`get_new_message_count()` Returns the "New Message" count within the current folders
`number`	`get_unread_message_count()` Returns the "Unread Message" count within the current folders
`number`	`is_connected()` Checks whether the session is currently connected to a mailserver.
	`open_child_folder(p_foldername varchar2)` Opens a child folder
	`open_folder(p_folder varchar2)` Opens a mailbox folder
	`open_inbox()` Opens the mailbox folder "INBOX".
	`open_parent_folder()` Opens the parent folder.
	`rename_folder(p_foldername varchar2, p_new_foldername varchar2)` Renames a folder
`varchar2`	`rfc_decode(p_text varchar2)` Decodes encoded special characters (unicode, etc) to normal text.
	`set_fetch_limit(p_limit number)` Sets the Fetch Limit for GET_MAIL_HEADERS.

Field Detail

PROTOCOL_IMAP

  public varchar2(4) PROTOCOL_IMAP

Constant for procedure CONNECT_SERVER: Use the IMAP protocol

PROTOCOL_POP3

  public varchar2(4) PROTOCOL_POP3

Constant for procedure CONNECT_SERVER: Use the POP3 protocol

Method Detail

connect_server

public  connect_server(p_hostname varchar2, 
                       p_port number, 
                       p_protocol varchar2, 
                       p_userid varchar2, 
                       p_passwd varchar2, 
                       p_ssl boolean)

Connects to a mailserver.

Parameters:: p_hostname - Hostname of the mail server; p_port - TCP/IP port of the mail server. For IMAP connections this is typically 143, for POP3 connections 110.; p_protocol - Protocol to use: Either MAIL_CLIENT.PROTOCOL_IMAP or MAIL_CLIENT.PROTOCOL_POP3.; p_userid - Userid for the mailserver connection.; p_passwd - Password for the mailserver connection.; p_ssl - SSL flag - defaults to false.

is_connected

public number is_connected()

Checks whether the session is currently connected to a mailserver.

Returns:: 1 if a connection exists, 0 otherwise.

open_inbox

public  open_inbox()

Opens the mailbox folder "INBOX". On most mailservers this folder contains the new messages.

open_folder

public  open_folder(p_folder varchar2)

Opens a mailbox folder

Parameters:: p_folder - absolute folder path

open_parent_folder

public  open_parent_folder()

Opens the parent folder. If the current folder is the root of the folder hierarchy the procedure does nothing.

open_child_folder

public  open_child_folder(p_foldername varchar2)

Opens a child folder

Parameters:: p_foldername - folder path to be opened relative to the current folder

close_folder

public  close_folder()

closes the currently open mailbox folder. The mailbox' root folder is the "current" folder afterwards.

expunge_folder

public  expunge_folder()

performs the "expunge" operation on the mailbox folder: permanatly deletes all messages marked as "deleted".

disconnect_server

public  disconnect_server()

disconnects from the server.

get_mail_headers_p

public mail_t get_mail_headers_p()

Retrieve all messages in the folder as TABLE OF MAIL_T. Message-specific operations can be done via methods of MAIL_T. This is the pipelined version of the function; which returns the first messages more quickly and is more memory efficient.

 SELECT * FROM TABLE(MAIL_CLIENT.GET_MAIL_HEADERS_P())

Returns:: messages of the current folder as TABLE OF MAIL_T

get_mail_headers

public mail_t get_mail_headers()

Retrieve all messages in the folder as TABLE OF MAIL_T. Message-specific operations can be done via methods of MAIL_T.

 SELECT * FROM TABLE(MAIL_CLIENT.GET_MAIL_HEADERS())

Returns:: messages of the current folder as TABLE OF MAIL_T

get_message

public mail_t get_message(p_message_number number)

Get the Message object corresponding to the given message number. A Message object's message number is the relative position of this Message in its Folder. Messages are numbered starting at 1 thro get_priority return varchar2ugh the total number of message in the folder. Note that the message number for a particular Message can change during a session if other messages in the Folder are deleted and the Folder is expunged.

Parameters:: p_message_number - the message number
Returns:: the message object as MAIL_T

get_message_count

public number get_message_count()

Returns the message count within the current folders

Returns:: NUMBER message count

get_new_message_count

public number get_new_message_count()

Returns the "New Message" count within the current folders

Returns:: NUMBER message count

get_unread_message_count

public number get_unread_message_count()

Returns the "Unread Message" count within the current folders

Returns:: NUMBER message count

get_fetch_limit

public number get_fetch_limit()

Returns the current Fetch Limit for GET_MAIL_HEADERS. Note that this only affects the amount of message headers passed from the JVM to the PL/SQL layer. The Java Mail API always retrieves all message headers matching the filter from the POP3 or IMAP server.

Returns:: NUMBER the current fetch limit - "-1" stands for "unlimited".

set_fetch_limit

public  set_fetch_limit(p_limit number)

Sets the Fetch Limit for GET_MAIL_HEADERS. Note that this only affects the amount of message headers passed from the JVM to the PL/SQL layer. The Java Mail API always retrieves all message headers matching the filter from the POP3 or IMAP server.

Parameters:: p_limit - the new Fetch Limit for GET_MAIL_HEADERS - "-1" means: unlimited

get_folders

public mail_array_ct get_folders()

Retrieves the current folders' subfolder list

Returns:: VARCHAR2 Array containing the current folders' subfolder names

rfc_decode

public varchar2 rfc_decode(p_text varchar2)

Decodes encoded special characters (unicode, etc) to normal text.

 SQL> select mail_client.decode('Test f=?iso-8859-15?B?/A==?=r den Mailserver') decoded_text from dual;

 DECODED_TEXT
 --------------------------------------------------------------------------------
 Test für den Mailserver

Parameters:: p_text - the text to be decoded
Returns:: decoded text

create_folder

public  create_folder(p_foldername varchar2)

Creates a folder under the current folder

Parameters:: p_foldername - the path of the subfolder to be created relative to the current folder.

rename_folder

public  rename_folder(p_foldername varchar2, 
                      p_new_foldername varchar2)

Renames a folder

Parameters:: p_foldername - the path of the subfolder to be renamed relative to the current folder.; p_new_foldername - the new path relative to the current folder.

delete_folder

public  delete_folder(p_foldername varchar2)

Deletes a subfolder under the current folder

Parameters:: p_foldername - the path of the subfolder to be deleted relative to the current folder.

get_folder_name

public varchar2 get_folder_name()

Returns the short name of the current folder

Returns:: short name of the current folder.

get_folder_fullname

public varchar2 get_folder_fullname()

Returns the full name of the current folder

Returns:: full name of the current folder.

Overview

Deprecated

Index

Generator

PL/SQL Email Client

SUMMARY: FIELD | TYPE | METHOD

DETAIL: FIELD | TYPE | METHOD