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:
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
SAS System | SAS/ACCESS Interface to ODBC | Linux for x64 | V.03.05 | | Viya | |
*
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.
When reading data from Oracle using SAS/ACCESS® Interface to ODBC, the data might be displayed as black diamonds that contain a white question mark, or as what appear to be Asian characters.
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 |