Analytics Software

Ouch! Do you realize that, provided you do write that trigger, an insert into that table will last very long? Better create a table mails2send that will have a foreign key to P_IT_ISSUES and have that trigger insert into mails2send  the value of the PK for that row in P_IT_ISSUES. And have a job scan mails2send  and send the emails. Otherwise, your transaction processing will be very slow, most likely inacceptable.

For sending e-mails from PL/SQL use utl_smtp or utl_mail.

I know, but need to do this temporarily. Will use utl_smtp later on but for now this needs to be done.

Any idea how the trigger can be modified to loop?

The trigger mentioned is when an issue is raised, it would go to person with approver='Approver 1', he is first approver. When he logs into application and sets p_it_issues.is_approved='Y', then email would shoot to p_it_people.Approver='Approver 2' . He would again log in, set p_it_issues.is_approved='Y' and email would shoot to p_it_people.Approver='Approver 3' . There could be more than one person with p_it_people.Approver='Approver 1' or 2 or 3. So all would need to approve it and set Y .

A trigger like this sends e-mails BEFORE the transaction is committed - which means that e-mails can be send via UTL_SMTP, and the transaction rolled back. Thus users can receive e-mails for transactions never committed.

Simplistic solution:

Create a stored proc that can be called to send e-mail for a row PK parameter, e.g. SendMail( mailID ).

In the trigger call DBMS_JOB.submit() to execute the SendMail() proc for the relevant row.

Only when the transaction is committed, will the jobs scheduled queued for execution.

Note that when using APEX_MAIL from a trigger, workspace security ID needs to be set - and you need to ensure the e-mails are queued without being committed for sending.

My Understanding

• APEX_MAIL uses UTL_MAIL under the hood.
  • APEX_MAIL just makes it easier to send multiple attachments
  • AFAIK - APEX_MAIL now supports email Templates.
• APEX_MAIL queues the email
  • ROLLBACK will undo the queuing
• You need a running DBMS_SCHEDULER job to process the queue
  • DBA_SCHEDULER_JOBS where job_name='ORACLE_APEX_MAIL_QUEUE' and owner='APEX_nnnnnn'
• Using APEX_MAIL.push_queue in a Trigger (or 2nd Process) is really bad.
  • Oracle can "rollback and try again"
    • You can get multiple identical emails sent
  • APEX can "rollback and try again"
    • You can get multiple identical emails sent
  • Rapidly sending multiple identical emails can get you Black Listed faster than you can hit the SEND button.

From my understanding, APEX_MAIL already does 90% of what Billy and BEDE are recommending.

The other 10% is about "how you interact with it".

My recommendation to APEX developers

• put your "send email" code in a dedicated package
• Call the "Send Email" code as part of a 2nd Process, not within a trigger.
• If you need a non-APEX apps to use the same "Send Email" code, force that application to use an API (Package)
  • REVOKE insert, update, delete ON table_in_question FROM application_users_and_roles
  • Use a Table API (TAPI) or Transaction API (XAPI) [a package]
  • The TAPI/XAPI should be responsible for setting up the APEX Session if necessary. (see APEX_MAIL documentation for HOWTO setup APEX Session)

In short

• Keep your DML code separate from your "Send Email" code.
• Only send emails on committed data. (use a queue so that your can "send email later after commit")

My $0.02

MK

PS - this is mostly for OP, not Billy/BEDE

Mike, it uses UTL_SMTP - UTL_MAIL is far too primitive.