Biztalk unable to read Oracle 18 stored procedure with strongly typed ref cursor

We have Biztalk to read data from a table in Oracle by calling a stored procedure. The stored procedure returns a strongly typed cursor and it was working fine with Oracle 12.

Updating to Oracle 18 is returning a response as if the cursor is weakly typed: https://docs.microsoft.com/en-us/biztalk/adapters-and-accelerators/adapter-oracle-database/message-schemas-for-ref-cursors

This link has the exact problem that we are facing, but the cursor is strongly typed.

I tried returning a ref cursor using record and explicity specifying the column names and types. It did not work. The Biztalk version is 2013 R2.

Has anyone encountered this problem with Oracle 18? Works fine with Oracle 12.

The strongly typed cursor is

TYPE t_ReqCursor is REF CURSOR RETURN REQUEST%ROWTYPE

and what Biztalk is reading is something like this

<GenRecordRow xmlns="http://Microsoft.LobServices.OracleDB/2007/03">
        <GenRecordColumn>
            <GenRecordColumn>
                <ColumnName>AGNCY_RQST_ID</ColumnName>
                <ColumnValue>545</ColumnValue>
                <ColumnType>System.Int64</ColumnType>
            </GenRecordColumn>
            <GenRecordColumn>
                <ColumnName>RQST_ID</ColumnName>
                <ColumnValue>4344</ColumnValue>
                <ColumnType>System.Int64</ColumnType>
            </GenRecordColumn>
</GenRecordRow>

Solution 1:

I had the same problem with BizTalk 2016 connecting Oracle19c, the current setup is BizTalk2016 and Oracle12c. And Oracle is planned to update to latest Oracle19c version.

So based on this setup we know BizTalk2016 will not support Oracle19c DB. We are using tightly coupled WCF-OracleDB adapter. We tried all the usual suspects, like upgrading Oracle Client, ODAC components in BizTalk Server. Nothing changed the below format to Original format,

So We tried analyzing on the Oracle side, as to compare what changed between Oracle12c vs Oracle19/18c. Basically we gone through all the depreciated functionality in both the DB’s documentation. And we found the below,

Ref : https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/behavior-changes-deprecated-desupport-oracle-database.html#GUID-543498D6-3799-4217-9BE3-4BB8630FC32D

Extract from Oracle Documentation “If you prefer to continue to collect the ALL_TYPES and the associated user views, ALL_TYPE_ATTRS and ALL_COLL_TYPES in ARGUMENTS views, then you can set events to events='10946, level 65536'. Setting this event reverts the ARGUMENTS views back to the behavior in Oracle Database releases earlier than 12.1, in which DATA_LEVEL can be greater than 0, and descriptive metadata for the type and any nested types is included in the view. If you make this change, then you must recompile affected packages after you set the event. When you recompile the affected packages, the compiler recollects the additional metadata. This event also reverts OCIDescribeAny() to the behavior in Oracle Database releases earlier than 12.1”

Based on the above documentation in the latest version DB’s they are moving the Arguments list into three different tables,

• **ALL_PLSQL_TYPES
• ALL_PLSQL_TYPE_ATTRS
• ALL_PLSQL_COLL_TYPES**

This is important to BizTalk as BizTalk is looking for the Schema information in this table.

all_arguments

Now that Oracle Moved the Schema/Meta data information into the new table, BizTalk's Schema rendering is different from the Oracle12 Vs Oracle19/18c.

So we set/replicate the events as mentioned above in a session, and tried to recompile just the BizTalk packages, BizTalk2016 Started working as usual.

1. alter session set events '10946 trace name context forever, level 65536';
2. alter package <package_name> compile;

This is just a work around not an actual solution. But for a short term until we re-write the code this will work.

Note: We are still testing the solution on all fronts like performance/load testing in the solution. And we are yet to figure out the side effects of this solution. I will keep posted if we found anything.