Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Posts Tagged "Oracle Adapter"

Sending an empty VARRAY to an Oracle procedure from BizTalk

Dan Field

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…