Why does DG4ODBC report the wrong length for my SQL Server character columns?

If your Oracle database character set is UTF-8 (UTF8 or AL32UTF8), some versions of DG4ODBC triple the length when describing character columns and pad character values with blanks. DG4ODBC does this because the UTF-8 character set may require up to three bytes to encode a character. The column length that DG4ODBC reports is based on the maximum number of bytes that may be required to store a column value rather than the maximum number of characters a column value may contain.

To illustrate this issue, we created the following table in SQL Server:

CREATE TABLE
    test_table (id_col INT identity, char_col CHAR(20))
INSERT INTO
    test_table
VALUES
    ('ABC')

Although the specified length for char_col was 20, the length that DG4ODBC reports for the column is three times this value:

SQL> SELECT LENGTH ("char_col") FROM dbo.test_table@dg4odbc_link; LENGTH("char_col") ------------------ 60

(The SQL Server ODBC driver reports the correct column length, as the following unixODBC Driver Manager log extract demonstrates.)

[ODBC][11699][1306405741.320643][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [char_col]
Data Type = 0x7fff56ceaf48 -> 1
Column Size = 0x7fff56ceaee0 -> 20

Running the following query returns a blank-padded value: ABC, which is the value stored in the column, plus 57 blank characters, a total of 60 characters.

SQL> SELECT "char_col" FROM dbo.test_table@dg4odbc_link;

char_col
------------------------------------------------------------
ABC

You can change this behaviour by configuring DG4ODBC to be character orientated rather than byte orientated when reporting character column lengths. To do this:

  1. Add these lines to your initsid.ora file: 
    HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
HS_NLS_LENGTH_SEMANTICS = CHAR
  2. Stop and start the Oracle Listener.

Further information