Oracle Char column

2 posts, 0 answers
  1. Henri
    Henri avatar
    74 posts
    Member since:
    Aug 2008

    Posted 22 Jun 2009 Link to this post

    Given following table

     

    CREATE TABLE ASSEMBLYTYPE  
        (AT_CODE                        CHAR(1) NOT NULL,  
        AT_DESC                        VARCHAR2(10) NOT NULL 
    )  
    /  
    ALTER TABLE ASSEMBLYTYPE  
    ADD CONSTRAINT PK_ASSEMBLYTYPE PRIMARY KEY (AT_CODE)  
    USING INDEX 

    When trying to query,

    Test method TestProject1.Class1Test.TestNewMsg threw exception:  Telerik.OpenAccess.Exceptions.DataStoreException: Telerik.OpenAccess.RT.sql.SQLException: ORA-01722: invalid number

       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.next()
       at OpenAccessRuntime.Relational.conn.LoggingResultSet.next()
       at OpenAccessRuntime.Relational.fetch.FetchResultImp.rsNext()
       at OpenAccessRuntime.Relational.fetch.FetchResultImp.hasNext()  --->  Telerik.OpenAccess.RT.sql.SQLException: ORA-01722: invalid number

    Using this C# fragment

     

     

    Assemblytype atp = scope.Extent<Assemblytype>().Select(at => at).Where(at => at.AtCode == 'N').First();

     

     

     

    This happens because the generated sql is
    Telerik.OpenAccess Verbose: 71 : ado.Prepare c=9104556 t=13461890 SELECT * FROM ( SELECT "AT_CODE" COL1, "AT_CREATED_BY" COL2, "AT_CREATED_TS" COL3, "AT_DESC" COL4, "AT_MODIFIED_BY" COL5, "AT_MODIFIED_TS" COL6 FROM "ASSEMBLYTYPE" WHERE "AT_CODE" = 78 ) WHERE ROWNUM <= :TAKE 64688922

     

     

    The table contains, as at_codes 'N','O','A'....
    How can I fix this without modifying the table?
    Thanks.

  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 23 Jun 2009 Link to this post

    Hello Henri,

    I see the issue now. The reason is that the LINQ generated convert statements from char to int comparing via int ... something the MSSQL server allows implicitly, but for Oracle you need a filled table to get at least an error.
    The workaround is to use a query in the form:
     
     ... .Where(at => at.Id.Equals('N')).First();

    I will create a respective item for this.

    Thanks for reporting this issue,
    Thomas
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  3. DevCraft banner
Back to Top