 | |
Receiving email into pl/sql
comp.databases.oracle.tools comp.databases.oracle.tools
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Receiving email into pl/sql -
11-07-2007
, 02:39 PM
#2
| |||
| |||
|
|
I've sent email from pl/sql with utl_smtp and with 10g's utl_mail; but now I want to do the inverse, i.e. I want to READ email from pl/sql. The idea is to set up a dedicated email account. Users could send stereotyped messages to that account, and the pl/sql routine would read the mail, parse the messages and do the needful. As far as I can tell, utl_mail will send, but not receive email. I can think of some Rube Golberg workarounds but can anyone point the way to a "no fuss" way to read simple text emails? Thanks in advance |
some Microsoft Outlook client directly into an Oracle database I am sure
we can recommend a good 12 step program for you.
Incoming emails are stored somewhere. Find the location. Read them using
anything from UTL_FILE to whatever.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#3
| |||
| |||
|
|
Lee wrote: I've sent email from pl/sql with utl_smtp and with 10g's utl_mail; but now I want to do the inverse, i.e. I want to READ email from pl/sql. The idea is to set up a dedicated email account. Users could send stereotyped messages to that account, and the pl/sql routine would read the mail, parse the messages and do the needful. As far as I can tell, utl_mail will send, but not receive email. I can think of some Rube Golberg workarounds but can anyone point the way to a "no fuss" way to read simple text emails? Thanks in advance If you think it is a good idea to send emails, across the web, from some Microsoft Outlook client directly into an Oracle database I am sure we can recommend a good 12 step program for you. As you yourself point out, the email winds up as data somwhere; so why |
to a human agent or just reading an input file as data?
Why would reading email to drive a script from inside a stored procedure
be more dangerous than running a static batch script or a script that
takes input from a human or from a data file?
I assume you're concerned about sql injection attacks or maybe some sort
of spam and/or spoofing, or even an attempt to "flood" the system a la
DDOS attacks?
Maybe I'm not being sufficiently imaginative or paranoid, but I cant see
how the sort of scheme I'm thinking of is more dangerous than crossing
the street. Everybody and his uncle has a listserv that runs on commands
sent in by email, so why is that setting off alarm bells?
I can use an http callout to get data from anywhere on the planet. There
are "rest"-full web services, and SOAP interfaces and all sorts of ways
to have all kinds of heaven-knows-what get presented as input. A routine
that parses stereotyped email messages and deliveres canned reports in
response seems pretty benign.
Or am I living in a fools paradise?
Quote:
|
Incoming emails are stored somewhere. Find the location. Read them using anything from UTL_FILE to whatever. My Oracle server is running on a different box from my email server, so |
If I dont mind having more moving parts, there are plenty of ways to get
from there to here; but I was hoping for something with "no fuss, no
muss and no bother". Reading email seemed to fill the bill until
I realize that I dont seem to have a slick way to make that happen.
Maybe something in Java running in the database?
Of course there is that proverb about the relative velocity of fools and
fearful angels, so tell me more about why I could be stepping off a
cliff here.
#4
| |||
| |||
|
|
DA Morgan wrote: Lee wrote: I've sent email from pl/sql with utl_smtp and with 10g's utl_mail; but now I want to do the inverse, i.e. I want to READ email from pl/sql. The idea is to set up a dedicated email account. Users could send stereotyped messages to that account, and the pl/sql routine would read the mail, parse the messages and do the needful. As far as I can tell, utl_mail will send, but not receive email. I can think of some Rube Golberg workarounds but can anyone point the way to a "no fuss" way to read simple text emails? Thanks in advance If you think it is a good idea to send emails, across the web, from some Microsoft Outlook client directly into an Oracle database I am sure we can recommend a good 12 step program for you. As you yourself point out, the email winds up as data somwhere; so why would it be worse to send email to an automated agent than sending email to a human agent or just reading an input file as data? Why would reading email to drive a script from inside a stored procedure be more dangerous than running a static batch script or a script that takes input from a human or from a data file? I assume you're concerned about sql injection attacks or maybe some sort of spam and/or spoofing, or even an attempt to "flood" the system a la DDOS attacks? Maybe I'm not being sufficiently imaginative or paranoid, but I cant see how the sort of scheme I'm thinking of is more dangerous than crossing the street. Everybody and his uncle has a listserv that runs on commands sent in by email, so why is that setting off alarm bells? I can use an http callout to get data from anywhere on the planet. There are "rest"-full web services, and SOAP interfaces and all sorts of ways to have all kinds of heaven-knows-what get presented as input. A routine that parses stereotyped email messages and deliveres canned reports in response seems pretty benign. Or am I living in a fools paradise? Incoming emails are stored somewhere. Find the location. Read them using anything from UTL_FILE to whatever. My Oracle server is running on a different box from my email server, so the trick is to get the data from the mail server to the oracle box. If I dont mind having more moving parts, there are plenty of ways to get from there to here; but I was hoping for something with "no fuss, no muss and no bother". Reading email seemed to fill the bill until I realize that I dont seem to have a slick way to make that happen. Maybe something in Java running in the database? Of course there is that proverb about the relative velocity of fools and fearful angels, so tell me more about why I could be stepping off a cliff here. |
which was able to receive email (and yes: in the database). It became part
of Oracle Interoffice (which died some years later). Don't know if it's
still around as a product, but it is stil there as a part of Oracle
Collaboration Suite (able to receive mail in the database). I had a customer
who installed Coll. Suite for this purpose solely: they wanted to be able to
start processes based on email.
Installing OCS is quite a fuss, keeping it running even more, but it may be
worth a try. You'll have to pay for it, though.
Oracle Interconnect had an SMTP adapter (but that was in 2001). We never got
it working, iirc it could not handle attachments.Could be part of BPEL now
(does someone know?).
Shakespeare
#5
| |||
| |||
|
|
"Lee" <Lee (AT) JamToday (DOT) com> schreef in bericht news:fgu3sr$s28$1 (AT) reader1 (DOT) panix.com... DA Morgan wrote: Lee wrote: I've sent email from pl/sql with utl_smtp and with 10g's utl_mail; but now I want to do the inverse, i.e. I want to READ email from pl/sql. The idea is to set up a dedicated email account. Users could send stereotyped messages to that account, and the pl/sql routine would read the mail, parse the messages and do the needful. As far as I can tell, utl_mail will send, but not receive email. I can think of some Rube Golberg workarounds but can anyone point the way to a "no fuss" way to read simple text emails? Thanks in advance If you think it is a good idea to send emails, across the web, from some Microsoft Outlook client directly into an Oracle database I am sure we can recommend a good 12 step program for you. As you yourself point out, the email winds up as data somwhere; so why would it be worse to send email to an automated agent than sending email to a human agent or just reading an input file as data? Why would reading email to drive a script from inside a stored procedure be more dangerous than running a static batch script or a script that takes input from a human or from a data file? I assume you're concerned about sql injection attacks or maybe some sort of spam and/or spoofing, or even an attempt to "flood" the system a la DDOS attacks? Maybe I'm not being sufficiently imaginative or paranoid, but I cant see how the sort of scheme I'm thinking of is more dangerous than crossing the street. Everybody and his uncle has a listserv that runs on commands sent in by email, so why is that setting off alarm bells? I can use an http callout to get data from anywhere on the planet. There are "rest"-full web services, and SOAP interfaces and all sorts of ways to have all kinds of heaven-knows-what get presented as input. A routine that parses stereotyped email messages and deliveres canned reports in response seems pretty benign. Or am I living in a fools paradise? Incoming emails are stored somewhere. Find the location. Read them using anything from UTL_FILE to whatever. My Oracle server is running on a different box from my email server, so the trick is to get the data from the mail server to the oracle box. If I dont mind having more moving parts, there are plenty of ways to get from there to here; but I was hoping for something with "no fuss, no muss and no bother". Reading email seemed to fill the bill until I realize that I dont seem to have a slick way to make that happen. Maybe something in Java running in the database? Of course there is that proverb about the relative velocity of fools and fearful angels, so tell me more about why I could be stepping off a cliff here. Back in the nineties, Oracle used to have a product called Oracle Mail, which was able to receive email (and yes: in the database). It became part of Oracle Interoffice (which died some years later). Don't know if it's still around as a product, but it is stil there as a part of Oracle Collaboration Suite (able to receive mail in the database). I had a customer who installed Coll. Suite for this purpose solely: they wanted to be able to start processes based on email. Installing OCS is quite a fuss, keeping it running even more, but it may be worth a try. You'll have to pay for it, though. Oracle Interconnect had an SMTP adapter (but that was in 2001). We never got it working, iirc it could not handle attachments.Could be part of BPEL now (does someone know?). Shakespeare |
Integration InterConnect. But it can only handle IMAP4, no POP3.
I think that was our problem back then......
Shakespeare
#6
| |||
| |||
|
|
Back in the nineties, Oracle used to have a product called Oracle Mail, which was able to receive email (and yes: in the database). It became part of Oracle Interoffice (which died some years later). Don't know if it's still around as a product, but it is stil there as a part of Oracle Collaboration Suite (able to receive mail in the database). I had a customer who installed Coll. Suite for this purpose solely: they wanted to be able to start processes based on email. Installing OCS is quite a fuss, keeping it running even more, but it may be worth a try. You'll have to pay for it, though. Oracle Interconnect had an SMTP adapter (but that was in 2001). We never got it working, iirc it could not handle attachments.Could be part of BPEL now (does someone know?). |
Its beginning to look as if I'm not going to be able to do any better
than my friend Rube (Golberg) after all.
#7
| |||
| |||
|
|
"shakespeare" <whatsin (AT) xs4all (DOT) nl> schreef in bericht news:4732d249$0$244$e4fe514c (AT) news (DOT) xs4all.nl... "Lee" <Lee (AT) JamToday (DOT) com> schreef in bericht news:fgu3sr$s28$1 (AT) reader1 (DOT) panix.com... DA Morgan wrote: Lee wrote: I've sent email from pl/sql with utl_smtp and with 10g's utl_mail; but now I want to do the inverse, i.e. I want to READ email from pl/sql. The idea is to set up a dedicated email account. Users could send stereotyped messages to that account, and the pl/sql routine would read the mail, parse the messages and do the needful. As far as I can tell, utl_mail will send, but not receive email. I can think of some Rube Golberg workarounds but can anyone point the way to a "no fuss" way to read simple text emails? Thanks in advance If you think it is a good idea to send emails, across the web, from some Microsoft Outlook client directly into an Oracle database I am sure we can recommend a good 12 step program for you. As you yourself point out, the email winds up as data somwhere; so why would it be worse to send email to an automated agent than sending email to a human agent or just reading an input file as data? Why would reading email to drive a script from inside a stored procedure be more dangerous than running a static batch script or a script that takes input from a human or from a data file? I assume you're concerned about sql injection attacks or maybe some sort of spam and/or spoofing, or even an attempt to "flood" the system a la DDOS attacks? Maybe I'm not being sufficiently imaginative or paranoid, but I cant see how the sort of scheme I'm thinking of is more dangerous than crossing the street. Everybody and his uncle has a listserv that runs on commands sent in by email, so why is that setting off alarm bells? I can use an http callout to get data from anywhere on the planet. There are "rest"-full web services, and SOAP interfaces and all sorts of ways to have all kinds of heaven-knows-what get presented as input. A routine that parses stereotyped email messages and deliveres canned reports in response seems pretty benign. Or am I living in a fools paradise? Incoming emails are stored somewhere. Find the location. Read them using anything from UTL_FILE to whatever. My Oracle server is running on a different box from my email server, so the trick is to get the data from the mail server to the oracle box. If I dont mind having more moving parts, there are plenty of ways to get from there to here; but I was hoping for something with "no fuss, no muss and no bother". Reading email seemed to fill the bill until I realize that I dont seem to have a slick way to make that happen. Maybe something in Java running in the database? Of course there is that proverb about the relative velocity of fools and fearful angels, so tell me more about why I could be stepping off a cliff here. Back in the nineties, Oracle used to have a product called Oracle Mail, which was able to receive email (and yes: in the database). It became part of Oracle Interoffice (which died some years later). Don't know if it's still around as a product, but it is stil there as a part of Oracle Collaboration Suite (able to receive mail in the database). I had a customer who installed Coll. Suite for this purpose solely: they wanted to be able to start processes based on email. Installing OCS is quite a fuss, keeping it running even more, but it may be worth a try. You'll have to pay for it, though. Oracle Interconnect had an SMTP adapter (but that was in 2001). We never got it working, iirc it could not handle attachments.Could be part of BPEL now (does someone know?). Shakespeare Checked it out: there still an SMTP adapter in Oracle Application Server Integration InterConnect. But it can only handle IMAP4, no POP3. I think that was our problem back then...... Shakespeare |
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#8
| ||||||||
| ||||||||
|
|
DA Morgan wrote: Lee wrote: I've sent email from pl/sql with utl_smtp and with 10g's utl_mail; but now I want to do the inverse, i.e. I want to READ email from pl/sql. The idea is to set up a dedicated email account. Users could send stereotyped messages to that account, and the pl/sql routine would read the mail, parse the messages and do the needful. As far as I can tell, utl_mail will send, but not receive email. I can think of some Rube Golberg workarounds but can anyone point the way to a "no fuss" way to read simple text emails? Thanks in advance If you think it is a good idea to send emails, across the web, from some Microsoft Outlook client directly into an Oracle database I am sure we can recommend a good 12 step program for you. As you yourself point out, the email winds up as data somwhere; so why would it be worse to send email to an automated agent than sending email to a human agent or just reading an input file as data? |
and a wide variety of diseases. In the database you would have to write
your own: Reinvent the wheel.
Quote:
|
Why would reading email to drive a script from inside a stored procedure be more dangerous than running a static batch script or a script that takes input from a human or from a data file? |
is far more that could happen here than just that. Wouldn't be hard to
create one heck of a good denial of service attack with email. I don't
think you, and even 100 of your friends can type that fast.
Quote:
|
I assume you're concerned about sql injection attacks or maybe some sort of spam and/or spoofing, or even an attempt to "flood" the system a la DDOS attacks? |
Quote:
|
Maybe I'm not being sufficiently imaginative or paranoid, but I cant see how the sort of scheme I'm thinking of is more dangerous than crossing the street. Everybody and his uncle has a listserv that runs on commands sent in by email, so why is that setting off alarm bells? |
or two people trying to hit you. When you get on the internet you have
every kid with a keyboard trying to take you out. The odds are not in
your favor.
Quote:
|
I can use an http callout to get data from anywhere on the planet. There are "rest"-full web services, and SOAP interfaces and all sorts of ways to have all kinds of heaven-knows-what get presented as input. A routine that parses stereotyped email messages and deliveres canned reports in response seems pretty benign. |
DBMS_NETWORK_ACL_ADMIN package (ACL = Access Control List).
Quote:
|
Or am I living in a fools paradise? |
Quote:
|
Incoming emails are stored somewhere. Find the location. Read them using anything from UTL_FILE to whatever. My Oracle server is running on a different box from my email server, so the trick is to get the data from the mail server to the oracle box. |
are authorized to do so.
Quote:
|
Of course there is that proverb about the relative velocity of fools and fearful angels, so tell me more about why I could be stepping off a cliff here. |
companies that intentionally expose their databases to the public.
Amazon.com, Ebay, etc. You want to duplicate that effort? Because
clicking on an email client is too much work?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#9
| |||
| |||
|
|
shakespeare wrote: "shakespeare" <whatsin (AT) xs4all (DOT) nl> schreef in bericht news:4732d249$0$244$e4fe514c (AT) news (DOT) xs4all.nl... "Lee" <Lee (AT) JamToday (DOT) com> schreef in bericht news:fgu3sr$s28$1 (AT) reader1 (DOT) panix.com... DA Morgan wrote: Lee wrote: I've sent email from pl/sql with utl_smtp and with 10g's utl_mail; but now I want to do the inverse, i.e. I want to READ email from pl/sql. The idea is to set up a dedicated email account. Users could send stereotyped messages to that account, and the pl/sql routine would read the mail, parse the messages and do the needful. As far as I can tell, utl_mail will send, but not receive email. I can think of some Rube Golberg workarounds but can anyone point the way to a "no fuss" way to read simple text emails? Thanks in advance If you think it is a good idea to send emails, across the web, from some Microsoft Outlook client directly into an Oracle database I am sure we can recommend a good 12 step program for you. As you yourself point out, the email winds up as data somwhere; so why would it be worse to send email to an automated agent than sending email to a human agent or just reading an input file as data? Why would reading email to drive a script from inside a stored procedure be more dangerous than running a static batch script or a script that takes input from a human or from a data file? I assume you're concerned about sql injection attacks or maybe some sort of spam and/or spoofing, or even an attempt to "flood" the system a la DDOS attacks? Maybe I'm not being sufficiently imaginative or paranoid, but I cant see how the sort of scheme I'm thinking of is more dangerous than crossing the street. Everybody and his uncle has a listserv that runs on commands sent in by email, so why is that setting off alarm bells? I can use an http callout to get data from anywhere on the planet. There are "rest"-full web services, and SOAP interfaces and all sorts of ways to have all kinds of heaven-knows-what get presented as input. A routine that parses stereotyped email messages and deliveres canned reports in response seems pretty benign. Or am I living in a fools paradise? Incoming emails are stored somewhere. Find the location. Read them using anything from UTL_FILE to whatever. My Oracle server is running on a different box from my email server, so the trick is to get the data from the mail server to the oracle box. If I dont mind having more moving parts, there are plenty of ways to get from there to here; but I was hoping for something with "no fuss, no muss and no bother". Reading email seemed to fill the bill until I realize that I dont seem to have a slick way to make that happen. Maybe something in Java running in the database? Of course there is that proverb about the relative velocity of fools and fearful angels, so tell me more about why I could be stepping off a cliff here. Back in the nineties, Oracle used to have a product called Oracle Mail, which was able to receive email (and yes: in the database). It became part of Oracle Interoffice (which died some years later). Don't know if it's still around as a product, but it is stil there as a part of Oracle Collaboration Suite (able to receive mail in the database). I had a customer who installed Coll. Suite for this purpose solely: they wanted to be able to start processes based on email. Installing OCS is quite a fuss, keeping it running even more, but it may be worth a try. You'll have to pay for it, though. Oracle Interconnect had an SMTP adapter (but that was in 2001). We never got it working, iirc it could not handle attachments.Could be part of BPEL now (does someone know?). Shakespeare Checked it out: there still an SMTP adapter in Oracle Application Server Integration InterConnect. But it can only handle IMAP4, no POP3. I think that was our problem back then...... Shakespeare Get a Collab Suite license. That's another solution. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org |
receiving mail, but what the ... ,right?
Shakespeare
#10
| |||
| |||
|
|
shakespeare wrote: Back in the nineties, Oracle used to have a product called Oracle Mail, which was able to receive email (and yes: in the database). It became part of Oracle Interoffice (which died some years later). Don't know if it's still around as a product, but it is stil there as a part of Oracle Collaboration Suite (able to receive mail in the database). I had a customer who installed Coll. Suite for this purpose solely: they wanted to be able to start processes based on email. Installing OCS is quite a fuss, keeping it running even more, but it may be worth a try. You'll have to pay for it, though. Oracle Interconnect had an SMTP adapter (but that was in 2001). We never got it working, iirc it could not handle attachments.Could be part of BPEL now (does someone know?). Thanks for the input. Its beginning to look as if I'm not going to be able to do any better than my friend Rube (Golberg) after all. |
write a POP3 or IMAP client in PL/SQL using UTL_TCP.
Both protocols are well defined standards.
This would allow you to pull messages directly into a store procedure.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.