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.

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:

• How to Get BLOB from File in PL/SQL?
• Oracle UTL_SMTP: Send Mail with Attachment Example Using Oracle Wallet Authentication