Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1055
  • Last Modified:

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
0
mohammadzahid
Asked:
mohammadzahid
  • 4
  • 3
1 Solution
 
jrb1Commented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
jrb1Commented:
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
 
jrb1Commented:
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
 
mohammadzahidAuthor Commented:
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!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now