4 Replies Latest reply: Oct 25, 2010 1:54 PM by 645300 RSS

    Char difference in ORACLE And SQL SERVER

    645300
      Hello gurus,

      I was trying to querying data from Oracle into sql server through linked server, but throws me an error!! length mis match error!!I know there is CHAR data type in sql server and as well as in oracle.

      But when i use CAST FUNCTION with CHAR it works fine
      -- error code 
      
        select * from openquery( linkoracle11 
                 select  col1, col2, col3 from test_table )
      
      
      -- say col3 char(1)   -- data type
      ---

      -- working code 
      
        select * from openquery ( linkoracle 11 
                 select  col1, col2, cast (col3 as char(1)) as col_3 from test_table )
      I was wondering, whats the difference between oracle and sql server for CHAR datatype ?

      Any idea gurus ?

      Thanks,
        • 1. Re: Char difference in ORACLE And SQL SERVER
          JustinCave
          What is the error you get?

          What is the database character set and NLS_LENGTH_SEMANTICS on your Oracle system?
          SELECT *
            FROM v$nls_parameters
           WHERE name LIKE '%CHARACTERSET';
          
          SQL> SHOW PARAMETER nls_length_semantics;
          What is the character set of the data in SQL Server?

          If your local database is using a variable-length character set (like UTF8) and NLS_LENGTH_SEMANTICS is set to BYTE (the default), a CHAR(1) allocates 1 byte of storage which may not be enough for a single character. The receiving application may need to allocate a buffer with 3 times as many bytes as there are characters in order to ensure that it will be able to handle the result. I'm not sure why adding an implicit CAST would change that, but since we're multiple levels removed from the code that figures out how large a buffer to allocate, that's not hugely surprising.

          Justin
          • 2. Re: Char difference in ORACLE And SQL SERVER
            645300
            What is the error you get?
            
            
            Returned an unexpected data length for the fixed-length column. The expected data length is 4, while the returned data length is 1
            What is the database character set and NLS_LENGTH_SEMANTICS on your Oracle system?
            
            charterset is UTF8 & NLS_length_semantics - BYTE
            I m not sure of sql server side !!
            • 3. Re: Char difference in ORACLE And SQL SERVER
              JustinCave
              The character set is UTF8? Or AL32UTF8?

              If the character set is AL32UTF8, then it would seem likely that it has something to do with variable length character sets. A single character in the AL32UTF8 character set can require up to 4 bytes of storage so the client will often allocate 4-bytes of storage for every character just in case. There are various OCI calls that the linked server implementation might be making that would tell the server that a CHAR(1) could require up to 4 bytes of storage. If the linked server implementation is unaware that this is an upper bound, that could cause the error you listed (is there no error number?).

              Justin
              • 4. Re: Char difference in ORACLE And SQL SERVER
                645300
                Justin,

                You are right it is "AL32UTF8" I think it something to do with variable lenght! So we can work around by using Varchar2 with cast function, but i m not sure how translates from link server to oracle !!


                Anyways thank you so much for your suggestions!!