Question

Procedure Command_Sys.Mail is overloaded

  • 16 December 2020
  • 9 replies
  • 735 views
Mark.Feldpausch
Rank
Userlevel 4
Badge +8

I have an event action to execute online SQL and I’m getting this error on the background job.  Any ideas on how to troubleshoot?

I will say that all other standard IFS email event actions work fine, only when I attempt to do an email through the method below do I have an issue.

 

Procedure Command_Sys.Mail is overloaded and overloaded procedure is not supported.                    ORA-20105: Transaction.OVERLOAD

 

Here’s my SQL statement (taken from another post by @durette - thanks for the idea)

 

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_SENDER1',           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;

This topic has been closed for comments

9 replies

dsj
Rank
Userlevel 7
Badge +20

Hi Mark,

 

Overloading means that there’s more than one instances of procedure signatures are available.

Command_Sys.mail has 3 different method signatures and unfortunately overloaded procedures are not supported to call with transaction_sys.deferred_call :(

Command_Sys.mail signatures (Apps10)

 

Hope it helps!

Damith

DSJ - https://dsj23.me
durette
Rank
Userlevel 7
Badge +18

This seems like an unnecessary regression from v9 to v10.

 

Overloads can cause confusion with positional arguments, but it’s better to always use named arguments with rarely called procedures. Why can’t the caller use named arguments in the dynamic SQL?

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
Mathias Dahl
Rank
Userlevel 7
Badge +30

Does it have to be a deferred call?

/Mathias
william.klotz
Rank
Userlevel 7
Badge +21

Hi @Mark.Feldpausch ,

 

What we do is we’ve created a stored procedure which does some work and then calls the Command_Sys.Mail to send the email.   We pass the parameters to the stored procedure by using  CLIENT_SYS.Add_To_Attr and calling Transaction_SYS.Deferred_Call to call the stored procedure from a custom event.

 

Attached is the stored procedure we are using and below is the custom event implemented when the shipment is closed.

DECLARE
    a_ VARCHAR2(32000) := NULL;
       
BEGIN
    CLIENT_SYS.Clear_Attr(a_);
    CLIENT_SYS.Add_To_Attr('SHIPMENT_NO_', '&NEW:SHIPMENT_ID', a_);
    CLIENT_SYS.Add_To_Attr('SITE_','&NEW:CONTRACT',a_);
    Transaction_SYS.Deferred_Call('OUR_SHIPMENT_NOTIFICATION','PARAMETER',a_,'Notify customer service representative shipment id=&NEW:SHIPMENT_ID');

END; 

 

Regards,

William Klotz

1 Attachment
tobese
Rank
Userlevel 3
Badge +7

If you use naming of the parameters in the call I think you can get the one you want.

Like ProcX(a => ‘a’, ...

Cheers /Tommy
Mathias Dahl
Rank
Userlevel 7
Badge +30

If you use naming of the parameters in the call I think you can get the one you want.

Like ProcX(a => ‘a’, ...

I don't think it will help in this context. We send in a method name as a string and the arguments as an attribute string and the framework needs to figure out how to call that. I think there is where we get into problems with overloaded methods like the one here. It might not be impossible, but it should be harder to figure out what method to call.

 

/Mathias
durette
Rank
Userlevel 7
Badge +18

If you use naming of the parameters in the call I think you can get the one you want.

Like ProcX(a => ‘a’, ...

I don't think it will help in this context. We send in a method name as a string and the arguments as an attribute string and the framework needs to figure out how to call that. I think there is where we get into problems with overloaded methods like the one here. It might not be impossible, but it should be harder to figure out what method to call.

 

I understood this as a suggestion on what the internal code could be doing in the dynamic SQL.

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
tobese
Rank
Userlevel 3
Badge +7

Ok, I thought that the substitute variables where simply inserted as text and that you could build up the string with the parameters.

Cheers /Tommy
ZTC ZTC JGOTA
Rank
Userlevel 6
Badge +14

Hi @Mark.Feldpausch ,

 

What we do is we’ve created a stored procedure which does some work and then calls the Command_Sys.Mail to send the email.   We pass the parameters to the stored procedure by using  CLIENT_SYS.Add_To_Attr and calling Transaction_SYS.Deferred_Call to call the stored procedure from a custom event.

 

Attached is the stored procedure we are using and below is the custom event implemented when the shipment is closed.

DECLARE
    a_ VARCHAR2(32000) := NULL;
       
BEGIN
    CLIENT_SYS.Clear_Attr(a_);
    CLIENT_SYS.Add_To_Attr('SHIPMENT_NO_', '&NEW:SHIPMENT_ID', a_);
    CLIENT_SYS.Add_To_Attr('SITE_','&NEW:CONTRACT',a_);
    Transaction_SYS.Deferred_Call('OUR_SHIPMENT_NOTIFICATION','PARAMETER',a_,'Notify customer service representative shipment id=&NEW:SHIPMENT_ID');

END; 

 

Regards,

William Klotz

Man, You made my day!!

JL
Terms & ConditionsCookie settings