Devart Forums

Posted: **Thu 12 Apr 2012 13:19**

I get "ORA-21525 attribute number or (collection element at index) %s violated its constraints" when using an Object Type as parameter for a function when Unicode = true, if the length of the value is longer than 50% of the length of a varchar2-attribute of the object type.

Assume this scenario:
The type TAdress has been created (equal to the example in your documentation http://www.devart.com/dotconnect/oracle ... bject.html). Furthermore a Package with a function has been created:

Code: Select all

FUNCTION countEmployees(adress IN TAdress) RETURN NUMBER;

Now create the OracleCommand to call the function, add the Parameter and execute it:

Code: Select all

Using cm As New Devart.Data.Oracle.OracleCommand
   cm.CommandText = "BEGIN :RET := schema.package.countEmployees(:adress); END;"
..
   Dim pAdress As New Devart.Data.Oracle.OracleParameter
   pAdress.OracleDbType = Devart.Data.Oracle.OracleDbType.Object
   pAdress.Direction = ParameterDirection.Input
   pAdress.ObjectTypeName = "schema.TAdress"
   pAdress.ParameterName = "adress"
   pAdress.Value = Me.ToOracleObject(t.Connection)
   cm.Parameters.Add(pAdress)
..
   cm.ExecuteNonQuery()
End Using

This is the ToOracleObject-Function:

Code: Select all

         Public Function ToOracleObject(ByVal connection As FW.Data.Connection) As Devart.Data.Oracle.NativeOracleObject
            Dim oracleObjectOci As Devart.Data.Oracle.NativeOracleObject
            oracleObjectOci = New Devart.Data.Oracle.NativeOracleObject(OT_NAME, connection.OracleConnection)

            'Case1:
            'oracleObjectOci("Country") = "123456789012345"
            'Case2:
            'oracleObjectOci("Country") = "1234567890123456"

            Return oracleObjectOci
        End Function

With Unicode = false this will work for both cases.
With Unicode = true this will work for case 1, but will fail for case 2 and give ORA-21525 Exception. Although Country has a length of 30 (characters) and the value is just 16 characters long.

Is this probably a bug in NativeOracleObject?
I get this behaviour with 6.80.325. I just tested Functions of Packages. I did not test Procedures/Function outside of a package.

Regards,
Matthias

Posted: **Tue 17 Apr 2012 14:33**

We could not reproduce the issue. Please send us a small test project with DDL/DML scripts for reproducing the issue. Also please specify the version of your Oracle Client and Oracle database.

Posted: **Tue 17 Apr 2012 17:34**

Sample project including scripts has been submitted.

I get the ORA21525-exception with OracleClient 10.2.0.1 (together with devart 5.70.140)
With Oracle Client 11.1.0.6 (and devart 6.80.325) the ExecuteReader just gets stuck and never returns...

Database:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

Posted: **Thu 19 Apr 2012 12:48**

Thank you for your test project. We have reproduced the issue, but this is the Oracle Client bug. As a workaround you can use one of the following variants:
1. Use the Direct mode. In this case note that the support for OBJECT data types in the Direct mode has been added in dotConnect for Oracle 6.70.293;
For more information, please refer to http://www.devart.com/dotconnect/oracle ... story.html
2. Specify explicitly the column size in bytes:

Code: Select all

CREATE OR REPLACE TYPE SCOTT.TAddress1 AS OBJECT (
  Country VARCHAR2(30 byte),
  City VARCHAR2(30 byte),
  Street VARCHAR2(30 byte),
  Apartment NUMBER
);

Posted: **Wed 02 May 2012 11:55**

Thank you, Pinturiccio.

Workaround 2 works, but we have to recreate all object types. And to make sure that the complete data fits into the object, we have to define each VARCHAR2-field with Bytes = 4*Chars.
Workaround 1 works with Version 6.80 (with Version 5.70 we get ORA-03115 unsupported network datatype or representation).

Do you know if this is fixed in Oracle Client 11.2 (currently we are using 11.1)?
Is Oracle aware of or working on this bug? How to submit this bug to Oracle?

Regards,
Matthias

Posted: **Mon 07 May 2012 14:28**

matthiasramp wrote:Do you know if this is fixed in Oracle Client 11.2 (currently we are using 11.1)?

The issue still persists in Oracle Client 11.2.

matthiasramp wrote:Is Oracle aware of or working on this bug?

We recommend you to contact the Oracle support team.

matthiasramp wrote:How to submit this bug to Oracle?

Try searching the support on Oracle site http://www.oracle.com/index.html

Posted: **Wed 10 Jun 2015 02:17**

Big thanks to this solution!

Devart Forums

ORA-21525 with ObjectType as Parameter and Unicode=true

ORA-21525 with ObjectType as Parameter and Unicode=true

Re: ORA-21525 with ObjectType as Parameter and Unicode=true

Re: ORA-21525 with ObjectType as Parameter and Unicode=true

Re: ORA-21525 with ObjectType as Parameter and Unicode=true