DATA Step, Macro, Functions and more

oracle tables access with special chars

Reply
Contributor
Posts: 31

oracle tables access with special chars

hi Guys,
I have an issue while accessing tables with characters like $ in their names
for example, the below one works well with SAS datasets

proc contents data=abc.'au$co_auth$fc'n;
run;

proc sql;
select * from abc.'au$co_auth$fc'n;
quit;

But if abc refers to ORACLE libname, I get error as below

I now get the new error message reading: ERROR: The value 'AU$CO_AUTH$FC'n is not a valid SAS name.

Please help how I can access such datasets. Thanks.

Cheers
Mark

PROC Star
Posts: 326

Re: oracle tables access with special chars

[ Edited ]
Posted in reply to MarkNicholas

I think yoi need to havepreserve_tab_names=yes;  in your libname statement and then your logic should work or  please try something like this below

 

 

libname mydblib oracle user=testuser password=testpass
        preserve_tab_names=yes;
proc sql dquote=ansi;
   select * from mydblib."my table";

 

this was taken from

 

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001342346.htm

Contributor
Posts: 31

Re: oracle tables access with special chars

Posted in reply to MarkNicholas
The above did not work. This time we get error the table cannot be found. Pls help
PROC Star
Posts: 326

Re: oracle tables access with special chars

[ Edited ]
Posted in reply to MarkNicholas

first check whether you are able to access other tables in this particular libname. try out explicit pass through also. I would also like to know expert opinion of @LinusH on this topic

SAS Employee
Posts: 284

Re: oracle tables access with special chars

The PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES= LIBNAME options determine how SAS/ACCESS Interface to Oracle handles case sensitivity, spaces, and special characters. Please use both of them in LIBNAME. Also, I do not see that you have specified SCHEMA=. SCHEMA= specifies the name that is assigned to a logical classification of objects in a relational database. 

Super User
Posts: 5,437

Re: oracle tables access with special chars

Thanks @kiranv_. Not sure I can add to what have been already said.

But I can't stop wonder why people make it so complicated. Because you can, it doesn't mean it's a good idea to have table/column names with special/national/space chars etc. If you can't have the DBA rename things, perhaps creating views that comply with SAS naming standards is one way around it.

Assume one way of handling is not to preserve names, and then use _ (haven't tested though).

Data never sleeps
PROC Star
Posts: 1,760

Re: oracle tables access with special chars

Posted in reply to MarkNicholas

Another option is to use passthru:

proc sql;
  connect using ORALIB;
  select * from connection to ORALIB ( select * from CRAZYTABLENAME );
quit;

The stuff between the parentheses is oracle syntax.

 

Ask a Question
Discussion stats
  • 6 replies
  • 127 views
  • 2 likes
  • 5 in conversation