Reading Oracle tables using PC Files Server

Reply
Occasional Contributor
Posts: 6

Reading Oracle tables using PC Files Server

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?

Many thanks!

Occasional Contributor
Posts: 13

Re: Reading Oracle tables using PC Files Server

Hi,

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

Proc SQL;

Select * from ORACLE.tablename;

Quit;

Occasional Contributor
Posts: 6

Re: Reading Oracle tables using PC Files Server

Hi Keith,

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>"         

                                       Port=<port>                    

                                       Dsn="<dsn>"                 

                                       Serveruser="<Username of the account PC Files Server is running on>"

                                       Serverpass="<Password of the account PC Files Server is running on>" 

                                       user='<Db username>'       

                                       password="<Db password>"         

                                       schema = <schema> ;

Ask a Question
Discussion stats
  • 2 replies
  • 186 views
  • 0 likes
  • 2 in conversation