locked
Biztalk 2016(2020) Oracle 2019 Strong-typed cursor schema RRS feed

  • Question

  • Hi everybody,

    We`ve started to migrate oracle version 11.2.0.2  to version 19.3c. BizTalk version both 2016 and 2020.

    We faced with next problem:

    We`re trying to load oracle procedure schema strong typed cursor as out parameter into biztalk solution. As a result we get schema as a weaked-typed cursor: all returned columns  interpred as a columnName, columnValue, columnType.     

    create or replace package test is

     TYPE TREFUSAl_LIST IS RECORD(
       product_code VARCHAR2(32),
       quantity VARCHAR2(100)
       );

     TYPE TREFUSAL_LIST_Resp IS REF CURSOR RETURN TREFUSAl_LIST;
     FUNCTION AutoImport_dispatch_refusals(pcsd_code IN VARCHAR2
                                           ) RETURN TREFUSAL_LIST_Resp;

    end test;

             

    create or replace package body test is

    FUNCTION AutoImport_dispatch_refusals(pcsd_code IN VARCHAR2
                                          ) RETURN TREFUSAL_LIST_Resp
     IS 
      vcur TREFUSAL_LIST_Resp;
     BEGIn

      OPEN vcur FOR 
       SELECT 'code' product_code, 'qty' quantity from dual;

       RETURN vcur;

     end;  
    begin
      -- Initialization
      null;
    end test;

      <complexType name="GenRecordRow">
        <sequence>
          <element minOccurs="0" maxOccurs="1" name="GenRecordColumn" nillable="true" type="ns3:ArrayOfGenRecordColumn" />
        </sequence>
      </complexType>
      <element name="GenRecordRow" nillable="true" type="ns3:GenRecordRow" />
      <complexType name="GenRecordColumn">
        <sequence>
          <element minOccurs="1" maxOccurs="1" name="ColumnName" type="string" />
          <element minOccurs="1" maxOccurs="1" name="ColumnValue" nillable="true" type="string" />
          <element minOccurs="1" maxOccurs="1" name="ColumnType" type="string" />
        </sequence>
      </complexType>
      <element name="GenRecordColumn" nillable="true" type="ns3:GenRecordColumn" />
      <complexType name="ArrayOfGenRecordColumn">
        <sequence>
          <element minOccurs="0" maxOccurs="unbounded" name="GenRecordColumn" type="ns3:GenRecordColumn" />
        </sequence>
      </complexType>
      <element name="ArrayOfGenRecordColumn" nillable="true" type="ns3:ArrayOfGenRecordColumn" />
      <complexType name="ArrayOfGenRecordRow">
        <sequence>
          <element minOccurs="0" maxOccurs="unbounded" name="GenRecordRow" type="ns3:GenRecordRow" />
        </sequence>
      </complexType>
      <element name="ArrayOfGenRecordRow" nillable="true" type="ns3:ArrayOfGenRecordRow" />

    Moreover when we were trying to create Simple WinFroms Applicaton with DataSet using such kind of procedure we`ve got an error: "There is no mapping for the "ProviderDbType" mapped member"

    As we`ve investigated Oracle forums the most related topic is an Oracle bug 28672457  There was a change in how the table functions get the attribute names which was solved by patch. 

    Does anybody know is there any solution for ODP.Net? 

    Friday, August 21, 2020 8:02 AM

All replies

  • We had update in BizTalk 2020 CU1 to get more support for 18c/19c in some advanced scenarios. Oracle has changed the way it returns metadata so new types of queries from other system tables were needed and more permissions may be needed. You would need BizTalk 2020 CU1 as well : 

    https://support.microsoft.com/en-gb/help/4566494/fix-oracle-db-adapter-to-handle-database-artifacts-with-nested-types-i

    • Proposed as answer by Colin Dijkgraaf Wednesday, August 26, 2020 10:10 PM
    Wednesday, August 26, 2020 8:53 PM
  • Thanks for reply. We have such kind of BizTalk server version.

    I tried without CU1 the result was the same.

    I`ll check according to your solution and let you know.

    Wednesday, September 9, 2020 9:13 AM
  • Hi NiklasEngfelt,

    We`ve installed the CU1 for BTS2020, after that during uploading Oracle procedure output strong typed cursor as a schema in VS2019 Generated items we received  error 

    An error has occurred while retrieving the properties for the selected operation. Retrieval of Operation Metadata has failed while building WSDL at 'http://Microsoft.LobServices.OracleDB/2007/03/......

    Do you know what did go wrong?

    We use Oracle Client 18.3, BizTalk 2020 CU1, VS2019 extensions 3.13.2.0


    Monday, September 14, 2020 1:25 PM
  • I think you meant to address that to NiklasEngfelt.  That is something you need to raise a ticket with Microsoft.
    Monday, September 14, 2020 10:11 PM
  • You`re definitely right, to NiklasEngfelt

    NiklasEngfelt are you able to answer to question above?

    Tuesday, September 15, 2020 7:08 AM
  • I`ve found that there was a problem in BTS2010 via MS SQL DataBase very similiar to us. It was resolved by modifying FMTONLY=ON as I understood. Maybe we can do something like this in Oracle DB?

    Can you help us? please?

    Wednesday, September 16, 2020 7:09 AM
  • Thursday, September 17, 2020 8:19 PM