Oracle backend always treats db-generated primary key as Int64

2 posts, 0 answers
  1. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 06 Aug 2013 Link to this post

    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.
  2. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 09 Aug 2013 Link to this post

    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
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
  3. DevCraft banner
Back to Top