Skip to main content

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;

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

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?

Does it have to be a deferred call?

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

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

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

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.

 

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.

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

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!!

Terms & ConditionsCookie settings