Sending an empty VARRAY to an Oracle procedure from BizTalk
Oracle packages can leverage VARRAY types to enable a caller to send a collection of values (an array) to a stored procedure in a single call. This VARRAY is very much like a single column Table Type in SQL Server. Generating the schema for this procedure required creating a class/assembly for the ODP.NET driver to use, which in turn is used by BizTalk. That process is fairly straightforward and documented well here. The one point missing from that blog is that the VARRAY object has to have an INDEXED BY INT in order for BizTalk to be able to use it; this is hinted at by the MSDN article on Limitations in the Oracle Adapter:
The Oracle Database adapter does not support PL/SQL tables that are not indexed by a numeric field.
It seems this limitation applies to VARRAY types (which makes sense considering they are backed by tables).
All was well and good when we sent a message that had any values to put into that array, but we ran into problems when trying to send messages that wanted to omit the array.
The error that came back was not especially helpful, but indicated a PL/SQL compilation error. The root problem seemed to be that the adapter was trying to st the VARRAY variable as atomically null (e.g. I_VARRAY_PARAM := null), which caused errors before even trying to execute the function. This seemed to be related to another limitation specified in the MSDN article:
The Oracle Database adapter does not enable clients to set the value of the first element in a VARRAY to NULL.
However, we were able to discover that as long as we sent at least 1 empty “string” node, the call worked fine. A simple XSLT fragment allowed us to achieve this in our map (the string node was actually generated with the “array” namespace, not depicted in the picture):
Here’s the XSLT in that scripting functoid:
<xsl:choose> <xsl:when test="boolean(SOURCE_REPEATING_NODE)"> <xsl:for-each select="SOURCE_REPEATING_NODE"> <array:string xmlns:array="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> <xsl:value-of select="SOURCE_DATA_NODE" /> </array:string> </xsl:for-each> </xsl:when> <xsl:otherwise> <!-- create an empty node --> <array:string xmlns:array="http://schemas.microsoft.com/2003/10/Serialization/Arrays" /> </xsl:otherwise> </xsl:choose>
Doing this resolved all errors from the BizTalk side and allowed the adapter to correctly assign the first value of the VARRAY as empty (instead of trying to atomically null out the whole array in the call, which did not work).