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:

Vinish Kapoor
Vinish Kapoor

Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

11 Comments

  1. Hi Boss,
    I am getting below error while implementing the mail client api. Please suggest me what is wrong here.

    Error report -
    ORA-29532: Java call terminated by uncaught Java exception: java.lang.NoClassDefFoundError
    ORA-06512: at "SNDTSTDB.MAIL_CLIENT", line 8
    ORA-06512: at "SNDTSTDB.MAIL_CLIENT", line 37
    ORA-06512: at line 2
    29532. 00000 - "Java call terminated by uncaught Java exception: %s"
    *Cause:  A Java exception or error was signaled and could not be
          resolved by the Java code.
    *Action:  Modify Java code, if this behavior is not intended.

    • The above error occurred while executing which code?

      By looking at the error, it seems the installation of mail client api is not completed correctly.

    • there was some grant issue. after grant provided i am getting this error. my credentials are correct

      Error report -
      ORA-29532: Java call terminated by uncaught Java exception: javax.mail.AuthenticationFailedException: LOGIN failed.
      ORA-06512: at "SNDTSTDB.MAIL_CLIENT", line 16
      ORA-06512: at "SNDTSTDB.MAIL_CLIENT", line 35
      ORA-06512: at line 3
      29532. 00000 - "Java call terminated by uncaught Java exception: %s"
      *Cause:  A Java exception or error was signaled and could not be
            resolved by the Java code.
      *Action:  Modify Java code, if this behavior is not intended.

    • I have now connected to email server. is it possible to load new emails to database table automatically. current load email procedure will load all the emails & it will take more time to complete the process.

  2. Hi Vinish, I was wondering if this solution will work with the current version of APEX. Also, is there anything in APEX (or on the roadmap) to perhaps integrate this into APEX or is there any other solution to retrieving inbound email responses into APEX?
    Thanks.

  3. Hi Vinish, your code is outstanding, notice that I have the same error that happened to Bheem Vangi:

    Error report –
    ORA-29532: Java call terminated by uncaught Java exception: javax.mail.AuthenticationFailedException: LOGIN failed.
    ORA-06512: at “SNDTSTDB.MAIL_CLIENT”, line 16
    ORA-06512: at “SNDTSTDB.MAIL_CLIENT”, line 35
    ORA-06512: at line 3
    29532. 00000 – “Java call terminated by uncaught Java exception: %s”
    *Cause:  A Java exception or error was signaled and could not be
          resolved by the Java code.
    *Action:  Modify Java code, if this behavior is not intended.

    Do you know how to fix it please?

    • Dear Vinish, thank you very much for your prompt reply. See that I run the install.sql and grantPublic.sql files successfully (as you describe in the README file) without errors. I also gave 'SYS: java.net.SocketPermission' and 'SYS: java.lang.RuntimePermission' permissions with no problem.
      But when I run Example-1: Connect Using MAIL_CLIENT
      with your code:
      ************************************************** *********
      begin
       mail_client.connect_server (
        p_hostname => 'my_mailhost.com',
        p_port => 995,
        p_protocol => mail_client.protocol_POP3,
        p_userid => 'my_user_id',
        p_passwd => 'my_password',
        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;
      ************************************************** *********
      It gives me as a result:

      Error report -
      ORA-29532: Java call terminated by uncaught Java exception: java.lang.NoClassDefFoundError
      ORA-06512: at "SYS.MAIL_CLIENT", line 16
      ORA-06512: at "SYS.MAIL_CLIENT", line 35
      ORA-06512: at line 2
      29532. 00000 - "Java call terminated by uncaught Java exception:% s"
      * Cause: A Java exception or error was signaled and could not be
            resolved by the Java code.
      * Action: Modify Java code, if this behavior is not intended.

      My JAVA version is: JServer JAVA Virtual Machine 19.0.0.0.0

      I believe that the package installed well (see attached figure). Do you have any tips about it?

    • You also need to set the Java CLASSPATH system variable on your server/personal computer. Have you done that already?

    • Dear Vinish, I have been searching the web for how to do it but I am really confused (especially with jre and jdk, sorry for my ignorance regarding this). In fact I think that on my system I only have the jre installed. Could you direct me to a web page where I find the correct way to set the Java CLASSPATH system variable, please?

    • You may have the same issue that I just encountered. I have had this up and running for almost a year and it has worked great. All of a sudden I also get this error ORA-29532: Java call terminated by uncaught Java exception: javax.mail.AuthenticationFailedException: LOGIN failed followed by javax.mail.MessagingException: A2 BAD User is authenticated but not connected.

      I have traced the issue back to us disabling basic authentication on our Office 365 tenant. So has anybody been able to use mail_client with basic authentication turned off? I suspect this will become more and more of an issue as organizations turn this off for security reasons.

      I think the solution is to use OAuth instead but that will likely require a pretty big overhaul of mail_client

Comments are closed.