Problem Note 69895: Invalid data is read from VARCHAR2(xxx CHAR) columns in Oracle when you use SAS/ACCESS® Interface to ODBC
 |  |  |  |  |
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:
- For explicit SQL, change all the selected VARCHAR2(nn CHAR) columns to use the Oracle CONVERT function:
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 Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to ODBC | Linux for x64 | V.03.05 | Viya | ||
Viya on Linux: An update for this issue is available for SAS Viya 3.5. For instructions on how to access and apply software updates, see the Updating Your SAS Viya software section in the SAS Viya 3.5 for Linux Deployment Guide at
http://documentation.sas.com/?softwareId=administration&softwareVersion=3.5&softwareContextId=softwareUpdates| Type: | Problem Note |
| Priority: | high |
| Topic: | Data Management ==> Data Sources ==> External Databases ==> ODBC (Open Database Connectivity) Data Management ==> Data Sources ==> External Databases ==> Oracle SAS Reference ==> SQL |
| Date Modified: | 2023-03-13 15:08:01 |
| Date Created: | 2023-02-23 08:29:50 |