Re: Receiving email into pl/sql (follow up)

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 08 Nov 2007 13:12:21 -0800
Message-ID: <1194556336.784124_at_bubbleator.drizzle.com>

shakespeare wrote:

> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht 
> news:1194538721.437178_at_bubbleator.drizzle.com...
>> shakespeare wrote:
>>> "shakespeare" <whatsin_at_xs4all.nl> schreef in bericht 
>>> news:4732d249$0$244$e4fe514c_at_news.xs4all.nl...
>>>> "Lee" <Lee_at_JamToday.com> schreef in bericht 
>>>> news:fgu3sr$s28$1_at_reader1.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_at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
> 
> Yes, like I already pointed out... Quite a lot of overhead for just 
> receiving mail, but what the ...   ,right?
> 
> Shakespeare 

Last time I checked only $60 per named user. A bargain at twice the price.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Nov 08 2007 - 22:12:21 CET