Can I define a UDT inside a Oracle Function Package and use it on my WCF-Oracle Adaptor?

One of this days I got this problem were some help was needed to provide an insight regarding the use of the WCF-Oracle Adapter for Biztalk, being very comfortable with the WCF part of the issue I thought that I could provide some help on the discussion.

So basically the issue he had this team that was trying to consume an Oracle Function Package which has a User Defined Type defined in it. (big hint here) :-)

The developers were already implemented this solution by following our recommendation in the below article that explains how to proceed with a correct WCF-Oracle adaptor configuration.
Invoke Functions and Procedures with REF CURSORS in Oracle Database using BizTalk Server

But unfortunately they were not able to generate the schemas for their internal pipeline functions, moreover they told me that the issue was caused by the fact that the adaptor was not enable to retrieve the metadata of the function hence not able to generate the schemas (second hint here).

They even share with me the errors that they were seeing.

Error while retrieving or generating the WSDL. Adapter message: Retrieval of Operation Metadata has failed while building WSDL at 'https://Microsoft.LobServices.OracleDB/2007/03/......' Microsoft.ServiceModel.Channels.Common.MetadataException: Retrieval of Operation Metadata has failed while building WSDL at 'https://Microsoft.LobServices.OracleDB/2007/03/....' ---> Microsoft.ServiceModel.Channels.Common.MetadataException: Invalid Metadata. Check if the database user has permissions to UDT '..._TABLE' at Microsoft.Adapters.OracleDB.OracleCommonMetadataResolverHandler.CreateProcedureParameter(OracleCommonConnectionWrapper connection, DataTable metadataTable, Int32 i, DataRow row, String dataType, ProcedureMetadata operation, OracleCommonExecutionHelper executionHelper, OracleCommonTypeMetadataPreResolver preResolver) at Microsoft.Adapters.OracleDB.OracleCommonMetadataResolverHandler.ResolveProcedureMetadata(OracleCommonConnectionWrapper connection, DataTable metadataTable, ProcedureMetadata operation, OracleCommonExecutionHelper executionHelper, OracleCommonTypeMetadataPreResolver preResolver) at Microsoft.Adapters.OracleDB.OracleCommonMetadataResolverHandler.ResolveOperationMetadata(String operationId, TimeSpan timeout, TypeMetadataCollection& extraTypeMetadataResolved) at Microsoft.ServiceModel.Channels.Common.Design.MetadataCache.GetOperationMetadata(String uniqueId, Guid clientId, TimeSpan timeout) at Microsoft.ServiceModel.Channels.Common.Design.WsdlBuilder.SearchBrowseNodes(MetadataRetrievalNode[] nodes, WsdlBuilderHelper helper, TimeoutHelper timeoutHelper) --- End of inner exception stack trace ---

Looking at all this information I had pretty had an idea about what was happening but of course we first needed to calm down everyone and try to get all the information flowing to the developers, so they could understand what was happening.

So, the next natural step, was asking the developers to share with me the problematic function so I can check on the inner works of the procedure they were trying to implement, and after i got that function we could confirm that they were indeed specifying inside this function package a User Defined Type.

Unfortunately, except for PL/SQL tables (which of course was not the case), the Oracle Database adapter does not support UDTs that are defined inside a package.

This is stated on the below article.

Limitations of BizTalk Adapter for Oracle Database

So, after that we enter a different discussion in terms of explaining that this behavior that their were seeing was indeed by design, which is not always a good discussion.

Hope that helps.