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…
Databases are very frequently at the heart of an enterprise integration. EAI tasks frequently involve polling databases, calling stored procedures in databases, as well as ETL and basic CRUD work on databases. The database backing a particular application is often a natural source to integrate with – and if no on premise database exists for a particular source, creating one for local OLTP purposes can help increase insight and decrease chattiness between on premises and off premises applications.
It’s no wonder then that both BizTalk and Mulesoft ESB offer database connectivity out of the box. In this post, I’ll compare Mule and BizTalk’s SQL capabilities, primarily focusing on Oracle and SQL Server. Why these two? They’re the most popular enterprise grade database engines. MySQL is also very popular, but lacking in some enterprise features that this blog will be examining. PostgresSQL…
Yesterday, I attended a seminar covering the Oracle BI landscape. My aim was to come out of this session with a clearer idea of how Oracle products correspond to the Microsoft BI stack. My impression going into this seminar was that Oracle had many, many applications bundled under the BI umbrella. Nevertheless, I was surprised by the sheer number of options available. Practically every piece of the MS BI Stack has at least two parallel products on the Oracle side, in some cases many more. My second impression was that Oracle has done some very credible work to integrate the vast number of applications they’ve developed alongside their Siebel and Hyperion purchases.
Here’s how Oracle visualizes their BI offering: