Send email through procedure in PL/SQL

15 pts.
Tags:
PL/SQL
SQL
Dear Team,
I want to make a procedure through which I can send email to my team mail id. Actually there are number of procedures running daily in our system. So I want to send the status of each procedure means if procedure is getting success,failed or running. If the procedure is in running state I have to show the running time in that email. And also if the procedure is failed then i have to show the error message in that email. Actually one table is present in our database in which procedure id, date, start_time, end_time, status, error desc columns are there. I shared the details below.
proc id   date        start_time               end_time                status       error desc
  1      6/7/2017    7/5/2017 9:55:16 AM     7/6/2017 1:36:25 AM      SUCCESS   
  2      6/28/2017   6/29/2017 8:30:02 AM    6/29/2017 2:20:15 PM     FAIL         -1555 - ORA-01555: snapshot too old: rollback segment number 334 with name "_SYSSMU334_1817651691$" too small
ORA-02063:
  3      7/5/2017    7/6/2017 9:34:54 AM                              RUNNING
As I said, I have to show all the 3 types of status in the email which is to be sent through procedure. Kindly help me regarding this.


Software/Hardware used:
I am using toad for oracle software

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    It is not completely clear what you need help with.

    The task you mention includes several things you need to know how to do, so it is not clear whether you only need help with the email sending, or something else as well.
    For the e-mail part, a Google search would give you lots of examples. Sending mail from the database is not difficult.

    If that is not what you want, or at least, not only what you need help with, you need to ask more specific questions, and let us know exactly the parts of the task you don't know how to do.
    84,310 pointsBadges:
    report
  • Biswa92
    Thanks for your Reply. Firstly I want to a make a procedure which gives all the status like 'success','FAIL','Running' using procedure id as i mentioned all this above.
    After getting that status i want to show the status through auto mail.
    This auto mail should be done through Procedure using PL/SQL.
    Kindly help me to do that.
    In my mail the format should be as given below.
    The below parameters I need in that mail. I hope you understand my point.
    proc id   date        start_time               end_time                status       error desc
      1      6/7/2017    7/5/2017 9:55:16 AM     7/6/2017 1:36:25 AM      SUCCESS   
      2      6/28/2017   6/29/2017 8:30:02 AM    6/29/2017 2:20:15 PM     FAIL         -1555 - ORA-01555: snapshot too old: rollback segment number 334 with name "_SYSSMU334_1817651691$" too small
    ORA-02063:
      3      7/5/2017    7/6/2017 9:34:54 AM                              RUNNING
    15 pointsBadges:
    report
  • carlosdl
    I understand what you want, but you didn't clarify which parts you need help with.

    In general, you would need to do this:

    1) Create a procedure to
    • Query your table with a cursor and build your email message body by concatenating the data from each row as desired.
    • Use the UTL_MAIL package to send the email message.
    2) Use the scheduler to define when you want the procedure to be executed.

    Here's an article you might find helpful: Sending Email From Oracle

    84,310 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: