OracleCommand.Prepare hang on v5.70

OracleCommand.Prepare hang on v5.70

Postby chengkungui » Mon 31 May 2010 05:35

OracleCommand.Prepare method hang when prepare a stored procedure with PLSQL table as parameters in synchronization mode(ParameterCheck set to true).

This funtcion used to work in version 5.25. It stopped working after I upgarded to 5.70 recently.

It seems to fail sync
Code: Select all
create or replace package ArrayTest is

  type TArray is TABLE OF VARCHAR(50) INDEX BY BINARY_INTEGER;
 
  PROCEDURE TestPassArray(pArray IN TArray, pCount OUT INTEGER);

end ArrayTest;

create or replace package body ArrayTest IS

  PROCEDURE TestPassArray(pArray IN TArray, pCount OUT INTEGER) is
  begin
    pCount := pArray.count;
  end;
 
end ArrayTest;


Code: Select all
        public void TestArray()
        {
            OracleConnection conn = new OracleConnection("your connection");
            conn.Open();
            OracleCommand cmd = conn.CreateCommand();
            cmd.Parameters.Clear();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "ArrayTest.TestPassArray";
            cmd.ParameterCheck = true;
            cmd.Prepare();
            string[] array = new string[3]{"0", "1", "2"};
            cmd.Parameters[0].Value = array;
            cmd.ExecuteNonQuery();
            Assert.AreEqual(3, cmd.Parameters[1].Value, "Incorrect item count.");
            cmd.Dispose();
            conn.Close();
        }
chengkungui
 
Posts: 5
Joined: Mon 31 May 2010 00:44

Postby Shalex » Mon 31 May 2010 12:20

We have reproduced the problem with the 5.70.140 build. We will notify you when it is fixed.

It seems like this version also doesn't allow to declare the pArray parameter explicitly (without the ParameterCheck=true option). Please look forward to the fix.
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Tue 01 Jun 2010 11:18

I have reproduced two problems with dotConnect for Oracle 5.70 before posting my previous message:
1) program hangs on OracleCommand.Prepare();
2) the "Parameter value is incorrect." error when initializing parameters explicitly (the TArray type should be global in this case, not within a package):
Code: Select all
    cmd.Parameters.Add("pArray", OracleDbType.Table).ObjectTypeName = "scott.TArray";
    cmd.Parameters["pArray"].Value = array;
    cmd.Parameters.Add("pCount", OracleDbType.Integer).Direction = System.Data.ParameterDirection.Output;


Now we have fixed the problem with the "Parameter value is incorrect." error, but cannot reproduce again "OracleCommand.Prepare hangs" in our test environment. Please specify the exact versions of your:
a) Oracle Server (xx.x.x.x.x);
b) Oracle Client (xx.x.x.x);
c) dotConnect for Oracle (x.xx.xxx) from the Tools > Oracle > About menu of Visual Studio.
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44

Postby chengkungui » Mon 07 Jun 2010 06:56

Oracle server version is 10.2.0.4.0 64bit
Client OCI version is 10.1.0.4.0
dotConnet for oracle 5.70.140

OracleCommand.Prepare() only hang in synchronization mode. i.e. ParameterCheck set to true. Otherwise, it is fine.

I don't really know the difference between global table type and package table type in term of using it as procedure parameters. But I am pretty sure we have been using package table type for ages and no problems so far.

Thanks.
chengkungui
 
Posts: 5
Joined: Mon 31 May 2010 00:44

Postby Shalex » Wed 09 Jun 2010 10:33

1. Please specify your call stack when the program hangs. This will help us to identify the problem.
2. Could you please confirm that your Oracle Client version is 10.1.0.4 (maybe, it was a misprint and you meant 10.2.0.4)?
3. Do you have possibility to check if the problem persists with another version of Oracle Client?

We cannot reproduce the problem when OracleCommand.Prepare() hangs at the moment.
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44

Postby chengkungui » Thu 10 Jun 2010 05:02

My Oracle client version is 10.1.0.4. We have not upgrade it for a while.
This is all the threads of my unit test. It is hanging at OracleCommand.Prepare()

Image
the call stack of OraOCIEI10.dll!SltsqtStartFunc is
ntdll.dll!KiFastSystemCallRet
KERNEL32.dll!WaitForMultipleObjectsEx+0x8e
OraOCIEI10.dll!nigcui+0xf8ec
OraOCIEI10.dll!ldmGetMessageId+0x105134
OraOCIEI10.dll!putenv+0x20aa1
OraOCIEI10.dll!lxu4Decomposition+0x3948
OraOCIEI10.dll!SltsqtStartFunc+0x17
KERNEL32.dll!BaseThreadInitThunk+0x12
ntdll.dll!RtlInitializeExceptionChain+0x63
ntdll.dll!RtlInitializeExceptionChain+0x36
chengkungui
 
Posts: 5
Joined: Mon 31 May 2010 00:44

Postby chengkungui » Thu 10 Jun 2010 05:43

I just upgrade my oracle client to 11.1.0.1. The poblem still exists.
chengkungui
 
Posts: 5
Joined: Mon 31 May 2010 00:44

Postby Shalex » Fri 11 Jun 2010 16:12

Unfortunately, this is not .NET call stack, and it is not clear where the problem originates from (either ParameterCheck implementation or procedure execution). This call stack says that OCI tries to receive the error message (or the message that there are no errors), but it hangs on the critical section.

1. Please check that you are using the OracleConnection object in one thread (from the moment of creation till executing commands on it).
2. Also try setting Devart.Data.Oracle.OracleUtils.UseSeparateOCIEnvironment=false. Maybe the problem is here.
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44

Postby chengkungui » Wed 16 Jun 2010 05:31

This is the .net call stack.
I have set the OracleUtils.UseSeparateOCIEnvironment to false.
Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleUtils.MakeParameterName(string pName = "PARRAY", System.Collections.ArrayList existingNames = Count = Cannot evaluate expression because the code of the current method is optimized.) + 0x219 bytes
Devart.Data.Oracle.dll!Devart.Data.Oracle.as.a(string A_0, int A_1 = 0x00000000) + 0x1242 bytes
Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleCommand.DescribeProcedure(string name = "ArrayTest.TestPassArray") + 0xeb bytes
Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleCommand.CreateStoredProcSql(string procName) + 0x2e6 bytes
Devart.Data.dll!Devart.Common.DbCommandBase.CreateSql() + 0x57 bytes
Devart.Data.dll!Devart.Common.DbCommandBase.Sql.get() + 0x23 bytes
Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleCommand.InternalPrepare(bool implicitPrepare, int startRecord = 0x00000000, int maxRecords) + 0x88 bytes
Devart.Data.dll!Devart.Common.DbCommandBase.Prepare() + 0x6b bytes
chengkungui
 
Posts: 5
Joined: Mon 31 May 2010 00:44

Postby Shalex » Thu 17 Jun 2010 16:04

We are investigating the issue.
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Fri 18 Jun 2010 07:54

We have implemented some changes in our source code basing on your call stack. Look forward to the next build. We plan to release it next week. I will post here when it is available for download.
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 07 Jul 2010 17:57

New build of dotConnect for Oracle 5.70.146 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=18424 .
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle