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.
PARTINDEX | PARENTINDEX | CONTENT_TYPE | SIZE |
0,0 | 0 | text/plain | 2993 |
0,1 | 1 | text/html | 94849 |
1 | 1 | multipart/report | 39398 |
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;
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.
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.
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?
The issue is with your installation and configuration of the Java code.
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