Home » SQL & PL/SQL » SQL & PL/SQL » how to generate an email on a database event? (Oracle Database, 10g, Windows Server-2003)
how to generate an email on a database event? [message #507435] Sun, 15 May 2011 04:37 Go to next message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member

Dear gurus...I've heard about diferent ways of generating email in response to a database event e.g
1 - Using UTL_MAIL package.
OR
2 - Event Driven Reporting feature of 10g Database and using SRW.Run_Report package to generate and mail a report.

Now I'm confused that how should I accomplish the following tasks:
(i) - How to generate an email after Insert/Update/Delete on a table and acknowledge the concerned users about that event?
(ii)- How to attach a text file with that email?
Re: how to generate an email on a database event? [message #507439 is a reply to message #507435] Sun, 15 May 2011 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
i) Using TRIGGER
ii) UTL_MAIL package allows to attach a file. There are many examples here and on the web just search for them.

Regards
Michel
Re: how to generate an email on a database event? [message #507452 is a reply to message #507439] Sun, 15 May 2011 12:39 Go to previous messageGo to next message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member

Dear Michel...You mean that I'he to use both Event-Driven as well as UTL_MAIL package for these jobs? Can u give me please a link to start from? Actually I've searched for these problems on web and I got so many answers that I was confused that which way I should follow. Embarassed Shocked
Re: how to generate an email on a database event? [message #507453 is a reply to message #507452] Sun, 15 May 2011 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
>(ii)- How to attach a text file with that email?
specific answer depends upon details NOT provided.

where does text file reside? on client system or on DB Server?
Exactly how is end user interfaced to the DB Server? 3-tier via web browser?
Re: how to generate an email on a database event? [message #507454 is a reply to message #507452] Sun, 15 May 2011 13:09 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you searrch for TRIGGER and UTL_MAIL?
I'm very surprised you didn't find anything.

Here's a link where you will find many thing:
http://www.oracle.com/pls/db102/homepage

Here's another one:
http://www.orafaq.com/forum/s/102589/

Regards
Michel
Re: how to generate an email on a database event? [message #507455 is a reply to message #507453] Sun, 15 May 2011 14:56 Go to previous messageGo to next message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member

Michel...3-Tier structure is working here with 10g Aps services in middle and 10g Db in 1st Tier. Text file resides on client's system. Yes, u may be surprised but I've been in "Catch in 22 situations" because online search provided me 3 kinds of solutions:
1-UTL_SMTP
2-UTL_MAIL
3-SRW.Run_Report in Event Driven Reporting
I've been unable to decide which solution would work for me because I've to accomplish 2 types of job.
i)-Firstly I need to inform via email to some users about any update to some specific columns of a table.
ii)-2ndly, n little bit more complex, is that when user updates a record in a table, a text file including that record will be generated(its my choice whether this file should be created on Apps Server or on client machine) like that of TEXT_IO and mail to some other users.
So this is all the story, what do u suggest now? Shocked
Re: how to generate an email on a database event? [message #507456 is a reply to message #507455] Sun, 15 May 2011 15:57 Go to previous messageGo to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What do you mean with "Text file resides on client's system" ?

A "text file on a client system" has nothing to do with database events.

I would pick one place where everything takes place. Either

- The Database server
- The Apps server
- The Client

The solution would depend on where you want it to happen.

Also,

Quote:

when user updates a record in a table, a text file including that record will be generated


Why? If the goal is just to send that file via mail, forget about the intermediate step of creating a file. Put the change data directly in a mail. Or into a temporary or log table.

[Updated on: Sun, 15 May 2011 15:57]

Report message to a moderator

Re: how to generate an email on a database event? [message #507457 is a reply to message #507456] Sun, 15 May 2011 16:57 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
>1-UTL_SMTP
runs on DB server & first available with V8.1.6
does not directly support file attachments, but they are possible with additional programming

>2-UTL_MAIL
runs on DB server, first available with V10 & supports attachments but they must reside on DB Server

>3-SRW.Run_Report in Event Driven Reporting
runs on Reports Server & IIRC does not support file attachements

Pick the tool that best serves the requirements
Re: how to generate an email on a database event? [message #507464 is a reply to message #507456] Mon, 16 May 2011 00:37 Go to previous messageGo to next message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member

Thanx BlackSwan for differentiating these 3 options.
And Thomas, I need to create that text file because I'm writing updated data into a Txt file and that Txt file is needed to be sent to a client who imports this Txt file into his database by himself.
So, if someway, I'm able to create Txt file after a DB event and mail it automatically, then surely it'll relax my user from this job and reduce delays in sending mails. Its no problem if all of this takes place on my server.
Lastly what package should I use if I send only some data in email, not a file attached with it?
Re: how to generate an email on a database event? [message #658455 is a reply to message #507464] Thu, 15 December 2016 00:16 Go to previous messageGo to next message
Mohammed Ayaz
Messages: 2
Registered: December 2016
Junior Member
How to send mail whenever job runs successfully
Re: how to generate an email on a database event? [message #658459 is a reply to message #658455] Thu, 15 December 2016 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Reading the topic and trying what is suggested.

Re: how to generate an email on a database event? [message #658472 is a reply to message #507464] Thu, 15 December 2016 07:08 Go to previous messageGo to next message
EdStevens
Messages: 976
Registered: September 2013
Senior Member
mazam wrote on Mon, 16 May 2011 00:37
Thanx BlackSwan for differentiating these 3 options.
And Thomas, I need to create that text file because I'm writing updated data into a Txt file and that Txt file is needed to be sent to a client who imports this Txt file into his database by himself.
So, if someway, I'm able to create Txt file after a DB event and mail it automatically, then surely it'll relax my user from this job and reduce delays in sending mails. Its no problem if all of this takes place on my server.
Lastly what package should I use if I send only some data in email, not a file attached with it?
WHAATT??

You insert data into a table, then write that data to a text file and send it to the user ... so that he can "import" it into .... the database????? Where you've already inserted it??????
Re: how to generate an email on a database event? [message #658473 is a reply to message #658472] Thu, 15 December 2016 07:23 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
Ed,

Thread is only 5+ YEARS old
Re: how to generate an email on a database event? [message #658474 is a reply to message #658455] Thu, 15 December 2016 07:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Mohammed Ayaz wrote on Thu, 15 December 2016 01:16
How to send mail whenever job runs successfully
Is it DBMS_SCHEDULER job? If so, use DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION. And don't forget to set mail server:

        DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
                                               attribute => 'email_server',
                                               value     => 'your-mail-server:your-mail-server-port'
                                              );

SY.

[Updated on: Thu, 15 December 2016 07:33]

Report message to a moderator

Re: how to generate an email on a database event? [message #658530 is a reply to message #658473] Fri, 16 December 2016 06:41 Go to previous message
EdStevens
Messages: 976
Registered: September 2013
Senior Member
BlackSwan wrote on Thu, 15 December 2016 07:23
Ed,

Thread is only 5+ YEARS old
Ah, so it is. I failed to notice that when Mohammed Ayaz revived and hijacked the thread with his own question.
Previous Topic: dynamic sql to grant privilege
Next Topic: DEFAULT Value Behavior
Goto Forum:
  


Current Time: Mon Oct 15 16:59:40 CDT 2018