Using the DATASETS procedure and the CONTENTS procedure against an Oracle library might return the following error:
ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended. SQL statement: SELECT OBJECT_NAME ,OBJECT_TYPE FROM USER_OBJECTS OBJ WHERE (OBJ.OBJECT_TYPE IN ('VIEW'))SELECT SYNONYM_NAME ,'SYNONYM' FROM ALL_OBJECTS OBJ,ALL_SYNONYMS SYN
WHERE (SYN.OWNER ='PUBLIC') AND (OBJ.OBJECT_TYPE IN ('TABLE','VIEW')) AND (SYN.TABLE_OWNER=OBJ.OWNER ) AND (SYN.TABLE_NAME=OBJ.OBJECT_NAME ).
This issue occurs when you assign the Oracle library with the DB_OBJECTS option, and the option value contains PUBLIC_SYNONYMS together with another value (like VIEWS or TABLES):
Specifically, this problem occurs because of an incorrect SQL query that the SAS/ACCESS Interface to Oracle engine generates.
To circumvent this issue, use a concatenated library:
libname synonyms oracle path='MyOracleDB' user=scott pwd=tiger DB_OBJECTS= (PUBLIC_SYNONYMS);
libname tab_view oracle path='MyOracleDB' user=scott pwd=tiger DB_OBJECTS= (TABLES VIEWS);
libname ALL (synonyms tab_view);
proc datasets lib=ALL; quit;