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;

There are some caveats to consider:

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

If you use the a CREATE statement similar to the following, there are some potential issues:

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

Product Family	Product	System	SAS Release
			Reported	Fixed*
SAS System	SAS/ACCESS Interface to Oracle	64-bit Enabled HP-UX	9.1 TS1M3	9.2 TS1M0
		64-bit Enabled Solaris	9.1 TS1M3	9.2 TS1M0
		Linux	9.1 TS1M3	9.2 TS1M0
		64-bit Enabled AIX	9.1 TS1M3	9.2 TS1M0
		Microsoft® Windows® for 64-Bit Itanium-based Systems	9.1 TS1M3	9.2 TS1M0
		Microsoft Windows Server 2003 Datacenter 64-bit Edition	9.1 TS1M3	9.2 TS1M0
		Microsoft Windows Server 2003 Enterprise 64-bit Edition	9.1 TS1M3	9.2 TS1M0
		Microsoft Windows XP 64-bit Edition	9.1 TS1M3	9.2 TS1M0
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3	9.2 TS1M0
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3	9.2 TS1M0
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3	9.2 TS1M0
		Microsoft Windows XP Professional	9.1 TS1M3	9.2 TS1M0
		Windows Vista	9.1 TS1M3	9.2 TS1M0
		Windows Vista for x64	9.1 TS1M3	9.2 TS1M0

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

---

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

http://www.sas.com/techsup/download/hotfix/82_sbcs_prod_list.html#020088

---

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

http://www.sas.com/techsup/download/hotfix/e9_sbcs_prod_list.html#020088

---

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

http://www.sas.com/techsup/download/hotfix/e9_dbcs_prod_list.html#020088

---

Type:	Problem Note
Priority:	high
Topic:	SAS Reference ==> LIBNAME Engines Data Management ==> Data Sources ==> External Databases ==> Oracle

Date Modified:	2009-07-08 10:38:41
Date Created:	2007-05-01 11:37:45