When you access an Oracle database from SAS^® in single-byte encoding and set the database character set to UTF-8, SAS/ACCESS Interface to Oracle might return incorrect byte lengths for character columns that have lengths defined in CHAR semantics (lengths are usually much larger than needed).

This problem occurs when you use SAS/ACCESS Interface to Oracle for CHAR and VARCHAR2 columns. The problem reflects byte lengths based on the database character set and not the client-side (SAS) encoding. For example, for a VARCHAR2(3 CHAR) column in an Oracle UTF-8 database, the byte length surfaced in SAS is tripled (9). So all the CHAR and VARCHAR2 column lengths are tripled in SAS even though the client-side encoding is single byte.

There are two ways to work around this issue:

1. Create tables with CHAR and VARCHAR2 datatype columns by specifying the length with byte semantics.

   For example: CREATE TABLE T1 (COL1 VARCHAR2(3 BYTE)).

2. For an existing table, use ALTER TABLE to change the column length to byte semantics.

   For example: ALTER TABLE T1 MODIFY (COL1 VARCHAR2(3 BYTE))

Select the Hot Fix tab in this note to access the hot fix for this issue.

This hot fix enables you to obtain the correct byte lengths even when the length of a CHAR or VARCHAR2 column is specified in CHAR semantics.

***ONLY apply this hot fix if your Oracle database character set is a multiple-byte character set such as UTF-8 and if the length of the character columns of the tables in this database are specified with CHAR semantics.

After you apply the hot fix, you must use the DBSERVER_MAX_BYTES= environment variable and LIBNAME statement option to get the correct byte lengths for CHAR and VARCHAR2 columns that are defined with CHAR semantics.

1. Set the environment variable, DBSERVER_MAX_BYTES. This solution will work for the duration of the SAS session:

   For example, if the database character set is UTF-8 and the SAS encoding is single byte, then set the environment variable to 3.

   unix_prompt> export dbserver_max_bytes=3
2. Set the DBSERVER_MAX_BYTES= option in a LIBNAME statement. This solution only works for that particular LIBNAME statement, and it WILL override the environment variable setting.

   For example, if the database character set is UTF-8 and the SAS encoding is single byte, then set the LIBNAME option to 3, as shown in the following example:

   libname x oracle user=scott pw=tiger dbserver_max_bytes=3;

1. This solution WILL fail in a database that has tables that were created with "mixed" semantics! It is recommended that you use the NLS_LENGTH_SEMANTICS parameter instead of explicitly providing the value at CREATE TABLE time, as shown in the following example:
   create table t1 (c1 varchar2(10 Byte), c2 varchar2(10 char)) /*not good */
2. Setting these options does affect the NCHAR and NVARCHAR2 byte lengths.
3. The values that are provided with these options will not be validated.

   For example: DBSERVER_MAX_BYTES=3000 -- will not report an error

This statement might not be favorable because C1 and C2 will be assigned the variable length with incorrect values, which could cause variable truncation or wasted resources.

Operating System and Release Information

A fix for Release 8.2 (TS2M0) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at :