"Naming Conventions" error when trying to select data from oracle database

[John_Wick]

Hello, experts!

We have two SAS clusters that are deployed on different servers. Let it be SERVER 1 and SERVER 2.

We have a problem with selecting data from oracle database table on SEVER 1. Part of log with query and error looks like this:

------------------SERVER 1 LOG---------------------------

LIBNAME DWH_R ORACLE
PRESERVE_COL_NAMES=YES

PRESERVE_TAB_NAMES=YES
DBMAX_TEXT=32767
READBUFF=7500 INSERTBUFF=7500
PATH=***
SCHEMA=***
USER=***

PASSWORD=***

NOTE: Libref DWH_R was successfully assigned as follows:

Engine: ORACLE
Physical Name: ***

options validvarname=any;
options sastrace=',,,ds' sastraceloc=saslog nostsuffix
sql_ip_trace=(note, source) msglevel=i fullstimer;

proc sql dquote=ansi;
select * from dwh_r.'S0208$KBS_CHAT_BOT_SUGGEST'n;

ORACLE_4: Prepared: on connection 21
SELECT * FROM "DWH_STAGE2"."S0208$KBS_CHAT BOT_SUGGEST"

Summary Statistics for ORACLE are:
Total SQL prepare seconds were: 0.000822
Total seconds used by the ORACLE ACCESS engine were 0.002057

ERROR: This DBMS table or view cannot be accessed by the SAS System because it contains column names that are
normalized (uppercased) compare is performed. See "Naming Conventions" in the SAS/ACCESS documentation.
not unique

------------------SERVER 1 LOG---------------------------

We think, that the problem is due to certain long names of table fields, which after being cut off (SAS accepts field names of no more than 30 characters) become the same like this two fields with long labels:

But in SAS, there is a special VALIDVARNAME option, which in any case will change the name to a unique one by adding an index to the end of the name, as shown in "Name" field in the picture above. This is what happens on our SERVER 2 (part of log with query):

------------------SERVER 2 LOG---------------------------

LIBNAME DWH_R ORACLE
PRESERVE_COL_NAMES=YES

PRESERVE_TAB_NAMES=YES
DBMAX_TEXT=32767
READBUFF=7500 INSERTBUFF=7500
PATH=***
SCHEMA=***
USER=***

PASSWORD=***

NOTE: Libref DWH_R was successfully assigned as follows:

Engine: ORACLE
Physical Name: ***

options validvarname=any;
options sastrace=',,,ds' sastraceloc=saslog nostsuffix
sql_ip_trace=(note, source) msglevel=i fullstimer;

proc sql dquote=ansi outobs=10;
select * from dwh_r.'S0208$KBS_CHAT_BOT_SUGGEST'n;

ORACLE_5: Prepared: on connection 6
SELECT * FROM "DWH_R"."S0208$KBS_CHAT BOT_SUGGEST"

SQL_IP_TRACE: None of the SQL was directly passed to the DBMS.

ORACLE_6: Executed: on connection 6

SELECT statement  ORACLE_5

WARNING: Statement terminated early due to OUTOBS=10 option.

Summary Statistics for ORACLE are:
Total row fetch seconds were: 0.336985
Total SQL execution seconds were: 0.031686
Total SQL prepare seconds were: 0.001000
Total seconds used by the ORACLE ACCESS engine were 1.934726

NOTE: Procedure SQL used (Total process time):

------------------SERVER 2 LOG---------------------------

What points have been checked already?

• different combination of validvarname and validmemname options (this did not help)
• output comparison of proc options command on both workspace server's (this did not help)
• Attempt to reproduce the error on SERVER 2 (this step was unsuccessful)

Both servers use SAS 9.4: 

• SERVER 1 - 9.4 (TS1M3)
• SERVER 2 - 9.4 (TS1M7)

There is a feeling that SAS on SERVER 1 does not respond to the VALIDVARNAME option. Can version of SAS affect this?

We also found SAS note for this error - 16835 - An error occurs when DBMS tables do not have unique column names and you try to reference a .... It suggests creating a VIEW of this table, but we want to avoid this, because on SERVER 2, select to the table works perfectly without creating a VIEW. 

I will be grateful for any useful information and any thoughts on this matter!

[Patrick]

Adding a count to make variable names unique is as documented here both for validvarname V7 and ANY.

The SAS Note you referenced indicates that this behaviour exists since release 9.2 TS1M0.

There are of course differences between maintenance releases but even the older release on server 1 is way past SAS9.2. But may-be this exact 9.4M3 release got this issue again.

Creating a view is what I would be doing always in such cases because it provides full control over the names on the SAS side.

If you don't want to create such a view (or even better ask and Oracle DBA to create a view for you on the Oracle side) then you could also contact SAS Tech Support. May be this issue is already known for SAS9.4M3 and there is a patch for it. 

Be aware that SAS9.4 M3 has been released in July 2015 which means you should really upgrade not the least because this version is now even approaching the end of the grace period for Standard Support (Support Levels for SAS® 9.4 & Earlier Releases).