Oracle backend always treats db-generated primary key as Int64

[Greg]

I have a table with a primary key that is derived from sys_guid() via a default value, the column type is varchar(32). In the model schema this column is marked true for IsPrimaryKey, IsBackendCalculated, HasDefaultValue. In the model Identity=true, and the table identity mechanism is DatabaseServerCalculated. When I insert into the table I get an ORA-01722: invalid number. I think the root of the problem is the generated command, that looks something like this:
INSERT INTO ... VALUES ... RETURNING "ID_COLUMN" INTO :AutoIncValue
and the Oracle backend (OracleFactory.PrepareAutoinc) sets the type of the AutoIncValue parameter on the command to Int64 regardless of the type of the ID_COLUMN. The same schema/object model settings work flawlessly with MSSQL.

I tried using raw(16) and varchar(32) column types, the results are the same. Is there any viable workaround here? I need to store 16 bytes of relevant info, that won't fit into an 8 byte int64.

[Doroteya]

Hello Greg,

Thank you for the provided information. I managed to reproduce the error you experience and was able to define a workaround for it.

Generally, the insertion of a database default GUID value in a primary key column in Oracle is a limitation on our side.

The alternative I would suggest to you is to use the GUID identity mechanism offered by Telerik OpenAccess ORM. This feature generates the GUID value for the primary key column of a given table instead of both the developer and the server. You can find more information about it in this documentation article.

The implementation of the suggested workaround would require a small change in the design of the primary key column - its type should be varchar2 and the length should be extended with 8 symbols:

<primary_column_name> VARCHAR2(40) DEFAULT 'SYS_GUID() ' NOT NULL

If that is feasible to you, the solution includes the following steps:
1) Update the design of the column
2) Update the class in the model using the Update from Database wizard
3) Select the class in Visual Designer and set the Identity Mechanism property to Guid
4) Select the identity property of the class in Visual Designer and change its type from String to Guid
5) Save the .rlinq file

If you continue to experience this behaviour after applying the suggested solution, it would be very useful to provide us with a sample project that demonstrates it. 

I am looking forward to your feedback.


Regards,
Doroteya
Telerik