Results 1 to 3 of 3

Thread: Mail and Oracle

  1. #1
    Join Date
    Nov 2002
    Location
    Holland
    Posts
    11

    Unanswered: Mail and Oracle

    Hi,

    I am using Oracle 8.1.7 on a Windows 2000 Server.
    I want to generate an E-mail when a specific table is updated.
    Can someone tell me what the best way is to do this ?

    Thanks.

    Cecodeel

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Mail and Oracle

    Originally posted by cecodeel
    Hi,

    I am using Oracle 8.1.7 on a Windows 2000 Server.
    I want to generate an E-mail when a specific table is updated.
    Can someone tell me what the best way is to do this ?

    Thanks.

    Cecodeel
    Create an AFTER UPDATE trigger on the table. Use the UTL_SMTP package to send the email. However, there are 2 issues with this:
    1) UTL_SMTP is pretty slow, which will impact your performance
    2) UTL_SMTP will send out the email even if the update is rolled back.

    These issues can be overcome by using DBMS_JOB as follows:
    - Create a stored procedure that uses UTL_SMTP to send an email
    - In the trigger, call DBMS_JOB.SUBMIT to immediately run your stored procedure

    This overcomes the above issues because:
    1) The job is run asynchronously, so the trigger does not have to wait for the job to complete
    2) The job only runs when the update is committed, and not at all if it is rolled back.

    If you have Tom Kyte's book "Expert One On One Oracle", he shows a complete implementation of this approach.

  3. #3
    Join Date
    Nov 2002
    Location
    Holland
    Posts
    11

    Re: Mail and Oracle

    Originally posted by andrewst
    Create an AFTER UPDATE trigger on the table. Use the UTL_SMTP package to send the email. However, there are 2 issues with this:
    1) UTL_SMTP is pretty slow, which will impact your performance
    2) UTL_SMTP will send out the email even if the update is rolled back.

    These issues can be overcome by using DBMS_JOB as follows:
    - Create a stored procedure that uses UTL_SMTP to send an email
    - In the trigger, call DBMS_JOB.SUBMIT to immediately run your stored procedure

    This overcomes the above issues because:
    1) The job is run asynchronously, so the trigger does not have to wait for the job to complete
    2) The job only runs when the update is committed, and not at all if it is rolled back.

    If you have Tom Kyte's book "Expert One On One Oracle", he shows a complete implementation of this approach.

    Ok
    Thanks

    Cecodeel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •