Streaming in OracleDB adapter

OracleDB adapter supports streaming of data for both inbound (wrt adapter) and outbound messages where it is possible to do so. Streaming can be at record level (in case of Insert operation), xml node level or xml node value level.

For inbound messages, the adapter consumes it using Message.WriteBodyContents(XmlDictionaryWriter). In general for any operation the adapter keeps on reading the message until it has accumulated the minimum amount of data that can be passed to Oracle database using ODP.NET. This is true for all operations except UpdateLOB (will come to this in a short while). Also in most cases what this means is that the message is fully read before sending any data. Insert operation (inserting array of rows into a table) is slightly different. In this case one row/record is read and inserted into Oracle (in future version the number of records to be read before doing an insert will be customizable through a binding property).

One problem with the above approach is that it is very difficult to stream data into Oracle large object types like BLOB, (N)CLOB. So the UpdateLOB operation was introduced on tables/views having these columns. Input to UpdateLOB operation is the large object column name, unique filter giving the row that is to be updated and the actual data. The data is read in small chunks and passed to Oracle. This actually has xml node value level streaming. This method works fine in WCF channel scenario (and BizTalk which is an extension to that) but not in the WCF proxy scenario. In proxy the actual data becomes a byte array and defeats streaming. This is a limitation of WCF proxy model which doesn't allow a combination of streamed (actual data) and non-streamed (column name and filter) parameters in the same direction.

For outbound messages, the adapter always tries to stream if possible. For e.g. in case of Select operation response, one row/record is read from Oracle and output. If the row has large object types then the values for those are streamed as well (xml node value streaming). The same approach is also taken for stored procedures/functions that has output parameters of large object and REF CURSOR types. But this works only in WCF channel scenario when the message is consumed using Message.WriteBodyContents(XmlDictionaryWriter) (in BizTalk this is the case as well). In proxy scenario there is no streaming as the message is fully de-serialized into objects. In order to support streaming for large object types in proxy, ReadLOB operation was added on tables/views having large object type columns. The method signature looks like Stream ReadLOB(string column_name, string filter) . This is only meant to work in proxy. In channel the same thing can be achieved using a Select operation.