none
Oracle Procedures and xsi:nil="true" RRS feed

  • Question

  • When we call an Oracle procedure using BizTalk Adapter Pack v2, it fails because not all the elements in the XML document are present. According to the documentation we must add all missing elements with the attribute xsi:nil="true". This is a very fundamental change compared to version v1.

    Typically there are 50 parameters in a call and some 10 or so might be missing. Potentially 48 could be missing. What is the easiest way to ensure that all the elements are present in the XML. Map (what functoid to use?), custom xslt, some sort of change in the generated schemas?

    Thanks in advance.

    BTW, This is from the documentation

    Outbound operations: In the previous version of the Oracle Database adapter, if you executed stored procedures in which some parameters are not specified in the input XML file, a NULL value was sent for those parameters. Even if default values were specified for these parameters in the stored procedure, still a NULL value was sent for those parameters. In the current version of the adapter, no value is sent for the parameters that do not have a value specified in the input XML file. If a default value is specified in the stored procedure, the Oracle database uses that value because no value was sent by the adapter. If a NULL value needs to be sent, the user needs to specify a NULL node in the input XML file by setting the value of “nil” attribute to “true.”


    Antti
    Tuesday, August 25, 2009 2:06 PM

Answers

  • Lets define your problem: You have a stored procedure, that takes 50 parameters. However, you do not necessarily pass in all 50 parameters.

    Now, what is the correct place to solve this problem? Obviously, your parameters need to be optional OR they should have a default value (which could be NULL).

    In my opinion, you should make your stored procedure parameters have the default value of NULL, or make them optional - that is the correct fix from the design perspective too.

    Writing complex maps or custom XSLT is not the best way to solve this. I understand that the behavior in the adapter changed, but I'd call that a bug fix.

    Thanks,
    Manas

    Wednesday, August 26, 2009 4:24 PM

All replies

  • So do you see any issue if you do not specify the parameters that you dont want in your input? If you skip an element, it is as good as being set to nil="true".

    An easy way to skip a lot of nodes is to create a logical NOT functoid, hardcode the value TRUE as the input inside it, and connect its output to all the nodes you want not to be present in the resultant XML.

    Let me know if this solves your issue.

    Thanks!
    Manas
    Wednesday, August 26, 2009 9:33 AM
  • The problem we are having is that the Oracle procedure has to few input parameters because the new version of the adapter does not supply NULL values for missing elements.

    The error is this

    Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 2, column 8:
    PLS-00306: wrong number or types of arguments in call to 'CREATE_USER'
    ORA-06550: line 2, column 1:
    PL/SQL: Statement ignored --->

    So for each of the 50 arguments I have to create XSLT like this

        <b:P_USER_ID>
          <xsl:choose>
            <xsl:when test="a:UserID">
              <xsl:value-of select="a:UserID"/>
            </xsl:when>
            <xsl:otherwise>
              <xsl:attribute name="xsi:nil">true</xsl:attribute>
            </xsl:otherwise>
          </xsl:choose>
        </b:P_USER_ID>


    Antti
    Wednesday, August 26, 2009 9:42 AM
  • Hi Antti,

    You can use Nil functoid in map to set the attribute. Use it to specify unlinked elements' xsi:nil to true.

    Nil Value Functoid
    http://msdn.microsoft.com/en-us/library/aa578520(BTS.10).aspx

    IsNil Functoid
    http://msdn.microsoft.com/en-us/library/aa561802(BTS.10).aspx

    Thanks.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, August 26, 2009 9:55 AM
    Moderator
  • I have no clue how this could be done in the mapper.

    The requirement is to copy the content from the source node and if it does not exist set the attribute xsi:nil to true. And this has to be done 50 times.
    Antti
    Wednesday, August 26, 2009 10:33 AM
  • What Wen-Jun has suggested will work for you. Please try it out and let us know. Due to the behavior of Oracle Adapter in BAP V1 where it used to pass NULL for parameters that were not specified, parameters that had default values could not be made use of, and that is why the behavior was corrected in BAP V2.0.

    You can use the one single nil node and connect it to all the nodes that need to be set to nil (you might need to use a conditional functoid).

    Thanks,
    Manas

    Wednesday, August 26, 2009 12:17 PM
  • We do not know which elements might be missing, so potentially 48 tests have to be created. I would like to try it but do not know how to put all these functoids together.

    I have already written an XSLT file to solve this problem. Over 400 lines!
    What a negative impact on our productivity. I would really appreciate if the earlier behavior could be switched on per port if desired.


    Antti
    Wednesday, August 26, 2009 12:28 PM
  • Lets define your problem: You have a stored procedure, that takes 50 parameters. However, you do not necessarily pass in all 50 parameters.

    Now, what is the correct place to solve this problem? Obviously, your parameters need to be optional OR they should have a default value (which could be NULL).

    In my opinion, you should make your stored procedure parameters have the default value of NULL, or make them optional - that is the correct fix from the design perspective too.

    Writing complex maps or custom XSLT is not the best way to solve this. I understand that the behavior in the adapter changed, but I'd call that a bug fix.

    Thanks,
    Manas

    Wednesday, August 26, 2009 4:24 PM
  • You are right but there is a twist. We are not in control of the Oracle procedure. One other problem arised with that procedure. Because it contains COMMIT, the default configuration of the adapter causes the send to fail. That was easy to fix because there is parameter in the adapater configuration to turn off ambient transactions.


    Antti
    Thursday, August 27, 2009 6:27 AM
  • I completely understand the problem you are facing. I really appreciate your feedback, and I will ensure that it is conveyed appropriately.

    Thanks!
    Manas
    Thursday, August 27, 2009 8:04 AM
  • We were able to change the procedure and it uses defaults like this:

      FUNCTION  CREATE_USER(  p_pref_first_name     IN NS.PREF_FIRST_NAME%TYPE DEFAULT NULL, .....

    Unfortunately this does not fix the problem. If all parameters are not present, the call returns this 

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


    So I had to create an XSLT sheet that contains 50 of these


    <

     

    P_PREF_FIRST_NAME>
    <
    xsl:choose>
    <
    xsl:when test="ns0:P_PREF_FIRST_NAME">
    <
    xsl:value-of select="ns0:P_PREF_FIRST_NAME"/>
    </
    xsl:when>
    <
    xsl:otherwise>
    <
    xsl:attribute name="xsi:nil">true</xsl:attribute>
    </
    xsl:otherwise>
    </
    xsl:choose>
    </
    P_PREF_FIRST_NAME>





    Antti
    Wednesday, October 28, 2009 10:14 AM
  • Hi,

    Sorry I could not look into this earlier. This is not expected - you should be safely able to skip parameters for which defaults are set - can you check if you're missing one of the mandatory parameters? Also, can you endure that you are using BizTalk Adapter Pack 2.0 and not 1.0?

    Thanks,
    Manas
    Monday, November 9, 2009 12:52 PM
  • Hi,

    We tested this by supplying all parameters -> worked.
    Drop one of the optional prameters with default set to null -> does not work.

    We have upgraded to Adapter Pack 2 anyway because we also needed the SQL Server support.


    Antti
    Monday, November 9, 2009 12:57 PM
  • Is it possible to share the SP definition and the input XML?
    Tuesday, November 10, 2009 6:33 AM
  • Not in public but you could send me an e-mail

    antti dot somersalo at fortum dot com

    Antti
    Tuesday, November 10, 2009 7:35 AM
  • It turns out that sometimes you can left a parameter out but in most cases the call to Oracle fails.

    Maybe there is some pattern but I cannot find it.


    Antti
    Tuesday, December 22, 2009 3:24 PM
  • We are experiencing this same issue using the Oracle WCF Adapter from Adapter Pack 2. I was just curious of the official "fix" for this was still xslt for any parameters that are optional. The post marked as the answer on this, IMO doesn't really answer the question. It doesn't matter if you give the optional parameters a default value or not.

     


    Chris Wigley MCAD\MCPD
    Wednesday, June 16, 2010 9:44 PM