Re: Handling exceptions from table columns values and emailing ASAP Please

On 28 Jul 2006 06:14:09 -0700, clinttoris_at_hotmail.com wrote:

>
>clinttoris_at_hotmail.com wrote:
>> Howdie,
>>
>> I am using Oracle 9 and UTL_SMTP pacakge.
>>
>> Could someone please help me with my code. I have a database table
>> with lets just say 2 colums EAddress and CC. In these fields are email
>> addresses that I have populated manually using the Insert into tableX
>> Values('jsheco_at_hotmail.com', 'goracle_at_yahoo.ca').
>>
>> If any one of the email address are invalid I need my exception handler
>> to send me an email telling me the email address that caused a problem
>> as well as the error message and not send out the message to the
>> EAdress or CC. My exception handler is inside a loop so it will do
>> this for all rows. Here is my stab at the code. If anyone could help
>> me code this much appreciated.
>>
>> EXCEPTION
>> WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
>> UTL_SMTP.MAIL(mail_conn, 'MrAdmin_at_yahoo.ca');
>> raise_application_error(-20000, 'Failed to send mail due to the
>> following error: ' || sqlerrm);
>> END;
>> END LOOP;
>>
>> Thanks for looking at this.
>
>Sorry experts,
>
>Just is a bit of a rush. Does anyone have any input into this? Thanks
>again.

As this is a volunteer forum, your request is unreasonable. Obviously you also didn't test your code, because in that case you would have noticed, that your call to raise_application_error would cause the code to crash.

--
Sybrand Bakker, Senior Oracle DBA