ORA-21525 with ObjectType as Parameter and Unicode=true

ORA-21525 with ObjectType as Parameter and Unicode=true

Postby matthiasramp » 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
matthiasramp
 
Posts: 12
Joined: Fri 22 Jan 2010 15:39

Postby Pinturiccio » 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.
Pinturiccio
Devart Team
 
Posts: 1866
Joined: Wed 02 Nov 2011 09:44

Postby matthiasramp » 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
matthiasramp
 
Posts: 12
Joined: Fri 22 Jan 2010 15:39

Postby Pinturiccio » 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
);
Pinturiccio
Devart Team
 
Posts: 1866
Joined: Wed 02 Nov 2011 09:44

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

Postby matthiasramp » 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
matthiasramp
 
Posts: 12
Joined: Fri 22 Jan 2010 15:39

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

Postby Pinturiccio » 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
Pinturiccio
Devart Team
 
Posts: 1866
Joined: Wed 02 Nov 2011 09:44

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

Postby Jekk » Wed 10 Jun 2015 02:17

Big thanks to this solution!
Jekk
 
Posts: 1
Joined: Wed 10 Jun 2015 02:15


Return to dotConnect for Oracle