BUG: OCI invalid handle: OracleObject with TIMESTAMP Type

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
goethals.f
Posts: 60
Joined: Mon 18 May 2009 13:17

BUG: OCI invalid handle: OracleObject with TIMESTAMP Type

Post by goethals.f » Wed 02 Jun 2010 15:44

Hello,

I think I have found a bug concerning the use of

*) custom Oracle Object Types which have a TimeStamp membertype
*) in combination with a custom Oracle Table Type of those types
*) used as an input parameter in a stored procedure.

I get the "OCI invalid handle" error when executing this procedure

I got the same error while executing the code generated by the Devart code generation tools for the object and the package.


When i use a object which have NO TimeStamp member --> everything runs smoothly (I have already several working examples, but all WITHOUT a Timestamp membertype).

I use Oracle 10.2.0.1 client (tested with oracle8 client but also error)
Server is Oracle 10.2.0.4
dotconnect 5.70.140.0


Can you reproduce this behaviour/BUG our point me otherwise to an example of this scenario.

Thx
Fred



-------------------------------------------



TYPE TESTING_ADMIN.WVLB_OBJECT3 AS OBJECT (
ID NUMBER(38),
VALUE1 VARCHAR2(20),
VALUE2 VARCHAR2(20),
NUMBER1 NUMBER(38),
TIMESTAMP1 TIMESTAMP(6)
)



CREATE OR REPLACE TYPE TESTING_ADMIN.WVLB_OBJECT3_TABLE AS
TABLE OF WVLB_OBJECT3;


--> even this empty procedure gives error, but works when there is no timestamp involved !!!

PROCEDURE INSERT_DETAILS3 (
p_wvlb_Object3_Table IN TESTING_ADMIN.WVLB_OBJECT3_TABLE
)
AS

BEGIN
null;
END INSERT_DETAILS3;

goethals.f
Posts: 60
Joined: Mon 18 May 2009 13:17

Post by goethals.f » Thu 03 Jun 2010 08:49

I have been testing further, and have some additional remarks:


*) When I have the following code:

Code: Select all

Devart.Data.Oracle.OracleTimeStamp myOracleTimeStamp = Devart.Data.Oracle.OracleTimeStamp.GetSysDate();
--> there is no time-info in the TimeStamp, only the current Date. (Time is always 00:00:00)


*) I have tested calling a procedure with one input Timestamp parameter, and this procedure I can use in my code without oci-call error.

But the TimeStamp value still has no time-info. (even when I specify the correct Type for the parameter: OracleDbType.TimeStamp)


PS: when I execute the same insert procedure, but use the normal
System.DateTime.Now as value (instead of the devart-one) --> my timestamp value is correct in the DB.


So I think there is maybe a bug concerning the use of TimeStamps in Object and Tables....

goethals.f
Posts: 60
Joined: Mon 18 May 2009 13:17

Post by goethals.f » Thu 03 Jun 2010 09:17

Hello again,

I have tested again, and now explicitly used the devart Object-wizard and the package-wizard.

And when executing my insert procedure which takes a table as input parameter (table consist of objects which have a Timestamp member)


The "OCI invalid handle." occurs in the wizard created object on the bold line:

oracleObjectOci["TIMESTAMP_SYSTIMESTAMP"] = this.mTIMESTAMP_SYSTIMESTAMP;

Code: Select all

    public Devart.Data.Oracle.NativeOracleObject ToOracleObject(Devart.Data.Oracle.OracleConnection connection) {
      NativeOracleObject oracleObjectOci;
      oracleObjectOci = new NativeOracleObject("TESTING_ADMIN.WVLB_OBJECT", connection);
      if (this.isNull) {
        return oracleObjectOci;
      }
      oracleObjectOci["ID"] = this.mID;
      oracleObjectOci["VALUE1"] = this.mVALUE1;
      oracleObjectOci["VALUE2"] = this.mVALUE2;
      oracleObjectOci["NUMBER1"] = this.mNUMBER1;
      [b]oracleObjectOci["TIMESTAMP_SYSTIMESTAMP"] = this.mTIMESTAMP_SYSTIMESTAMP;[/b]
      oracleObjectOci["TIMESTAMP1"] = this.mTIMESTAMP1;
      return oracleObjectOci;
    }
The error/stack trace is this:

Code: Select all

   bij Devart.Data.Oracle.bp.b(Int32 A_0)
   bij Devart.Data.Oracle.NativeOracleObjectBase.a(Object A_0, OracleAttribute A_1, IntPtr A_2)
   bij Devart.Data.Oracle.NativeOracleObject.a(Object A_0, OracleAttribute A_1)
   bij Devart.Data.Oracle.NativeOracleObject.set_Item(OracleAttribute attribute, Object value)
   bij Devart.Data.Oracle.NativeOracleObject.SetItemValue(Object value, String name)
   bij Devart.Data.Oracle.NativeOracleObjectBase.set_Item(String Name, Object value)
   bij TestDotConnect_OracleTable_WVLB.WVLB_OBJECT.ToOracleObject(OracleConnection connection) in T:\goethals.f\TFS_Projects\SADEF_TEST\Main\Source\TestDotConnect_OracleTable_WVLB\TestDotConnect_OracleTable_WVLB\WVLB_OBJECT.cs:regel 155
   bij Devart.Data.Oracle.OracleArray.a(OracleConnection A_0, NativeOracleArray A_1)
   bij Devart.Data.Oracle.OracleArray.Devart.Data.Oracle.ICustomOracleArray.ToOracleArray(OracleConnection con)
   bij 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, String A_13, av A_14, Boolean& A_15)
   bij Devart.Data.Oracle.OracleParameter.a(aq& A_0, Boolean A_1, OracleConnection A_2, Byte[] A_3, Hashtable A_4, av A_5, Boolean& A_6)
   bij Devart.Data.Oracle.OracleCommand.a(y A_0, Int32 A_1, OracleParameterCollection A_2, av A_3, Boolean& A_4)
   bij Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   bij Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
[/b]

So I really think there is somewhere something wrong with the TimeStamps (see the Timestamp.getsysdate which is also wrong...)

Do you have any thought on this ?

thx
Fred

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 03 Jun 2010 16:27

We've reproduced the problem with the GetSysDate function. We will inform you when it is fixed.

As for other problems, could you please send us a complete sample project with which they can be reproduced?

goethals.f
Posts: 60
Joined: Mon 18 May 2009 13:17

Post by goethals.f » Mon 07 Jun 2010 15:44

StanislavK wrote:We've reproduced the problem with the GetSysDate function. We will inform you when it is fixed.

As for other problems, could you please send us a complete sample project with which they can be reproduced?
I have uploaded a test project.

I got the OCI-invalid error when trying to pass the Table to a procedure as input param.

If possible, please modify or give some hints on which is the best way to transfer custom table types of custom object types?.

And how the interaction works with the devart generated code-objects (maybe i'm doing it wrong)

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Tue 08 Jun 2010 14:45

Thank you for the project, we've reproduced the problem. Also, we've answered you by mail.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 09 Jul 2010 17:13

We've fixed the problem with the GetSysDate function. The fix is available in the latest 5.70.146 build of dotConnect for Oracle. The build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For more information on improvements and fixes available in the 5.70.146 version, please refer to
http://www.devart.com/forums/viewtopic.php?t=18424

goethals.f
Posts: 60
Joined: Mon 18 May 2009 13:17

Post by goethals.f » Mon 02 Aug 2010 12:37

StanislavK wrote:We've fixed the problem with the GetSysDate function. The fix is available in the latest 5.70.146 build of dotConnect for Oracle. The build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For more information on improvements and fixes available in the 5.70.146 version, please refer to
http://www.devart.com/forums/viewtopic.php?t=18424
Hello,

I have rechecked my test-project (which I have submitted to devart also)

The problem with the getsysDate is indeed solved, but I am still unable to use a timestamp-field in my custom oracle object/table in combination with the input-parameter....

I still receive the "OCI-invalid handle" error, exactly as in the first posts...

Is this problem really solved ?

PS: I have tested with the version 5.70.152. I did not tested it with the specific 5.70.146 !!!


PS2: concerning looping over an OracleTable object (which is output-parameter from a procedure) --> I still don't get it how we can easily cast the generic OracleObject to the wizard-generated specific-object...)

I thought we should be able to use the FromOracleObject(...) method, but the generic OracleObject from the table, can not be casted to the NativeOracleObject...




Code: Select all

 

            //Get manual output parameter (table)

            Devart.Data.Oracle.OracleTable myOracleTableOut = (Devart.Data.Oracle.OracleTable)tmpOracleParameter_OUT_p_Devart_Object_Table.Value;
                                   
            foreach (Devart.Data.Oracle.OracleObject myOracleObjectItem in myOracleTableOut)
            {
                myDEVART_OBJECT = new DEVART_OBJECT();

                //QUESTION::: FromOracleObject --> does not work: how can we easy convert each OracleObject to --> DEVART_OBJECT
                //myDEVART_OBJECT.FromOracleObject(myOracleObjectItem);
                
//Casting is not possible
//myDEVART_OBJECT.FromOracleObject((Devart.Data.Oracle.NativeOracleObject)myOracleObjectItem);
                               
                //--> SOLUTION: manual Reading each properties via myOracleObjectItem[...] as wizard generates in FromOracleObject
--> can not be the goal of wizard-code if we have to copy it manual ....

                ...

            }



StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Wed 04 Aug 2010 08:39

The 5.70.146 build contains a fix for the GetSysDate issue only. As for the 'OCI invalid handle' problem, we are working on it and will inform you when it is fixed.

As for iterating through the OracleTable object, we will investigate the possibility of extending the Object wizard for generating methods similar to FromOracleObject and ToOracleObject, but using the OracleObject class instead of NativeOracleObject. At the moment, you can add an overload of the FromOracleObject method to the DEVART_OBJECT class. Please try creating the following method:

Code: Select all

public void FromOracleObject(Devart.Data.Oracle.OracleObject oraObject) 
and insert into it the code of the original FromOracleObject function. Please tell us if this helps.

goethals.f
Posts: 60
Joined: Mon 18 May 2009 13:17

Post by goethals.f » Fri 10 Sep 2010 13:58

StanislavK wrote:The 5.70.146 build contains a fix for the GetSysDate issue only. As for the 'OCI invalid handle' problem, we are working on it and will inform you when it is fixed.
Hello,

Did you guys made some progress on fixing the 'OCI invlida handle' error. (Related to the timestamp-type in an oracle custom object/table)

because now i'm really stuck. I can try to continue with a string replacement in the object and doing multiple to_timestamp(...) conversions in my pl-sql package code.
Also a lot of 'converting' is needed in the .net apps between the datetime and string objects.
and when the fix is there, change it all over again to real timestamp-types etc...


So a real fix would be really appreciated and a huge step forward.


Thx and hopefully you guys find the solution
Fred

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 13 Sep 2010 12:11

We are still working on the issue. We will inform you about the results as soon as possible.

jesuissur
Posts: 12
Joined: Wed 13 Oct 2010 14:28

Post by jesuissur » Mon 29 Nov 2010 22:22

StanislavK wrote:We are still working on the issue. We will inform you about the results as soon as possible.
We got an 'OCI Invalid Handle' with the latest version (6.00.58) when Entity Framework (using dotConnect for Oracle) open a connection.

It was fine with a previous version 5 build but we had a distributed transaction problem and want to give a try to the latest version.

Is it still something you are working on? Some workaround?

Thank you very much
Phil

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 02 Dec 2010 12:25

Please let us know if the problem persists using the local transaction scope.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 10 Dec 2010 12:56

We have resolved the problem with 'OCI invalid handle'. It was actually an error in the OCI, and we have implemented a workaround so that now inserting timestamps into user-defined types should work properly. These changes will be available in the nearest build of dotConnect for Oracle.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 17 Dec 2010 10:30

We have released the new 6.0.69 version of dotConnect for Oracle where these changes are available. The new build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For more information about the fixes and improvements available in dotConnect for Oracle 6.0.69, please refer to
http://www.devart.com/forums/viewtopic.php?t=19791

Post Reply