Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

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