CODEWITHSUNDEEP
emailtransactionsoracle11g
eaolson
eaolson

Reputation: 15092

Is it possible to transactionally send an email in Oracle?

I have a number of cases where I do something like this:

... do some database action ...
COMMIT;
if ( commit succeeded without error ) {
    send an email
}

Is there any straightforward way to automatically do this transactionally in Oracle 11g?

About the only way I can think of to do this is to have the transaction insert the email into a table and have a job that periodically wakes up to check the table and send any emails in it.

Upvotes: 2

Views: 211

Answers (1)

Justin Cave
Justin Cave

Reputation: 231851

You can with the DBMS_JOB package. When you submit a job using DBMS_JOB, the job won't run until the triggering transaction commits. Your job actually sends the email asynchronously just a couple seconds after the commit occurs.

CREATE PROCEDURE do_something
AS
  l_jobno NUMBER;
BEGIN
  <<do something>>
  dbms_job.submit( l_jobno,
                   'BEGIN send_email; END;',
                   sysdate + interval '5' second );
  commit;
END;

CREATE PROCEDURE send_email 
AS
BEGIN
  <<send email>>
END;

Upvotes: 2

Related Questions