Bug with unicode database version and object parameters

[Sergey Schukin]

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]

I think I found the cause. The bug in the Oracle Instant client, not the Devart component.

[Sergey Schukin]

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

[Pinturiccio]

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;

[Sergey Schukin]

Yes, it works. thank you.