SUPPORT / SAMPLES & SAS NOTES
 

Support

Problem Note 69895: Invalid data is read from VARCHAR2(xxx CHAR) columns in Oracle when you use SAS/ACCESS® Interface to ODBC

DetailsHotfixAboutRate It

Some data might not be displayed correctly when you read data from Oracle using SAS/ACCESS Interface to ODBC. This issue occurs when the Oracle columns being read are defined as VARCHAR2(nn CHAR) where nn is the length of the column and either of the following conditions are true:

  • You use the SQL procedure to execute explicit SQL. Here is example code: SELECT * FROM CONNECTION TO ODBC (explicit-sql) ;
  • You use PROC SQL code that triggers implicit pass-through. Here is example code: SELECT DISTINCT column-list FROM odbc-library.oracle-table-name

The problem has not been observed when running SAS® Viya® 3.5 on Microsoft Windows or with SAS/ACCESS® Interface to Oracle.

Workarounds

There are several workarounds:

  • For all examples, redefine your VARCHAR2(nn CHAR) columns to VARCHAR2(nn BYTE); however, this workaround is not likely to be practical for most sites, and nn might need to be a larger value.
  • For implicit SQL, add NOIPASSTHRU to the PROC SQL statement; however, this workaround might result in a reduction in performance:
PROC SQL NOIPASSTHRU ;
  • For explicit SQL, change all the selected VARCHAR2(nn CHAR) columns to use the Oracle CONVERT function:
convert(column-name,'AL32UTF8','UTF8')

Click the Hot Fix tab in this note for a link to instructions about accessing and applying the software update.



Operating System and Release Information

Product FamilyProductSystemProduct ReleaseSAS Release
ReportedFixed*ReportedFixed*
SAS SystemSAS/ACCESS Interface to ODBCLinux for x64V.03.05Viya
* 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.