02-16-2015 10:34 AM
I'm having trouble reading Oracle tables into Enterprise Guide using SAS PC Files Server (installed locally). The ODBC driver I'm using is 'Oracle in OraClient11g_home1'.
The connection seem to be ok, there are no errors in the libname assign, I can see the tables, open them and query them so long as I don't ask SAS to read any of the text fields. The character fields I'm working with in Oracle tend to be VARCHAR2(100).
I've tried various flavours of formatting the field, specifying the option DBMAX_TEXT on the libname statement, but I can't seem to get it to work. It wouldn't be so bad if I was given an error message, but it just hangs - it’s like control never gets returned to EG, so there are no errors in the log for me to work with. My only way of exiting each time is to close EG from Task Manager. Incidentally, it also does this if I have any errors at all in my code, even when querying fields it can handle if my code is perfect.
Since EG can open the the tables and display columns that I want, I thought perhaps I would be able to use the EG Query Builder, and EG would apply whatever options it needed to query the table (as it can obviously open it) but, again, it just hung at 'validating query'.
I'm not sure if I should be looking to change options on the ODBC driver, in EG... or maybe both, but I feel like I've tried everything!
Does anyone have any ideas?
02-18-2015 11:09 AM
I'm using the exact same method and drivers without any issues.
Have your tried just extracting one specific record to see if that works ?
Also if your tablesnames and column names use 'strange' characters you might experience issues. I've added the below two statements to my libname command.
LIBNAME ORACLE ODBC DSN="Oracle64" Password=xxxxxx schema=xxxx PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES= YES;
From there your query can be as simple as
Select * from ORACLE.tablename;
02-19-2015 10:03 AM
Thanks for your reply, but I don't think that is the same method. I think from the look of your libname you're able to use SAS/ACCESS interface to Oracle, whereas I can't do that. I'm going via PC Files Server, so my libname goes like this:
Libname <libref> PCFILES server="<ip address of the machine PC Files Server is running on>"
Serveruser="<Username of the account PC Files Server is running on>"
Serverpass="<Password of the account PC Files Server is running on>"
schema = <schema> ;