Receiving Email Messages through Oracle procedure. [message #234372] |
Tue, 01 May 2007 04:43  |
woolmer
Messages: 13 Registered: May 2007 Location: Uk
|
Junior Member |
|
|
Hi,
First of all best wishes for the people who are delivering their knowledge in that forum.
I would like to have a procedure that connect to pop servers and receive email messages with attachments. We want to do this through oracle forms
What type of routine, I need that browses through the pop servers and receives email messages with attachments into database table, through forms.
Can I have an example that how we do that?
Your valuable information will help me lot.
Woolmer
Databases 8i/9i
forms 6i
|
|
|
Re: Receiving Email Messages through Oracle procedure. [message #234735 is a reply to message #234372] |
Thu, 03 May 2007 01:16   |
psix666
Messages: 51 Registered: April 2007 Location: Azerbaijan
|
Member |

|
|
I find this on the one of the forums and use it now. Nice procedure. There is some issues when e-mails have rich text format. I think this will help you.
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED ReceiveMail AS
import javax.mail.*;
import javax.mail.internet.*;
import java.util.*;
import java.io.*;
import java.sql.*;
import sqlj.runtime.*;
import oracle.sql.BLOB;
public class ReceiveMail
{
static void getAttachments(Message message, int incidentNo)
throws MessagingException, IOException, SQLException {
//String attachments = "";
Object content = message.getContent();
if (content instanceof Multipart)
{
// -- Multi part message which may contain attachment
Multipart multipart = (Multipart)message.getContent();
// -- Loop through all parts of the message
for (int i=0, n=multipart.getCount(); i<n; i++) {
Part part = multipart.getBodyPart(i);
String disposition = part.getDisposition();
if ((disposition != null) && (disposition.equals(Part.ATTACHMENT) || disposition.equals(Part.INLINE))) {
//-- This part is a file attachment
String fileName = incidentNo+"_"+part.getFileName().replace(' ','_');
System.out.println("FILE: " + fileName);
String contentType = part.getContentType();
String mimeType = contentType.substring(0,contentType.indexOf(";"));
System.out.println("FILETYPE: " + mimeType);
InputStream is = part.getInputStream();
// -- To work with a BLOB column you have to insert a record
// -- with an emptly BLOB first.
#sql { insert into attachment(at_file, at_mimetype, at_attachment)
values (:fileName, :mimeType, empty_blob()) };
// -- Retrieve the BLOB
BLOB attachment = null;
#sql { select at_attachment into :attachment
from attachment where at_file = :fileName };
// -- Fill the BLOB
OutputStream os = attachment.getBinaryOutputStream();
int j;
while ((j = is.read()) != -1) {
os.write(j);
}
is.close();
os.close();
// -- Set the BLOB by updating the record
#sql { update attachment set at_attachment = :attachment
where at_file = :fileName };
}
}
}
}
static String getPlainTextBody(Message message)
throws MessagingException, IOException
{
Object content = message.getContent();
if (message.isMimeType("text/plain")) {
// -- Message has plain text body only
System.out.println("SIMPLE TEXT");
return (String) content;
} else if (message.isMimeType("multipart/*")) {
// -- Message is multipart. Loop through the message parts to retrieve
// -- the body.
Multipart mp = (Multipart) message.getContent();
int numParts = mp.getCount();
System.out.println("MULTIPART: "+numParts);
for (int i = 0; i < numParts; ++i) {
System.out.println("PART: "+mp.getBodyPart(i).getContentType());
if (mp.getBodyPart(i).isMimeType("text/plain")) {
// -- Return the plain text body
return (String) mp.getBodyPart(i).getContent();
} else if (mp.getBodyPart(i).isMimeType("multipart/*")) {
// -- Body is also multipart (both plain text and html).
// -- Loop through the body parts to retrieve plain text part.
MimeMultipart mmp = (MimeMultipart) mp.getBodyPart(i).getContent();
int numBodyParts = mmp.getCount();
System.out.println("MULTIBODYPART: "+numBodyParts);
for (int j = 0; j < numBodyParts; ++j) {
System.out.println("BODYPART: "+mmp.getBodyPart(j).getContentType());
if (mmp.getBodyPart(j).isMimeType("text/plain")) {
// -- Return the plain text body
return (String) mmp.getBodyPart(j).getContent();
}
}
}
}
return "";
} else {
System.out.println("UNKNOWN: "+message.getContentType());
return "";
}
}
static void saveMessage(Message message)
throws MessagingException, IOException, SQLException
{
//String body = "";
int incidentNo;
// -- Get a new incident number
#sql { select seq_incident.nextval into :incidentNo from dual };
// -- Get the header information
String from = ((InternetAddress)message.getFrom()[0]).getAddress();
System.out.println("FROM: "+ from);
String subject = message.getSubject();
System.out.println("SUBJECT: "+subject);
// -- Retrieve the plain text body
String body = getPlainTextBody(message);
// -- Store the message in the email table
#sql { insert into email (em_incident, em_from, em_subject, em_body)
values (:incidentNo, :from, :subject, :body) };
// -- Retrieve the attachments
getAttachments(message, incidentNo);
#sql { commit };
// -- Mark message for deletion
// message.setFlag(Flags.Flag.DELETED, true);
}
public static String Receive(String POP3Server, String usr, String pwd)
{
Store store = null;
Folder folder = null;
try
{
// -- Get hold of the default session --
Properties props = System.getProperties();
props.put("mail.pop3.connectiontimeout", "60000");
Session session = Session.getDefaultInstance(props, null);
// -- Get hold of a POP3 message store, and connect to it --
store = session.getStore("pop3");
store.connect(POP3Server, usr, pwd);
System.out.println("Connected");
// -- Try to get hold of the default folder --
folder = store.getDefaultFolder();
if (folder == null) throw new Exception("No default folder");
// -- ...and its INBOX --
folder = folder.getFolder("INBOX");
if (folder == null) throw new Exception("No POP3 INBOX");
// -- Open the folder for read_write (to be able to delete message) --
folder.open(Folder.READ_WRITE);
// -- Get the message wrappers and process them --
Message[] msgs = folder.getMessages();
for (int msgNum = 0; msgNum < msgs.length; msgNum++){
saveMessage(msgs[msgNum]);
}
System.out.println("No more messages");
return ("SUCCESS");
}
catch (Exception ex){
ex.printStackTrace();
return ex.toString();
}
finally{
// -- Close down nicely --
try{
// close(true), to expunge deleted messages
if (folder!=null) folder.close(true);
if (store!=null) store.close();
}
catch (Exception ex){
//ex.printStackTrace();
return ex.toString();
}
}
}
};
create or replace function receivemail (pop3_server in string
,pop3_usr in string
,pop3_pwd in string
)
return varchar2
is language java name
'ReceiveMail.Receive(java.lang.String
,java.lang.String
,java.lang.String) return String';
/
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Receiving Email Messages through Oracle procedure. [message #236323 is a reply to message #236320] |
Wed, 09 May 2007 10:50   |
woolmer
Messages: 13 Registered: May 2007 Location: Uk
|
Junior Member |
|
|
Hello,
You are right, But I wanted to do the whole routine through forms 6i/9i.
The main procedure, which you are calling through "when button press trigger", should also be automize through forms, regarding it where should we code these main procedure in forms?
Should this routine gives the message about the status of the mails. i.e if no mail receive or error give some message on forms.
Also Right now I don't have any Pop server email account.
How can I check, that this form receive emails and display on the form screen?
Should I give mail.yahoo.com to the logonserver and my yahoo .
I hope you can't take my posting cumbersome.
Thanks
Woolmer
|
|
|
|
|
|
|
|
|
|
Re: Receiving Email Messages through Oracle procedure. [message #237013 is a reply to message #236914] |
Fri, 11 May 2007 10:41   |
bermen
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Thanks what's your good name?
Well you are using these two tables for this process.In the following table what does the field at_mimetype in attachement table and em_incident in email table do?
Suppose if we want to store different pop servers into a table and then we want to select one of the popservers and receives messages from it how should we link this table with the other two which you created.
Would you also give me idea what filed i need in the new table for storing addressess of differebt popservers and then messsage receives from the selected popservers?
Could you please give me idea?
Thanks
Bremen
create table attachment(
at_file varchar2(500),
at_mimetype varchar2(500),
at_attachment blob
);
create table email (
em_incident integer,
em_from varchar2(1000),
em_subject varchar2(1000),
em_body nclob
);
|
|
|
|
Re: Receiving Email Messages through Oracle procedure. [message #237111 is a reply to message #237013] |
Sat, 12 May 2007 03:32   |
bermen
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Hi, I am Bremen Jogarrt. Likely we have to implement the same functionality which Woolmer has.
I will be glad to have all the necessary objects needed this routine to run sucessfuly.
Any way I run the java source code successfully and its function. Now I also configured forms version 9i for doing what I wanted. but when I started running the form I got the error:
"Failed to connect to the server /forms90/i90servlet:-1
Attched you find the screen short of the errors:
|
|
|
|
Re: Receiving Email Messages through Oracle procedure. [message #237115 is a reply to message #237112] |
Sat, 12 May 2007 04:14   |
bermen
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Hi,
I am getting following forms 9i runtime error during running the application
How would I overcome to this issue?
Java.security.AccessControlException:the permission(java.util.PropertyPermission*read,write)has not been granted to bremen
I put the three text item buttons (POPSERVER ,LOGIN,PWD) on forms where I give values on rumtime.
I edited your code with that one:
declare
v_error_msg varchar2(10000);
begin
v_error_msg:=receivemail(':block1.POPSERVER ',':block1.LOGIN',':block1.PWD');
message(v_error_msg);
pause;
end;
And now I am getting the above error which i mentioned.
Bremen
|
|
|
|
Re: Receiving Email Messages through Oracle procedure. [message #237136 is a reply to message #237120] |
Sat, 12 May 2007 10:42   |
bermen
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Hi,
I also made this entry
permission java.security.AllPermission;
in the following file on my O/S:
F:\program Files\Oracle\jre\1.3.1\lib\secutity\java policy
But this did not work and the errors still there, however now I am trying to implemet what you suggested.
thanks
bremen
|
|
|
Re: Receiving Email Messages through Oracle procedure. [message #237138 is a reply to message #237136] |
Sat, 12 May 2007 10:54   |
bermen
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Now I am getting No Listener Message message when I try to relogin on forms.
Bremen
Error
***********************************************************************
Fatal NI connect error 12541, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kliuu)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=g:\Forms9i\bin\ifbld90.exe )(HOST=kliuu)(USER=Administrator))))
VERSION INFORMATION:
TNS for 32-bit Windows: Version 9.0.1.3.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 9.0.1.3.0 - Production
Time: 12-MAY-2007 08:51:45
Tracing not turned on.
Tns error struct:
nr err code: 0
ns main err code: 12541
TNS-12541: TNS:no listener
ns secondary err code: 12560
nt main err code: 511
TNS-00511: No listener
nt secondary err code: 61
nt OS err code: 0
|
|
|
|
|
Re: Receiving Email Messages through Oracle procedure. [message #237210 is a reply to message #237182] |
Sun, 13 May 2007 07:01   |
bermen
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Hi,
I am using this:
declare
v_error_msg varchar2(10000);
begin
--v_error_msg:=receivemail('POP.GMAIL.COM','bremen@gmail.com','password');
message(v_error_msg);
pause;
end;
Now I am getting following error on runtime:
FRM-40735:WHEN-BUTTON-PRESS TRIGGER raised unhandled exception ora-29540
I got the messages through this pop account on O/S OutlookExpress.
I also would like to ask what is the recommended version for both forms and database for using your code.
Please also let me know what type of changes I will do in forms files or O/S level.
Thanks
Bremen
|
|
|
Re: Receiving Email Messages through Oracle procedure. [message #237264 is a reply to message #237210] |
Mon, 14 May 2007 00:33   |
bermen
Messages: 20 Registered: May 2007
|
Junior Member |
|
|
Hi,
I think may be the datatype of column em_body of email table and
at_attachment of attachment table cause raising trigger error on forms.
Does the data which is called by the procedure receivemail, dispaly on forms screen fields without problem? however my form is based on email table which i created earier.
I can't understand where is the problem?
Bremen
|
|
|
Re: Receiving Email Messages through Oracle procedure. [message #237265 is a reply to message #237264] |
Mon, 14 May 2007 00:38   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Quote: | ORA-29540: class string does not exist
Cause: Java method execution failed to find a class with the indicated name.
Action: Correct the name or add the missing Java class.
|
Like I said before, if you don't know what you are doing (if you don't know Java and you don't know Forms), don't think you can do a Java-Forms job!
You did not include the most important part of the error-message: the class name
[Updated on: Mon, 14 May 2007 00:47] Report message to a moderator
|
|
|
|
|
|