Bug with unicode database version and object parameters

Bug with unicode database version and object parameters

Postby Sergey Schukin » Sun 18 Dec 2011 13:03

Code: Select all
DB version: Oracle Database 11g Express Edition Release 11.2.0.2.0


I created a custom object database named T_ADDR:
Code: Select all
CREATE OR REPLACE TYPE T_ADDR AS OBJECT (
      VALUE1           VARCHAR2(23 CHAR),
      VALUE2           VARCHAR2(5 CHAR),
      VALUE3           VARCHAR2(5 CHAR)
   );


...and datatable named TABLE1:
Code: Select all
CREATE TABLE TABLE1 (
  ID NUMBER(9),
  NAME VARCHAR2(40 CHAR),
  ADDR T_ADDR)


When the connection string is set
Code: Select all
Unicode=True
, I can insert/update a value T_ADDR('11','11','11'), but cannot insert/update a value T_ADDR('11111111111111111111111','11111','11111') and get an error
Code: Select all
"ORA-21525: attribute number or (collection element at index) %s violated its constraints"


When the connection string is set
Code: Select all
Unicode=False
I get an error
Code: Select all
Object reference not set to an instance of an object.


A similar situation with NULL values.

My code:
Code: Select all
var command = new OracleCommand("UPDATE TABLE1 SET ID = :in_ID, NAME =:in_NAME, ADDR = :in_ADDR, connection);
            command.Parameters.Add("in_ID", card.Id);
            command.Parameters.Add("in_NAME", card.Name);
            command.Parameters.Add("in_ADDR", card.Addr.GetOracleObject(connection));
            command.ExecuteNonQuery();

...

public OracleObject GetOracleObject(OracleConnection connection)
      {
         var oracleObject = new OracleObject(OracleType.GetObjectType("T_ADDR", connection));
         oracleObject["VALUE1"] = val1;
         oracleObject["VALUE2"] = val2;
         oracleObject["VALUE3"] = val3;
         return oracleObject;
      }


The bug also appears in dbForge Studio for Oracle product.
Sergey Schukin
 
Posts: 10
Joined: Thu 22 Jan 2009 12:10

Postby Sergey Schukin » Mon 19 Dec 2011 15:15

I think I found the cause. The bug in the Oracle Instant client, not the Devart component.
Sergey Schukin
 
Posts: 10
Joined: Thu 22 Jan 2009 12:10

Postby Sergey Schukin » Tue 20 Dec 2011 09:45

Please help. I can not insert NULL value into a row.

Code: Select all
Oracle Client 11.2.0.1 x64
OS - Windows 7 x64


Code: Select all
connectionString="User Id=myuser;Password=mypassword;Server=SRV_TEST;Unicode=true;"


Code: Select all
var o = new OracleObject("T_ADDR", connection);
            o["VALUE1"] = DBNull.Value;
            o["VALUE2"] = DBNull.Value;
            o["VALUE3"] = DBNull.Value;
            
command.Parameters.Add("MY_FIELD", o);


I get an error
Code: Select all
Object reference not set to an instance of an object.


if I do so

Code: Select all
command.Parameters.Add("MY_FIELD", DBNull.Value);


I get an error
Code: Select all
ORA-00932: inconsistent datatypes: expected T_ADDR got CHAR
Sergey Schukin
 
Posts: 10
Joined: Thu 22 Jan 2009 12:10

Postby Pinturiccio » Tue 20 Dec 2011 14:20

Try using the following code:
Code: Select all
var o = new OracleObject("T_ADDR", connection);
o["VALUE1"] = DBNull.Value;
o["VALUE2"] = DBNull.Value;
o["VALUE3"] = DBNull.Value;
           
command.Parameters.Add("MY_FIELD",  OracleDbType.Object);
command.Parameters["MY_FIELD"].Value = o;
Pinturiccio
Devart Team
 
Posts: 1849
Joined: Wed 02 Nov 2011 09:44

Postby Sergey Schukin » Tue 20 Dec 2011 16:10

Yes, it works. thank you.
Sergey Schukin
 
Posts: 10
Joined: Thu 22 Jan 2009 12:10


Return to dotConnect for Oracle