Home » SQL & PL/SQL » SQL & PL/SQL » Email PL/SQL exception (Oracle 10g,Pl/Sql)
Email PL/SQL exception [message #342404] Fri, 22 August 2008 04:54 Go to next message
gaganzk
Messages: 36
Registered: May 2008
Member
Hi All,

I want to know is there any way through which i can mail the exception occur during procedure execution.

Waiting for your Mind Blowing Ideas Razz


Regards:
Gagan Deep Kaushal
Re: Email PL/SQL exception [message #342410 is a reply to message #342404] Fri, 22 August 2008 04:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
UTL_SMTP or UTL_MAIL
Re: Email PL/SQL exception [message #342411 is a reply to message #342404] Fri, 22 August 2008 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use utl_mail

Regards
Michel
Re: Email PL/SQL exception [message #342418 is a reply to message #342410] Fri, 22 August 2008 05:10 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
HI ,
Thanks for your Quick Response .but I need to mail my Exception String only,As i can say

Output Of this line if its not null:

DBMS_OUTPUT.PUT_LINE(sqlerrm').

Is it Possible?

Thanks:
Gagan Deep Kaushal
Re: Email PL/SQL exception [message #342419 is a reply to message #342418] Fri, 22 August 2008 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use utl_mail

Regards
Michel
Re: Email PL/SQL exception [message #342432 is a reply to message #342419] Fri, 22 August 2008 05:48 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

hi,

Here is the procedure to send the email from database.

create or replace procedure prc_send_mail (p_sender in varchar2, p_recipient in varchar2, p_message in varchar2)
    as
       l_mailhost varchar2(255) := 'mail.YourCompany.com';
       l_mail_conn utl_smtp.connection;
    begin
      l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
      utl_smtp.helo(l_mail_conn, l_mailhost);
      utl_smtp.mail(l_mail_conn, p_sender);
      utl_smtp.rcpt(l_mail_conn, p_recipient);
      utl_smtp.open_data(l_mail_conn );
      utl_smtp.write_data(l_mail_conn, p_message);
      utl_smtp.close_data(l_mail_conn );
      utl_smtp.quit(l_mail_conn);
   end;
/


regards,
Saadat Ahmad
Re: Email PL/SQL exception [message #342435 is a reply to message #342419] Fri, 22 August 2008 06:07 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
Hi,

Thanks for your Reply but i am not able to Resolve my problem.Actually i ran some procedure daily using jobs that send me mails as per their definition but one day exception occur in the procedure and i am not able to find out the problem so i need exception in my mail record .


I use this as in exception body
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (sqlerrm)

and i need to mail the output of above syntax.

Regards:
Gagan Deep Kaushal
Re: Email PL/SQL exception [message #342436 is a reply to message #342435] Fri, 22 August 2008 06:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If yuo're using DBMS_JOB, then if you remove the WHEN OTHER entirely, the error will be logged in the Alert.log for your database.

In general, a When Others like yours that simply hides all errors is a bug.



For those who can't be bothered to read the documentation, here's how you call UTL_MAIL.SEND
UTL_MAIL.SEND (
   sender      IN    VARCHAR2 CHARACTER SET ANY_CS,
   recipients  IN    VARCHAR2 CHARACTER SET ANY_CS,
   cc          IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   bcc         IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   subject     IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   message     IN    VARCHAR2 CHARACTER SET ANY_CS,
   mime_type   IN    VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
   priority    IN    PLS_INTEGER DEFAULT NULL);


You need to have set up the Smtp_Out_Server parameter before using it.
Re: Email PL/SQL exception [message #342438 is a reply to message #342435] Fri, 22 August 2008 06:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Does it not strike you as foolish to try to email the error raised when something fails to send an email?

If the problem is actually with the email server, then you're not going to find out about the problem, are you?
Re: Email PL/SQL exception [message #342443 is a reply to message #342438] Fri, 22 August 2008 06:27 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
Hi JRowbottom,

Its not so as you are considering i am sorry to give insufficient info.Now I will tell you what really happen in my case.

i use a procedure to insert some data from one schema to another and it also notify me through mail at end that i have insert every thing correctly.
then i use a job to run this procedure using dbms_job.
one day a error occur in insertion beacuse of constraint.and i did not get any mail but exception is there in procedure .Means i dont have any problem with mail server.So for better result i m looking to email that exception too.
That's what i am looking for. Cool

Thanks for your Kind attention.

Thanks:
Gagan Deep Kaushal
Re: Email PL/SQL exception [message #342449 is a reply to message #342443] Fri, 22 August 2008 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So use EXCEPTION clause to trap the error and send you an email but of course you will lose useful information unless you also send it in your mail.

Regards
Michel
Re: Email PL/SQL exception [message #342454 is a reply to message #342449] Fri, 22 August 2008 07:04 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
HI Michel,

So here is the problem with me .I am not able to find out the solution for that .
How to send exception output as mail.

I dont have any idea So please if you have any.


Thanks:
Gagan deep Kaushal

[Updated on: Fri, 22 August 2008 07:05]

Report message to a moderator

Re: Email PL/SQL exception [message #342457 is a reply to message #342454] Fri, 22 August 2008 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your problem. You know how to send a mail, you know how to use EXCEPTION (I think, I hope), now combine them.
Detail what you don't know.
Post an example.

Regards
Michel
Re: Email PL/SQL exception [message #342464 is a reply to message #342457] Fri, 22 August 2008 07:31 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
HI Michel Cadot,

I am also confused SIR Razz But Really Thanks for you Response. i Know how to mail required data but dont know how to mail EXCEPTIONs
Because i am not able to attach the output string of

DBMS_OUTPUT.PUT_LINE(sqlerrm)

into my mail.i am not able to find right syntax for it .


i follow this sequence:


Declare
Begin
Exception
End

Now when i try to use utl_smtp under Exception block it gives me error you cant use that. Sad


Regards:
Gagan Deep Kaushal

Re: Email PL/SQL exception [message #342465 is a reply to message #342464] Fri, 22 August 2008 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can call utl_smtp in exception block.
If you have an error then ou badly cal it but as you don't post the code we can't help more than saying you did it wrong.

By the way you don't put the output of "DBMS_OUTPUT.PUT_LINE(sqlerrm)" into your mail, you just put "sqlerrm".

Regards
Michel
Re: Email PL/SQL exception [message #342468 is a reply to message #342465] Fri, 22 August 2008 07:41 Go to previous message
gaganzk
Messages: 36
Registered: May 2008
Member
Hi Michel Cadot,


you are great sir i got it.

Here is the error I am using the line
"DBMS_OUTPUT.PUT_LINE(sqlerrm)"

Now its ok .

Gr888....... Smile


Thanks:
Gagan Deep Kaushal

Previous Topic: Other way of doing query
Next Topic: grouping function
Goto Forum:
  


Current Time: Thu Dec 08 10:04:40 CST 2016

Total time taken to generate the page: 0.09101 seconds