Bug with unicode database version and object parameters

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Sergey Schukin
Posts: 10
Joined: Thu 22 Jan 2009 12:10

Bug with unicode database version and object parameters

Post by 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

Post by 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

Post by 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

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Post by 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;

Sergey Schukin
Posts: 10
Joined: Thu 22 Jan 2009 12:10

Post by Sergey Schukin » Tue 20 Dec 2011 16:10

Yes, it works. thank you.

Post Reply