Skip to main content
Solved

How can i avoid a mutating problem with a custom event that sends emails when an application message finishes?

  • November 13, 2020
  • 3 replies
  • 1592 views

Forum|alt.badge.img+11

Am seeing the following error in the DB alert log when the event fires.

 

-4091: ORA-04091: table IFSAPP.FNDCN_APPLICATION_MESSAGE_TAB is mutating, trigger/function may not see it
ORA-04091: table IFSAPP.FNDCN_APPLICATION_MESSAGE_TAB is mutating, trigger/function may not see it

I have seen people getting around this with an autonomous pragma - but cant insert as am not executing an SQL block but sending an email.

 

Any ideas?

 

Best answer by durette

Create a PL/SQL block that sends an email via a background job. (My example assumes you have a mail sender MAIL_SENDER_HTML. Yours is probably something like MAIL_SENDER1 if you haven’t changed it.)

DECLARE
   attr_ VARCHAR2(32767);
BEGIN
   client_sys.clear_attr(attr_);
   client_sys.add_to_attr('FROM_USER_NAME_', fnd_session_api.get_fnd_user, attr_);
   client_sys.add_to_attr('TO_USER_NAME_',   fnd_session_api.get_fnd_user, attr_);
   client_sys.add_to_attr('SUBJECT_',        'This is your subject line.', attr_);
   client_sys.add_to_attr('TEXT_',           'This is your message body.', attr_);
   client_sys.add_to_attr('MAIL_SENDER_',    'MAIL_SENDER_HTML',           attr_);
   -- client_sys.add_to_attr('ERROR_TEXT_', NULL, attr_);
   -- client_sys.add_to_attr('ATTACH_',     NULL, attr_);
   -- client_sys.add_to_attr('CC_',         NULL, attr_);
   -- client_sys.add_to_attr('TIMER_',      NULL, attr_);
   -- client_sys.add_to_attr('AUDIT_',      NULL, attr_);
   -- client_sys.add_to_attr('FROM_ALIAS_', NULL, attr_);
   -- client_sys.add_to_attr('LABEL_',      NULL, attr_);
   transaction_sys.deferred_call(
      'command_sys.mail',
      'PARAMETER',
      attr_,
      'This is your background job description.');
END;
/

 

View original
Did this topic help you find an answer to your question?

durette
Superhero (Customer)
Forum|alt.badge.img+19
  • Superhero (Customer)
  • November 13, 2020

Create a PL/SQL block that sends an email via a background job. (My example assumes you have a mail sender MAIL_SENDER_HTML. Yours is probably something like MAIL_SENDER1 if you haven’t changed it.)

DECLARE
   attr_ VARCHAR2(32767);
BEGIN
   client_sys.clear_attr(attr_);
   client_sys.add_to_attr('FROM_USER_NAME_', fnd_session_api.get_fnd_user, attr_);
   client_sys.add_to_attr('TO_USER_NAME_',   fnd_session_api.get_fnd_user, attr_);
   client_sys.add_to_attr('SUBJECT_',        'This is your subject line.', attr_);
   client_sys.add_to_attr('TEXT_',           'This is your message body.', attr_);
   client_sys.add_to_attr('MAIL_SENDER_',    'MAIL_SENDER_HTML',           attr_);
   -- client_sys.add_to_attr('ERROR_TEXT_', NULL, attr_);
   -- client_sys.add_to_attr('ATTACH_',     NULL, attr_);
   -- client_sys.add_to_attr('CC_',         NULL, attr_);
   -- client_sys.add_to_attr('TIMER_',      NULL, attr_);
   -- client_sys.add_to_attr('AUDIT_',      NULL, attr_);
   -- client_sys.add_to_attr('FROM_ALIAS_', NULL, attr_);
   -- client_sys.add_to_attr('LABEL_',      NULL, attr_);
   transaction_sys.deferred_call(
      'command_sys.mail',
      'PARAMETER',
      attr_,
      'This is your background job description.');
END;
/

 


Forum|alt.badge.img+11
  • Sidekick (Customer)
  • November 19, 2020

Thanks this has really helped! Only issue now, is I cant seem to pull any of the event attributes into the output email - I really want to show the subject in the application message email output.


Mark.Feldpausch
Sidekick (Customer)
Forum|alt.badge.img+8

I’m having trouble getting this to work (post linked below).  What version of IFS are you using (I’m on v10)?

https://community.ifs.com/technology-infrastructure-cloud-integration-dev-tools-50/procedure-command-sys-mail-is-overloaded-5463?postid=17971#post17971

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings