This content has been marked as final. Show 4 replies
What is the error you get?
What is the database character set and NLS_LENGTH_SEMANTICS on your Oracle system?
What is the character set of the data in SQL Server?
SELECT * FROM v$nls_parameters WHERE name LIKE '%CHARACTERSET'; SQL> SHOW PARAMETER nls_length_semantics;
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.
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
I m not sure of sql server side !!
What is the database character set and NLS_LENGTH_SEMANTICS on your Oracle system? charterset is UTF8 & NLS_length_semantics - BYTE
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?).