• Print |
• E-mail  |
• Bookmark

When you access an Oracle database from SAS^® in single-byte encoding and set the database characterset to UTF8, the 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 using SAS/ACCESS Interface to Oracle for CHAR and VARCHAR2 columns and reflects byte lengths based on the database characterset and not the client-side (SAS) encoding. For example, for a VARCHAR2(3 CHAR) column in an Oracle UTF8 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))

The following hot fix will enable you to get 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 characterset is a multi-byte chacaterset like UTF8 and 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 following Environment variable and LIBNAME 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 characterset is UTF8 and SAS encoding is single-byte, then set the environment variable to 3.

   unix_prompt> export DBSERVER_MAX_BYTES=3

2. Set the LIBNAME option, DBSERVER_MAX_BYTES. This solution will work only for that particular LIBNAME statement and WILL override the environment variable setting.

   For example, if the database characterset is UTF8 and SAS encoding is single-byte, then set the LIBNAME option to 3.

   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.

   For example:
   create table t1 (c1 varchar2(10 Byte), c2 varchar2(10 char)) --not good

2. Setting these options will not 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

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

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 :