Problem Note 63971: An SQL query returns no data rows when you use SAS/ACCESS® Interface to Oracle
 |  |  |  |  |
When you run SAS® 9.4M6 (TS1M6) in AIX, Solaris, or HP-UX operating environments and connect to Oracle, an SQL query returns no rows when data is expected.
For example, when you run the following code, which should return one row, no data rows are returned:
libname ordb oracle user="user-ID" password="db-password" path="tns_name" ;
proc sql;
connect using ordb as oracle;
select * from connection to oracle
(select 123 as col1, 'Test' as col2 from dual);
disconnect from oracle;
quit;
connect using ordb as oracle;
select * from connection to oracle
(select 123 as col1, 'Test' as col2 from dual);
disconnect from oracle;
quit;
This issue occurs in SAS 9.4M6 in UNIX environments that have the Big Endian byte sequence. To determine whether you are using a Big Endian byte sequence, run the following code:
1? %put &SYSENDIAN;
BIG
BIG
To work around this issue, complete the following steps:
- Log on to the UNIX system as the SAS installer.
- Go to the !SASROOT directory.
- Use the following command to go to the sasexe subdirectory: cd sasexe
- Use the following command to remove the sasora file: rm -f sasora
- Create a new sasora link using the appropriate command for your environment:
- AIX: ln -s orr6411 sasora
- Solaris: ln -s ors6411 sasora
- HP-UX: ln -s orh6i11 sasora
- Restart the SAS session to apply the changes.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to Oracle | 64-bit Enabled AIX | 9.43 | 9.43 | 9.4 TS1M6 | 9.4 TS1M6 |
| 64-bit Enabled Solaris | 9.43 | 9.43 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| HP-UX IPF | 9.43 | 9.43 | 9.4 TS1M6 | 9.4 TS1M6 | ||
A fix for this issue for SAS/ACCESS Interface to Oracle 9.43 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/E7L.html#63971A fix for this issue for SAS/ACCESS Interface to Oracle 9.42 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/C7I.html#63971
After you successfully connect to an Oracle database using a LIBNAME statement or the SQL procedure, you might not see data rows from an SQL query that you expected to return data rows. In this scenario, no error message is returned.
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2020-05-28 10:45:15 |
| Date Created: | 2019-04-03 14:06:14 |