Forum Stats

  • 3,759,521 Users
  • 2,251,560 Discussions
  • 7,870,690 Comments

Discussions

OracleCommandBuilder.DeriveParameters(OracleCommand command)

User_RBD7W
User_RBD7W Member Posts: 3 Green Ribbon
edited Jun 21, 2021 5:21AM in ODP.NET

Hi Team,

We have one test case try to call a stored procedure on oracle 12c and 19c platforms, it's the exactly the same test case for both platforms. If we run the test separately only on a 12c database, it's fine. If we run the test case only on 19c database, it's also good.

BUT if we get the test case run in a sequence automatically, for example: run test case on 12c first then run the same test case on 19c in one test round: the test case for 12c is OK, but we got exception for the 19c test case.

Exception messge: "System.IndexOutOfRangeException: Index was outside the bounds of the array."

 Stack Trace: 

  OracleCommandBuilder.SetUpDpCommand(OracleCommand command)

  OracleCommandBuilder.DeriveParameters(OracleCommand command)


IF we change the running sequence: run test case on 19c first and then 12c in one test round, the 19c test run will be OK, but the 12c test run will failed with this error message: "ORA-01006: bind variable does not exist"

And it's coming from the same point :   OracleCommandBuilder.DeriveParameters(OracleCommand command).


So did anyone notice this kind of issue before or any help with this issue? Thanks.

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,934 Employee

    Are you using the same OracleConnection object to run the 19c and 12c stored procedures sequentially?

    If so, I suspect that ODP.NET statement caching and metadata caching are re-using the first DB version's query and table metadata in the second query attempt instead of deriving anew. As 12c and 19c are two different major DB versions, there's could be some difference between the two in what they provide for the metadata.

    You can try turning off statement caching and metadata caching whenever you switch the DB version you query. Or you can use two separate connection pools for 19 and 12c.

  • User_RBD7W
    User_RBD7W Member Posts: 3 Green Ribbon

    Hi Alex,

    Thanks for your reply and we do using different OracleConnection object to run the test for 19c and 12c.

    So before each test run, we will create a new OracleConnection object with the right setup detail (12c database and 19c database).

    I also agreed that something must be cached somewhere to get this exception, just not sure where and how that could be happen, as the test run for 12c and 19c, we kind of create a completely new set of object for each.


    And here is the connection string example:

    19c : "User ID=19cSchemaName;Password=password;Data Source=(DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(Host = Ora-06.com)(Port = 1521))  (CONNECT_DATA = (SID = DEVOra19c)));Metadata Pooling=false"

    12c: "User ID=12cSchemaName;Password=password;Data Source=(DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(Host = v-devserver)(Port = 1521))  (CONNECT_DATA = (SID = DEVOra12c)));Metadata Pooling=false"

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,934 Employee

    You should also turn off self tuning (Self Tuning=false). That will disable statement caching if you haven't set the statement cache size. With statement caching disabled and if the problem disappears, then it's almost certain the problem is with the statement cache.

    Which provider type (unmanged, managed, or core) are you using and which ODP.NET version?

  • User_RBD7W
    User_RBD7W Member Posts: 3 Green Ribbon

    We are using Oracle.ManagedDataAccess.dll (version 19.11.0).

    I tried the connection string like this but still not working:

    19c : "User ID=19cSchemaName;Password=password;Data Source=(DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(Host = Ora-06.com)(Port = 1521))  (CONNECT_DATA = (SID = DEVOra19c)));Metadata Pooling=false;Self Tuning=false"

    12c: "User ID=12cSchemaName;Password=password;Data Source=(DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(Host = v-devserver)(Port = 1521))  (CONNECT_DATA = (SID = DEVOra12c)));Metadata Pooling=false;Self Tuning=false"

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,934 Employee

    Your symptoms match another known bug (32843859). This looks like the same issue.

    It's a bug that was first filed against 19.11. A possible workaround is to downgrade the ODP.NET version until the bug is fixed. With that said, we haven't identified the root cause yet. So, it's not clear which earlier 19c version will work. Since this problem was found relatively recently, it's possible 19.11 was when this bug was first introduced.

  • User_P3O5D
    User_P3O5D Member Posts: 1 Green Ribbon

    Can someone from Oracle provide an update on the status of the bug 32843859? For some reason, we are unable to find this bug ID on Oracle's bug tracker site. We too have the same issue with Oracle client 19.1, connecting to 12c & 19c database versions. Knowing when the issue will be resolved, could help us decide whether to wait for the fix to be released or downgrade to lower version of Oracle client. Thanks!

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,934 Employee

    I've updated the bug to make it publicly viewable. You should be able to see it when you log into MOS in about 24 hours. The bug hasn't been resolved yet, but we have reproduced it in house.