?
Solved

Pl/Sql trigger to send email of error message.

Posted on 2005-02-26
8
Medium Priority
?
1,052 Views
Last Modified: 2012-05-05
Hello Experts,

I am looking for some ideas or logic on how to create a Oracle pl/sql trigger that retrieve error messages from a table and then send error message to a user via email.  

Thanks,

-mohammadzahid
0
Comment
Question by:mohammadzahid
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 13412526
What is going to trigger the message?  The insert to the table or something else?

If you want the error message table to trigger the email, here is some code.  It is coded to do send via a job.  If you don't do this, if you insert something into the error table and then rollback, the email would still be sent.  This way, you only get the email once the data is committed.

create trigger send_errors
after insert on error_table for each row
declare
   v_job number;
begin
   dbms_job.submit( v_job, 'SEND_EMAIL( JOB );' );
   insert into email_errors values ( l_job, :new.error_user, :new.error_message_text);
end;
/


and then...

 create  procedure send_email( p_job in number )
is
   v_rec email_errors%rowtype;
begin
   select * into v_rec from email_errors where id = p_job;

   ... format an send email using utl_smtp;

   delete from email_errors where id = p_job;
end;
/

If you don't care about waiting for the commit, here is an example of a trigger sending an email, and it includes the code for using utl_smtp.
0
 
LVL 11

Author Comment

by:mohammadzahid
ID: 13412604
Sorry, my plsql programming skills are not up to speed. Could you please provide table ddl used in the trigger and please explain the value of the v_job variable defined in the trigger. How v_job variable is getting value passed in the dbms_job.submit package. Is v_job value getting data from the error_table?

Thanks!

mohammadzahid
0
 
LVL 11

Author Comment

by:mohammadzahid
ID: 13412672
Sorry for not answering your question asked in your response. I am not sure if it will be insert or something else. I am discussing that with the DBA's. I personally like the idea of insert in table at row level but in the last meeting they suggested that we retain error log in the table and use a procedure to print the data(error messages) in a report.

Thanks!

- mohammadzahid


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:jrb1
ID: 13412673
The dbms_job.submit will generate a value for v_job.  That is the source.  I meant for the insert to reference this, not "l_job".  So v_job will be inserted into the error table, and when the procedure to send the message processes, it will read the errors associated with the job number.  (The send_email(job) says to pass the job number to the routine.)

I'm not sure what kind of data you are going to be sending (1 long string?), but in my example I was thinking something like:

create table email_errors
( id number primary key,
  user varchar2(30) not null,
  error_text varchar2(1000) not null
)

So when you send the email, you lookup the email address based on the userid and then send the text stored in error_text.
0
 
LVL 11

Author Comment

by:mohammadzahid
ID: 13412695
I am planning on sending Oracle error message in the table and then send ora-xxx message via email using utl_smtp package. Does this sound like something that can be done?

0
 
LVL 25

Expert Comment

by:jrb1
ID: 13412983
Certainly, that should be pretty easy to do.  I'd get away from the JOB portion of my example, however.  It won't help if you are using it on errors.  What types of errors are you trapping and who are you sending the emails to?
0
 
LVL 11

Accepted Solution

by:
sujit_kumar earned 2000 total points
ID: 13417014
--Create this procedure.

CREATE OR REPLACE procedure SEND_MAIL

    (

    FROM_USER in varchar2 default null,

    TO_USER in varchar2 default null,

    MESSAGE_SUBJECT in varchar2 default '[No subjectspecified]',

    MESSAGE_BODY in varchar2 default '[No messagespecified]'

    )

is

    SMTP_CONNECTION UTL_SMTP.CONNECTION;

    SMTP_REPLY UTL_SMTP.REPLY;

    CRLF varchar2(2) := CHR(13)||CHR(10);

    MESSAGE varchar2(4000);

begin



MESSAGE :=

    'From: '|| FROM_USER || CRLF ||

    'To: '|| TO_USER || CRLF ||

    'Subject: '|| MESSAGE_SUBJECT || CRLF ||

    '' || CRLF ||

    MESSAGE_BODY || CRLF;



    SMTP_REPLY :=

utl_smtp.open_connection(host=>'mailserver.doamin.com',port=>25,c=>SMTP_CONNECTION);

    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);

    dbms_output.put_line('Connection host:'||SMTP_CONNECTION.host);

    dbms_output.put_line('Connection port:'||SMTP_CONNECTION.port);





    SMTP_REPLY :=utl_smtp.command(c=>SMTP_CONNECTION,cmd=>'HELO',arg=>null);

    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);



    SMTP_REPLY :=utl_smtp.command(c=>SMTP_CONNECTION,cmd=>'MAILFROM:',arg=>FROM_USER);

    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);



    SMTP_REPLY :=utl_smtp.command(c=>SMTP_CONNECTION,cmd=>'RCPTTO:',arg=>TO_USER);

    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);



    SMTP_REPLY :=utl_smtp.open_data(c=>SMTP_CONNECTION);

    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);



   utl_smtp.write_data(c=>SMTP_CONNECTION,data=>MESSAGE);



    SMTP_REPLY :=utl_smtp.close_data(c=>SMTP_CONNECTION);

    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);



    SMTP_REPLY :=utl_smtp.command(c=>SMTP_CONNECTION,cmd=>'QUIT',arg=>null);

    dbms_output.put_line('Reply: '||SMTP_REPLY.code||' --'||SMTP_REPLY.text);



--utl_smtp.command(mail_conn, 'AUTH LOGIN');

EXCEPTION

  WHEN OTHERS THEN
    dbms_output.put_line(dbms_utility.format_error_stack);
    dbms_output.put_line(dbms_utility.format_call_stack);

end;
/


-- Then Create a trigger which will send the error to the procedure. And the procedure will e-mail it.

Like,

declare
   err varchar2(3000);
begin
...
..
exception
 when others then
  err := substr(sqlerrm, 1, 2999);
  SEND_MAIL(from@yahoo.com, to@rediffmail.com, 'ERROR', err);
end;
0
 
LVL 11

Author Comment

by:mohammadzahid
ID: 13441908
Thanks guys for providing valuable solution. I will try it out this week and close this thread if successfull.

-mohammadzahid

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question