Pl/Sql trigger to send email of error message.

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
LVL 11
mohammadzahidAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jrb1senior developerCommented:
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
mohammadzahidAuthor Commented:
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
mohammadzahidAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jrb1senior developerCommented:
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
mohammadzahidAuthor Commented:
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
jrb1senior developerCommented:
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
sujit_kumarCommented:
--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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mohammadzahidAuthor Commented:
Thanks guys for providing valuable solution. I will try it out this week and close this thread if successfull.

-mohammadzahid

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.