Skip to main content
Question

Procedure Command_Sys.Mail is overloaded

  • December 16, 2020
  • 9 replies
  • 832 views
Mark.Feldpausch
Sidekick (Customer)
Forum|alt.badge.img+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
Superhero (Partner)
Forum|alt.badge.img+22
  • dsj
  • Superhero (Partner)
  • 828 replies
  • December 16, 2020

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
Esther San Segundo
durette
pwlm
5 people like this
  • Esther San Segundo
    Esther San Segundo
  • durette
    durette
  • pwlm
    pwlm
  • tobese
    tobese
  • lopespetro
    lopespetro
durette
Superhero (Customer)
Forum|alt.badge.img+19
  • durette
  • Superhero (Customer)
  • 525 replies
  • December 16, 2020

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. If you ask for help in a private message, please know that I will likely repost it as a forum question (with your personal details redacted) and answer it here. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
Mark.Feldpausch
Envdebjyb
2 people like this
  • Mark.Feldpausch
    Mark.Feldpausch
  • Envdebjyb
    Envdebjyb
Mathias Dahl
Superhero (Employee)
Forum|alt.badge.img+32
  • Mathias Dahl
  • Superhero (Employee)
  • 2814 replies
  • December 17, 2020

Does it have to be a deferred call?

/Mathias
william.klotz
Superhero (Customer)
Forum|alt.badge.img+21
  • william.klotz
  • Superhero (Customer)
  • 479 replies
  • December 17, 2020

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 Attachments
ZTC ZTC JGOTA
1 person likes this
  • ZTC ZTC JGOTA
    ZTC ZTC JGOTA
tobese
Hero (Employee)
Forum|alt.badge.img+7
  • tobese
  • Hero (Employee)
  • 98 replies
  • January 4, 2021

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
durette
1 person likes this
  • durette
    durette
Mathias Dahl
Superhero (Employee)
Forum|alt.badge.img+32
  • Mathias Dahl
  • Superhero (Employee)
  • 2814 replies
  • January 7, 2021
tobese wrote:

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
Superhero (Customer)
Forum|alt.badge.img+19
  • durette
  • Superhero (Customer)
  • 525 replies
  • January 7, 2021
Mathias Dahl wrote:
tobese wrote:

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. If you ask for help in a private message, please know that I will likely repost it as a forum question (with your personal details redacted) and answer it here. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
Mathias Dahl
1 person likes this
  • Mathias Dahl
    Mathias Dahl
tobese
Hero (Employee)
Forum|alt.badge.img+7
  • tobese
  • Hero (Employee)
  • 98 replies
  • January 12, 2021

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
Hero (Customer)
Forum|alt.badge.img+14
  • ZTC ZTC JGOTA
  • Hero (Customer)
  • 219 replies
  • April 29, 2021
william.klotz wrote:

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

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