Error on selecting from table with type that contains ANYDATA

[jdorlon]

Hello,

If I create this type and table:

Code: Select all

drop table type_with_anydata purge;

CREATE OR REPLACE TYPE Cust_Address_Type_ANY
AS OBJECT
    ( street_address     VARCHAR2(40)
    , postal_code        VARCHAR2(10)
    , city               VARCHAR2(30)
    , state_province     VARCHAR2(10)
    , country_id         CHAR(2)
    , new_dev_column     CHAR(2)
    , something          sys.anydata
    )
/

create table type_with_anydata
(col1 number,
 col2 Cust_Address_Type_ANY);

Insert into TYPE_WITH_ANYDATA
   (COL1, COL2)
 Values
   (1, CUST_ADDRESS_TYPE_ANY('1',NULL,NULL,NULL,NULL,NULL,SYS.ANYDATA.convertVarchar2('This is some data')));
   
Insert into TYPE_WITH_ANYDATA
   (COL1, COL2)
 Values
   (2, CUST_ADDRESS_TYPE_ANY('1',NULL,NULL,NULL,NULL,NULL,SYS.ANYDATA.convertNumber(1234567890)));
   
Insert into TYPE_WITH_ANYDATA
   (COL1, COL2)
 Values
   (3, CUST_ADDRESS_TYPE_ANY('1',NULL,NULL,NULL,NULL,NULL,SYS.ANYDATA.convertDate(TO_DATE('01-JAN-2012','DD-MON-YYYY'))));
commit;

then, in a TSmartQuery tied to a TDBGrid...

Code: Select all

select t.rowid, t.* from type_with_anydata t

...I get this error when I scroll horizontally in the grid over to the ANYDATA field.
"Unknown data type .....\OraObjects.pas, line 2769

Can this be fixed in next version?

I am using Delphi Rad Studio 10.1 Berlin, Oracle 12.2 client and server, ODAC version 10.4.10

Thank you,
John Dorlon

[MaximG]

Thank you for the information. We have reproduced the issue and will investigate its origin. We will inform you about the results shortly.

[jdorlon]

Hey DevArt,

I just wanted to check the status of this. If it hasn't been fixed yet, can you fix it for the next release?

Thanks

John Dorlon

[jdorlon]

Hello,

A similar problem exists if the user-defined type contains SMALLINT.

Please let me know when both can be fixed.

Thank you

-John

[jdorlon]

Hello,

I just tested these bugs in the new version 11.1.3 and they still exist.

If there is either an ANYDATA or SMALLINT in the type, an error is thrown. I thought by a comment in the release notes that the ANYDATA bug was fixed but I guess that was something else.

Please take a look at these and fix for next version.

Thank you.

-John

[MaximG]

The given issue with Direct Mode was fixed in our components earilier, though it might occur in OCI Mode when the version of Oracle client doesn't match the server version; we cannot impact this behavior.
In our test environment, we've successfully tested our product using the matching Oracle client and server versions 12c, 18c, and 19c.

[jdorlon]

Hi Maxim,

Thank you for the reply. I am using OCI Mode.

I have found that if the Oracle client does not match the server, then Oracle gives an ORA-00001 (constriant violation), which is obviously wrong, and can be reproduced in SQL*Plus. I do not expect ODAC to solve this.

However, when client matches server, I still get an access violation. Did you try it with the unicode mode enabled? Sorry, I failed to mention that at the beginning, but I have unicode mode (both Session.Options.UseUnicode and OraCall.OCIUnicode) enabled.

Thanks

John Dorlon

[MaximG]

We haven't been able to reproduce the issue in our test environment using the matching versions of Oracle Server and Oracle Client with the UseUnicode option enabled.

[jdorlon]

Hello,

The problem occurs when the data is read from the COL2.SOMETHING column, in the 2nd or 3rd row. The error "Cannot convert type to OCI string" is given. I will email a sample project.

Thanks

John

[MaximG]

Thank you for the sample application. We've reproduced the issue in our environment using your application, and will let you know the result soon.