in

Retrieve Messages from Mailbox Using PL/SQL Mail_Client API

Recently I got the task to build a kind of mail client system in Oracle Apex. The requirement was to show the messages of mail Inbox on a page for a particular account so that the user can view the email messages, attachments and can delete the messages, etc. But in Oracle, there are packages to send emails using UTL_SMPT, UTL_MAIL, and APEX_MAIL, and there is no package to retrieve the mail messages from the mailbox. After searching a little bit, I found a PL/SQL MAIL_CLIENT API written by Carsten Czarski, with which you can easily retrieve the messages from the mailbox. And in this tutorial, I am giving the examples of MAIL_CLIENT API commands and procedures. First, download and install PL/SQL MAIL_CLIENT using the following link:

Download PL/SQL MAIL_CLIENT API

PL/SQL MAIL_CLIENT API Examples

In the following sections, I am giving the step by step examples to connect using the MAIL_CLIENT package, then how to view mailbox contents, how to view a particular message and its attachments, etc.

Example-1: Connect Using MAIL_CLIENT

To connect to the mail server, use the following PL/SQL code:

begin
  mail_client.connect_server(
    p_hostname => 'YourMailServer.com',
    p_port     => YourPortIntegerValue,
    p_protocol => mail_client.protocol_IMAP, -- or mail_client.protocol_POP3
    p_userid   => 'YourUserID',
    p_passwd   => 'YourPassword',
    p_ssl      => true -- true or false depends on your mailbox
  );

  mail_client.open_inbox;
  dbms_output.put_line('Mailbox successfully opened.');
  dbms_output.put_line('The INBOX folder contains '||mail_client.get_message_count||' messages.');
end;
/

Change the hostname, port, protocol, user id, and password according to your mailbox settings. And after executing the above code you will be connected to your mailbox. Now definitely you want to view the contents of your inbox. Use the following command:

Example-2: View the Mailbox Contents

To view the mailbox contents using the PL/SQL MAIL_CLIENT API, run the following SQL statement to view the messages latest on top:

select * from table(mail_client.get_mail_headers()) order by msg_number desc;

You will get the following columns from the above query:

  • MSG_NUMBER
  • SUBJECT
  • SENDER
  • SENDER_EMAIL
  • SENT_DATE
  • CONTENT_TYPE
  • DELETED
  • Some more flag columns

Example-3: Get the Structure of the Mail Message

The structure of a mail message contains the information, such as which PARTINDEX number contains the body part in plain text format, the body part in HTML format, and the attachments of the mail. Suppose you want to get the plain text of email body part run the following SQL queries:

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

The value 1 above is the MSG_NUMBER of the messages. It will give you the following information:

  • PARTINDEX
  • PARENTINDEX
  • CONTENT_TYPE
  • SIZE, etc.
PARTINDEXPARENTINDEXCONTENT_TYPESIZE
0,00text/plain2993
0,11text/html94849
11multipart/report39398

Example-4: Retrieve the Message Body

Now for example, if you want to get the message body in plain text format for message number 1 run the following query:

SELECT Mail_Client.Get_Message(1 /* specify message number */).get_bodypart_content_varchar2('0,0')
             FROM Dual;

Note: The 0,0 above is the value of the PARTINDEX column for text/plain content type.

To get the body in HTML format, we will run the following query with the PARTINDEX column value 0,1. It will return the body in CLOB:

SELECT Mail_Client.Get_Message(1 /* specify message number */).get_bodypart_content_clob('0,1')
            FROM Dual;

Example-5: Get the Mail Attachment

Similarly, get the mail attachment using the PARTINDEX value 1 as the parameter, as shown in the below query:

SELECT Mail_Client.Get_Message(1 /* specify message number */).Get_Bodypart_Content_Blob('1')
             FROM Dual;

Example-6: Delete a Mail Message

Below is the stored procedure example to delete the mail message, using the MAIL_CLIENT API.

Create or Replace PROCEDURE Delete_Mail_Msg(i_Msg_Number IN NUMBER) IS
     
t_Msg Mail_t;

BEGIN

Mail_Client.Connect_Server(p_Hostname => 'YourMailServer',
                           p_Port     => MailServerPort,
                           p_Protocol => Mail_Client.Protocol_Imap,
                           p_Userid   => 'username',
                           p_Passwd   => 'password',
                           p_Ssl      => TRUE);

Mail_Client.Open_Inbox;

t_Msg := Mail_Client.Get_Message(i_Msg_Number);
t_Msg.Mark_Deleted();

Mail_Client.Expunge_Folder;
Mail_Client.Close_Folder;
Mail_Client.Disconnect_Server;

EXCEPTION
     WHEN OTHERS THEN
       IF Mail_Client.Is_Connected() = 1 THEN
         Mail_Client.Close_Folder;
         Mail_Client.Disconnect_Server;
       END IF;
       Raise;
   END Delete_Mail_Msg;

Now just call the above procedure to delete a specific mail message, passed as parameter. Below is the example:

Begin
   Delete_Mail_Msg(3);
End;

The above call to procedure DELETE_MAIL_MSG will remove the email message number 3 from the server.

Also, giving the example below to store all inbox messages to a table with the mail body and attachment. Follow these steps:

Step-1: Create a Table.

CREATE TABLE MAIL_INBOX (
MSG_NUMBER INTEGER,
SUBJECT VARCHAR2(4000),
SENT_DATE DATE,
SENDER_EMAIL,
BODY_TEXT CLOB,
MAIL_ATTACHMENT BLOB)
/

Step-2: Create an Oracle PL/SQL Stored Procedure

CREATE OR REPLACE PROCEDURE LOAD_EMAILS IS

CURSOR c_Inbox IS
SELECT Msg_Number,
Subject,
Sender,
Sender_Email,
Sent_Date,
Content_Type
FROM TABLE(Mail_Client.Get_Mail_Headers())
ORDER BY Msg_Number DESC;

c_Clob CLOB;
b_blob BLOB;

t_Msg Mail_t;

v_Partindex VARCHAR2(100);
BEGIN

Mail_Client.Connect_Server(p_Hostname => 'YOURMAILSERVER',
p_Port => YOURPORT,
p_Protocol => Mail_Client.Protocol_Imap,
p_Userid => 'USERID',
p_Passwd => 'PASSWORD',
p_Ssl => TRUE);

Mail_Client.Open_Inbox;

FOR c IN c_Inbox LOOP

Dbms_Lob.Createtemporary(Lob_Loc => c_Clob,
Cache => TRUE,
Dur => Dbms_Lob.Call);

Dbms_Lob.Createtemporary(Lob_Loc => b_blob,
Cache => TRUE,
Dur => Dbms_Lob.Call);

IF Substr(c.Content_Type,
1,
9) = 'multipart' THEN
v_Partindex := NULL;
BEGIN
SELECT Partindex
INTO v_Partindex
FROM TABLE(Mail_Client.Get_Message(c.Msg_Number).Get_Structure())
WHERE Substr(Content_Type,
1,
9) = 'text/html';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

IF v_Partindex IS NOT NULL THEN

BEGIN
SELECT Mail_Client.Get_Message(c.Msg_Number).Get_Bodypart_Content_Clob(v_Partindex)
INTO c_Clob
FROM Dual;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

BEGIN
SELECT Mail_Client.Get_Message(c.Msg_Number).Get_Bodypart_Content_BLOB('1')
INTO b_blob
FROM Dual;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

END IF;
INSERT INTO mail_inbox
(Msg_Number,
Subject,
Sent_Date,
Sender_email,
Body_Text,
mail_attachment)
VALUES
(c.Msg_Number,
c.Subject,
c.Sent_Date,
c.Sender_Email,
c_Clob,
b_blob);
ELSIF Substr(c.Content_Type,
1,
9) = 'text/html' THEN

BEGIN
SELECT Mail_Client.Get_Message(c.Msg_Number).Get_Content_Clob()
INTO c_Clob
FROM Dual;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

INSERT INTO mail_inbox
(Msg_Number,
Subject,
Sent_Date,
Sender_email,
Body_Text)
VALUES
(c.Msg_Number,
c.Subject,
c.Sent_Date,
c.Sender_Email
c_Clob);

END IF;
END LOOP;
COMMIT;
Mail_Client.Close_Folder;
Mail_Client.Disconnect_Server;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;

IF Mail_Client.Is_Connected() = 1 THEN
Mail_Client.Close_Folder;
Mail_Client.Disconnect_Server;
END IF;
RAISE;

END LOAD_EMAILS;

Run the above procedure to populate the table with email messages as follows:

Begin
Load_Emails;
End;

Now you can query the table MAIL_INBOX to view the email messages.

Select * from mail_inbox;

Related Tutorials:

Written by Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.org and orclqa.com a question and answer forum for developers.

guest

11 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments