Multi-threaded OracleTable issue?

[cosullivan]

Hi,

I am calling a stored procedure which takes in an OracleTable as its parameter. When calling my function which executes this procedure on its own it works perfectly fine, however, if I call this function multiple times concurrently, with each call on its own thread I get the error below.

Any ideas as to why?

Thanks,
Cain

Cannot convert object of type 'System.Object' to object of type 'Devart.Data.Oracle.OracleTable'.
at Devart.Data.Oracle.av.a(Byte[] A_0, Int32 A_1, Object A_2)
at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, ObjectA_2, Byte[] A_3, Hashtable A_4, Int32 A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int3
2 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, OracleType
A_13, am A_14, Boolean& A_15)
at Devart.Data.Oracle.OracleParameter.a(bb& A_0, Boolean A_1, OracleConnectio
n A_2, Byte[] A_3, Hashtable A_4, am A_5, Boolean& A_6, Int32 A_7)
at Devart.Data.Oracle.OracleCommand.a(ak A_0, Int32 A_1, OracleParameterColle
ction A_2, am A_3, Boolean& A_4)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior,
IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior,
Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()

[cosullivan]

After spending the last 7 hours trying to figure it out we decided to install the Oracle client and turn Direct mode off - it now works flawlessly.

We are using version 6.80, so this appears to have some stability issues with regards to Direct mode.

[Shalex]

Thank you for your report. We can reproduce the problem in our environment intermittently, so it is very difficult to debug and fix the issue. Please give us the following information:
1) does the problem persist in your environment permanently? If yes, please send us a small test project with the corresponding DDL script;
2) specify the exact build (x.xx.xxx) of your dotConnect for Oracle and the version (xx.x.x.x) of your Oracle server.

We are investigating the issue.

[epascales]

Hi, I have similar problem. If anyone knows the solution to this problem? I would be grateful for your help...

Cheers

[Pinturiccio]

We could not reproduce the issue. Please provide the following information:
1) does the problem persist in your environment permanently? If yes, please send us a small test project with the corresponding DDL script;
2) specify the exact build (x.xx.xxx) of your dotConnect for Oracle and the version (xx.x.x.x) of your Oracle server.

[MatthewVukomanovic]

I am having the same issue (Exception at bottom).

The query runs fine when run individually. But when it's run in parallel then start getting the exception about not being about to convert the type.

This can happen with only two procedures being run at the same time, however to consistently get it running 6 or more seems to do the trick.

The dotConnect for Oracle version I'm using is 7.5.179 07-Feb-13

The oracle server that it is connecting to has this version information

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

The database definitions are

Code: Select all

FUNCTION GET_BASICF (F_BASIC IN MYFILTER_BASIC :=null) RETURN ref_cursor;

TYPE MYFILTER_BASIC as object
(
  Requestor       GTARRAY,
  start_date      date,
  end_date        date,
  L_ID         GTARRAY,
  L2_ID       GTARRAY,
  A1_ID       GTARRAY,
  POS_ID  GTARRAY,
  O_ID    GTARRAY,
  F_ID      GTARRAY,
  A2_ID         GTARRAY,
  D_ID         GTARRAY,
  R1_ID       GTARRAY,
  P1_ID        GTARRAY,
  P2 varchar2(3),
  O1_ID    GTARRAY,
  ol_start   date,
  ol_end     date,
  created_by      GTARRAY,
  last_updated_by GTARRAY,
  R_P    varchar2(80),
  A3_P   varchar2(3),
  RSQ           varchar2(3) 
);

TYPE "GTARRAY" is table of number;

I thought that the issue might have been fetching the database types. So I now exclusively fetch the objects i.e., I lock a collection, fetch and add the type, and then unlock the collection. So that way it's not errors with the types trying to be retrieved at the same time.

However this doesn't make any difference and it still gets the exception below. The exception would suggest to me that it is having a problem with the input parameter since the output parameter is a simple ref_cursor

System.InvalidOperationException occurred
HResult=-2146233079
Message=Cannot convert object of type 'System.Object' to object of type 'Devart.Data.Oracle.OracleObject'.
Source=Devart.Data.Oracle
StackTrace:
at Devart.Data.Oracle.br.a(Byte[] A_0, Int32 A_1, Object A_2)
at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Object A_2, Byte[] A_3, Hashtable A_4, Int32 A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, OracleType A_13, o A_14, Boolean& A_15)
at Devart.Data.Oracle.OracleParameter.a(bs& A_0, Boolean A_1, OracleConnection A_2, Byte[] A_3, Hashtable A_4, o A_5, Boolean& A_6, Int32 A_7)
at Devart.Data.Oracle.OracleCommand.a(ai A_0, Int32 A_1, OracleParameterCollection A_2, o A_3, Boolean& A_4)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Project.Services.Common.Repository.RepositoryBase.GetEntityList[T](DbCommand cmd, PropertyMap`1 mapping, Nullable`1 maxRecords) in d:\project\Source\Services.Common\Repository\Base\RepositoryBase.cs:line 189
InnerException:

[Shalex]

We have reproduced the "Cannot convert object of type 'System.Object' to object of type 'Devart.Data.Oracle.OracleObject'" error using your database objects with dotConnect for Oracle v7.5.179. But the latest (8.1.65) build is without this problem. Please upgrade.

If you do not have access to the registered dotConnect for Oracle v8.1.65, you can make sure that the issue is fixed with the trial version which can be downloaded from http://www.devart.com/dotconnect/oracle/download.html.