Solved

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

  • 13 November 2020
  • 2 replies
  • 102 views

Userlevel 1
Badge +8

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?

 

icon

Best answer by durette 13 November 2020, 19:38

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

2 replies

Userlevel 6
Badge +12

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;
/

 

Userlevel 1
Badge +8

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.

Reply