none
The adapter failed to transmit message going to send port in Biztalk 2013 R2 RRS feed

  • Question

  • Hi,

    I am creating restful api using  Biztalk 2013 R2. here the input and output are in json format.

    Am getting the below error when run the service url in fiddler .



    ----------------------------------------

    Thanks,

    Rajeshwari 



    Tuesday, July 12, 2016 6:00 AM

Answers

  • Hi Rajeshwari

    Inspect from Admin Console the suspended message. The error is pretty clear-

    PLS-00306: wrong number or types of arguments in call to '__'

    So, the message that is going to the Oracle SendPort is not correctly formed - like missing arguments/incorrect datatypes.

    How many parameters are accepted by the __ package? Make sure that the suspended message has the correct number of parameters.

    Refer these-

    This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure.

    http://www.dba-oracle.com/t_pls_00306_wrong_number_types_arguments.htm

    https://community.oracle.com/thread/363839?tstart=0

    http://stackoverflow.com/questions/19543343/pl-sql-error-pls-00306-wrong-number-or-types-of-arguments-in-call-to-trigger


    Thanks Arindam



    Tuesday, July 12, 2016 6:04 AM
    Moderator
  • Hi Rajeshwari

    Check the suspended message body from Admin Console - this will be the Oracle schema type message. The error is being thrown by the Oracle adapter as stated earlier.  Make sure that the suspended message has the correct number of parameters accepted by the __ package. Are some fields blank/missing in the suspended message?

    "do i need to change the type of the input parameters in both the schema. "

    Note that the Wizard would have created the correct Oracle operation schema for you. You just have to make sure that you are populating the fields in the Oracle schema correctly in your map. You should certainly not change anything in the Oracle schema that you got by running the Wizard.

    If you are creating the Oracle operation message correctly it does not matter what the JSON schema parameter types are.


    Thanks Arindam








    Wednesday, July 13, 2016 6:07 AM
    Moderator
  • Hi Rajeshwari

    To pass NULL field/element to Oracle, you have to set the xsi:nil="true"  attribute on the element.

    This is documented here-

    As a workaround, if you want to pass NULL value for a field or parameter, you must ensure that no value for the field or parameter is specified. For example, to pass NULL value for a field called “name”:
    <name xsi:nil="true"/>
    

    To set this attribute on elements that are not being passed in your JSON XML schema, you can use this approach-

    https://social.technet.microsoft.com/Forums/Lync/en-US/c5b01e29-4b68-4295-a530-dde126670f77/set-xsinil-to-true-in-the-biztalk-map?forum=biztalkgeneral




    Thanks Arindam

    • Marked as answer by Rajeshwari ML Tuesday, July 19, 2016 9:29 AM
    Wednesday, July 13, 2016 8:23 AM
    Moderator

All replies

  • Hi Rajeshwari

    Inspect from Admin Console the suspended message. The error is pretty clear-

    PLS-00306: wrong number or types of arguments in call to '__'

    So, the message that is going to the Oracle SendPort is not correctly formed - like missing arguments/incorrect datatypes.

    How many parameters are accepted by the __ package? Make sure that the suspended message has the correct number of parameters.

    Refer these-

    This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure.

    http://www.dba-oracle.com/t_pls_00306_wrong_number_types_arguments.htm

    https://community.oracle.com/thread/363839?tstart=0

    http://stackoverflow.com/questions/19543343/pl-sql-error-pls-00306-wrong-number-or-types-of-arguments-in-call-to-trigger


    Thanks Arindam



    Tuesday, July 12, 2016 6:04 AM
    Moderator
  • Hi,

    From the error it is clear that it is due to miss match of parameters, but it could not always be. I would suggest,

    1) Try to run the procedure outside the BizTalk and check it is running successfully without error

    2) If it is runs successfully compare the parameters with the one you are passing from BizTalk

    3) There could also be the possibility of Type incompatibility between the Package and anonymous PL/SQL block, refer http://stackoverflow.com/questions/19543343/pl-sql-error-pls-00306-wrong-number-or-types-of-arguments-in-call-to-trigger

    Hope this Helps!!!

    Please Mark as Answered if you satisfy with Reply.

    • Proposed as answer by vikas.a.mehta Tuesday, July 12, 2016 10:15 AM
    Tuesday, July 12, 2016 10:14 AM
  • Hi Rajeshwari

    Check the suspended message body from Admin Console - this will be the Oracle schema type message. The error is being thrown by the Oracle adapter as stated earlier.  Make sure that the suspended message has the correct number of parameters accepted by the __ package. Are some fields blank/missing in the suspended message?

    "do i need to change the type of the input parameters in both the schema. "

    Note that the Wizard would have created the correct Oracle operation schema for you. You just have to make sure that you are populating the fields in the Oracle schema correctly in your map. You should certainly not change anything in the Oracle schema that you got by running the Wizard.

    If you are creating the Oracle operation message correctly it does not matter what the JSON schema parameter types are.


    Thanks Arindam








    Wednesday, July 13, 2016 6:07 AM
    Moderator
  • Hi Rajeshwari

    Click on the part tab(in left pane) in the Message Details window that you have opened - it will show the message that is going to the adapter and is failing.


    Thanks Arindam


    Wednesday, July 13, 2016 6:53 AM
    Moderator
  • Hi,

    Are these 3 missing parameters present in the JSON input?

    Start with testing the map why the 3 parameters are missing.

    Take the JSON input XML that you are passing to the map (use this XML as 'Test Map Input Instance' and do a Test Map in Visual Studio. If you debug the map, you should find out the reason.


    Thanks Arindam


    Wednesday, July 13, 2016 7:17 AM
    Moderator
  • Hi Rajeshwari

    To pass NULL field/element to Oracle, you have to set the xsi:nil="true"  attribute on the element.

    This is documented here-

    As a workaround, if you want to pass NULL value for a field or parameter, you must ensure that no value for the field or parameter is specified. For example, to pass NULL value for a field called “name”:
    <name xsi:nil="true"/>
    

    To set this attribute on elements that are not being passed in your JSON XML schema, you can use this approach-

    https://social.technet.microsoft.com/Forums/Lync/en-US/c5b01e29-4b68-4295-a530-dde126670f77/set-xsinil-to-true-in-the-biztalk-map?forum=biztalkgeneral




    Thanks Arindam

    • Marked as answer by Rajeshwari ML Tuesday, July 19, 2016 9:29 AM
    Wednesday, July 13, 2016 8:23 AM
    Moderator
  • Hi Rajeshwari

    If you do a Test Map in Visual Studio by passing the JSON XML as input, are you getting the suspended Oracle message(with the missing parameters) as map output?


    Thanks Arindam

    Wednesday, July 13, 2016 8:25 AM
    Moderator
  • Hi Rajeshwari

    It is only correct if you always want to pass NULL for those Oracle fields/columns. If in the JSON XML input message those fields will sometimes have value, and will be empty at some times, you have to use conditional mapping as shown in the link shared earlier.

    Note- If the source elements in JSON XML message are empty/missing instead of containing isNil attribute, replace the IsNill functoid in the checks below with LogicalString functoid (see the example towards the end of this link .

    Refer below blog-

    https://sandroaspbiztalkblog.wordpress.com/2014/06/12/biztalk-mapper-working-with-nillable-values-xsiniltrue/


    Thanks Arindam





    Wednesday, July 13, 2016 8:55 AM
    Moderator
  • Hi Rajeshwari

    I notice that you are using the IsNil functoid. Note that this functoid checks if the source element in input message has nil attribute that is set to true, eg, it will return true only when the element linked to it is like:

    <NodeName xsi:nil="true" />

    Note that if your input element does not have the above attribute, but rather the element is missing or empty in the source message like below, it will return false and your logic will fail-

    <NodeName />, or <NodeName></NodeName>

    then, you should replace the IsNil functoid in your map with the LogicalString functoid, rest of your map will remain the same.

    Please refer what I shared earlier-

    Note- If the source elements in JSON XML message are empty/missing instead of containing isNil attribute, replace the IsNill functoid in the checks below with LogicalString functoid (see the example towards the end of this link .


    Thanks Arindam



    Wednesday, July 13, 2016 6:23 PM
    Moderator
  • Also, how does your input XML look like, are some of these elements in the source missing or empty? If yes, you need to use the LogicalString functoid for your conditional check as mentioned.

    Thanks Arindam

    Wednesday, July 13, 2016 6:25 PM
    Moderator
  • Hi Rajeshwari

    There is no need to do this-

    I have set the nillable=true attribute to the  source json xml inputs.

    Reason being - this XML will come from the JSON input, and in JSON there is no way to specify xsi:Nil. So, if there are missing fields/elements in JSON parameters, they will be missing/empty in the JSON XML.

    So, let the source XML remain the same. Only thing that will change is the map.So,

    i just need to replace the isnull function id with LogicalString functoid? the rest of the mapping remains the same?

    Yes, just that will work. Basically, instead of checking for presence of xsi:Nil attribute in input XML, you have to check for empty/missing elements in input XML. LogicalString functoid will do that for you.


    Thanks Arindam


    Thursday, July 14, 2016 7:57 AM
    Moderator
  • Hi Rajeshwari

    Actually none.

    Use something like this. I have shown the logic for one element. This means that whenever input element is blank or missing, the output element will have nil=true attribute set. If source element has value, it will be mapped in destination.


    Thanks Arindam


    Thursday, July 14, 2016 10:11 AM
    Moderator
  • Hi Rajeshwari

    Are you still getting same error from adapter?

    After the new map,  is there a difference in how the suspended message looks like? Is it still missing few parameters?


    Thanks Arindam


    Friday, July 15, 2016 5:47 AM
    Moderator
  • Can you share how the input message looks like (JSON XML format); and how the suspended message looks like? If you do a test map with the JSON XML format in Visual Studio, are you getting the output with all the Oracle schema parameters?

    Have you made sure that the new map is deployed/Host Instances restarted?


    Thanks Arindam



    Friday, July 15, 2016 6:14 AM
    Moderator
  • Hi Rajeshwari

    Can you share your JSON input?

    Does it only have these 3 fields - Content Removed


    Thanks Arindam


    Friday, July 15, 2016 9:12 AM
    Moderator
  • Hi Rajeshwari

    Can you share the XML file that you are using to do Test Map in Visual Studio? 

    Also, instead of the NULL fields in JSON, if all fields have value, does it work fine?


    Thanks Arindam


    Friday, July 15, 2016 9:33 AM
    Moderator
  • So, for the above input XML you are getting map output as below?

    Content Removed

    That sounds improbable. 

    Can you just create a new simple map(simply link source and destination fields). Now for the above JSON XML input, are you seeing all fields in the Oracle message when you do Test Map?

    If yes, your old map above is not correct(maybe the order of links to the Value Mapping functoid is not correct). Make sure that the first parameter to Value Mapping functoid is a boolean - you can switch/change the order once you launch the functoid.


    Thanks Arindam



    Friday, July 15, 2016 9:49 AM
    Moderator
  • Hi Rajeshwari

    Then it is an issue with the map as mentioned earlier. Always try to create a map incrementally - meaning do the mapping for one/two elements and immediately do a Test Map and check the output.

    Check this-

    Make sure that the first parameter to all the Value Mapping functoids is a boolean - you can switch/change the order once you launch the functoid.


    Thanks Arindam


    Friday, July 15, 2016 10:05 AM
    Moderator
  • When you create links to the Value Mapping functoid,  the first link should evaluate to a true/false value. The order of parameters to the functoid matters.

    If you double click and launch the functoid in the mapping grid,  it will show you if the first parameter is true/false,  ie a boolean type.

    Basically confirm that all the Value Mapping functoid parameters are in this order (notice that Logical String comes first)- 


    Thanks Arindam





    Friday, July 15, 2016 11:16 AM
    Moderator
  • Hi Rajeshwari

    If the map is correctly implemented, for NULL/empty nodes in input it will create the output elements with xsi:Nil = true attribute , which is what Oracle adapter requires for NULL/missing values.

    For the JSON -

    Content Removed

    What does the equivalent JSON XML you are getting?

    It looks like instead of empty/missing elements in JSON XML, there are blank values being created?

    Content Removed


    Thanks Arindam






    Tuesday, July 19, 2016 7:22 AM
    Moderator
  • Also can you test the map that you created by changing the Test Map Input Instance file by deleting one of the elements. How does the output look like? Does it contain Nil attribute in map output?

    Thanks Arindam

    Tuesday, July 19, 2016 7:30 AM
    Moderator
  • Hi Rajeshwari

    That is the JSON XML schema.I meant what is the JSON XML created, basically what is the XML input message going to the map at runtime?

    Can you first test this part-

    Also can you test the map that you created by changing the Test Map Input Instance file by deleting one of the elements, say P_TO_DATE. How does the output look like - are all Oracle XML elements created? Does it contain Nil attribute in map output?


    Thanks Arindam



    Tuesday, July 19, 2016 7:44 AM
    Moderator
  • Hi Rajeshwari

    Save the below xml in some file like test.xml-

    Content Removed

    Now in Map properties, provide above file path in Test Map Input Instance property.


    Thanks Arindam


    Tuesday, July 19, 2016 8:05 AM
    Moderator
  • Yes, if there are missing fields in JSON, just don't pass them in your JSON input.

    The map is working fine as per your output. So, is the flow working fine now?


    Thanks Arindam


    Tuesday, July 19, 2016 8:31 AM
    Moderator
  • You're welcome :)

    Thanks Arindam

    Tuesday, July 19, 2016 9:40 AM
    Moderator