Problem Note 62565: You receive "ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis. . ." after you use PROC SQL to join a SAS table to an Oracle table
 |  |  |  |  |
In SAS/ACCESS® Interface to Oracle, PROC SQL might generate an error that is similar to the following:
ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis. SQL
statement: SELECT "Oracle-date" FROM Oracle-table WHERE ( ("Oracle-date" IN
(TO_DATE('25MAY2018:00:00:00','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American')
,(TO_DATE('26MAY2018:00:00:00','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American')
,(TO_DATE('27MAY2018:00:00:00','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American')
... repeated for each date in the SAS table ...
,(TO_DATE('29MAY2018:00:00:00','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American')
,(TO_DATE('18FEB2021:00:00:00','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American')
OR ("Oracle-date" IN ( ) ) ).
ORACLE: *-*-*-*-*-*-* ROLLBACK *-*-*-*-*-*-*
statement: SELECT "Oracle-date" FROM Oracle-table WHERE ( ("Oracle-date" IN
(TO_DATE('25MAY2018:00:00:00','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American')
,(TO_DATE('26MAY2018:00:00:00','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American')
,(TO_DATE('27MAY2018:00:00:00','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American')
... repeated for each date in the SAS table ...
,(TO_DATE('29MAY2018:00:00:00','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American')
,(TO_DATE('18FEB2021:00:00:00','DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American')
OR ("Oracle-date" IN ( ) ) ).
ORACLE: *-*-*-*-*-*-* ROLLBACK *-*-*-*-*-*-*
The error can occur when all of the following conditions are true:
- You use the Oracle LIBNAME option MULTI_DATASRC_OPT=IN_CLAUSE.
- You use PROC SQL to join a SAS table to an Oracle table.
- The join criteria match a SAS column to an Oracle DATE column.
- The SAS table has more than 1,000 rows.
The following example code can create the error:
libname dbms oracle path=Oracle-path user=user-id password=password multi_datasrc_opt=in_clause ;
proc sql ;
select SAS-column
,Oracle-date
from SAS-table
inner join dbms.Oracle-table on SAS-table.SAS-column = Oracle-table.Oracle-date ;
quit ;
proc sql ;
select SAS-column
,Oracle-date
from SAS-table
inner join dbms.Oracle-table on SAS-table.SAS-column = Oracle-table.Oracle-date ;
quit ;
You can work around this issue by removing MULTI_DATASRC_OPT=IN_CLAUSE (highlighted above) from the LIBNAME statement to Oracle.
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 | Base SAS | z/OS | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 |
| z/OS 64-bit | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft® Windows® for x64 | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft Windows 8 Enterprise x64 | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft Windows 8 Pro x64 | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Enterprise x64 | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Pro 32-bit | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Pro x64 | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft Windows 10 | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2008 R2 | 9.4 | 9.4 TS1M0 | ||||
| Microsoft Windows Server 2008 for x64 | 9.4 | 9.4 TS1M0 | ||||
| Microsoft Windows Server 2012 Datacenter | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2012 R2 Datacenter | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2012 R2 Std | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2012 Std | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Windows 7 Enterprise x64 | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Windows 7 Professional x64 | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| 64-bit Enabled AIX | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| 64-bit Enabled Solaris | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| HP-UX IPF | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Linux for x64 | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
| Solaris for x64 | 9.4 | 9.4_M6 | 9.4 TS1M0 | 9.4 TS1M6 | ||
A fix for this issue for Base SAS 9.4_M5 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/B6Q.html#62565A fix for this issue for Base SAS 9.4_M4 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/A3Z.html#62565
When you use join criteria that match an Oracle DATE column to a SAS DATETIME column, an error might occur when the SAS table has more than 1,000 rows.
| Type: | Problem Note |
| Priority: | high |
| Topic: | Common Programming Tasks ==> Combining Data Data Management ==> Data Sources ==> External Databases ==> Oracle SAS Reference ==> LIBNAME Engines |
| Date Modified: | 2019-01-03 09:17:03 |
| Date Created: | 2018-07-03 03:25:32 |