OraFAQ Forum: SQL & PL/SQL » how to generate an email on a database event?

Home » SQL & PL/SQL » SQL & PL/SQL » how to generate an email on a database event? (Oracle Database, 10g, Windows Server-2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to generate an email on a database event? [message #507435]

Sun, 15 May 2011 04:37

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?

Report message to a moderator

Re: how to generate an email on a database event? [message #507439 is a reply to message #507435]

Sun, 15 May 2011 05:54

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: how to generate an email on a database event? [message #507452 is a reply to message #507439]

Sun, 15 May 2011 12:39

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

Embarassed

Shocked

Report message to a moderator

Re: how to generate an email on a database event? [message #507453 is a reply to message #507452]

Sun, 15 May 2011 13:09

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: how to generate an email on a database event? [message #507454 is a reply to message #507452]

Sun, 15 May 2011 13:09

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: how to generate an email on a database event? [message #507455 is a reply to message #507453]

Sun, 15 May 2011 14:56

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

Shocked

Report message to a moderator

Re: how to generate an email on a database event? [message #507456 is a reply to message #507455]

Sun, 15 May 2011 15:57

ThomasG
Messages: 3211
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

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: how to generate an email on a database event? [message #507464 is a reply to message #507456]

Mon, 16 May 2011 00:37

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?

Report message to a moderator

Re: how to generate an email on a database event? [message #658455 is a reply to message #507464]

Thu, 15 December 2016 00:16

Mohammed Ayaz
Messages: 2
Registered: December 2016

Junior Member

How to send mail whenever job runs successfully

Report message to a moderator

Re: how to generate an email on a database event? [message #658459 is a reply to message #658455]

Thu, 15 December 2016 01:09

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Reading the topic and trying what is suggested.

Report message to a moderator

Re: how to generate an email on a database event? [message #658472 is a reply to message #507464]

Thu, 15 December 2016 07:08

EdStevens
Messages: 1376
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??????

Report message to a moderator

Re: how to generate an email on a database event? [message #658473 is a reply to message #658472]

Thu, 15 December 2016 07:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Ed,

Thread is only 5+ YEARS old

Report message to a moderator

Re: how to generate an email on a database event? [message #658474 is a reply to message #658455]

Thu, 15 December 2016 07:32

Solomon Yakobson
Messages: 3273
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

EdStevens
Messages: 1376
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.

Report message to a moderator

Previous Topic:	dynamic sql to grant privilege
Next Topic:	DEFAULT Value Behavior

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 17 21:49:09 CDT 2024