Importing tables from Oracle database to SAS Visual Analytics

[Crysis85]

The ultimate goal is to import tables from an Oracle Database to a SAS application (Visual Analytics) on the same local network but different machine. Remote connection to the Oracle Database works (so the parameters are correct). SAS/ACCESS is installed and licensed on the SAS server. When I try to import data from Oracle in Visual Analytics as described in the manual i get the following error

ERROR: Could not load /sas/sashome/SASFoundation/9.4/sasexe/sasora (65 images loaded)ERROR: libclntsh.so.11.1: cannot open shared object file: No such file or directory1          
2          
/* ************************************************* */
/* Assign the libref for the data in a Oracle table */
/*
2        ! ************************************************* */

LIBNAME db_ora ORACLE PRESERVE_COL_NAMES=YES2        ! PRESERVE_TAB_NAMES=YES  PATH="192.168.1.16:1521/orcl" SCHEMA="hs" USER=system PASSWORD="{sas002}F3E0253E3F9DE76035AD5101"
2        !  ;

ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded. ERROR: Image SASORA   found but not loadable.. 
Please make sure Oracle environment is set correctly. 
Look in the install/Config doc for additional info for your platform. 
Other possible reasons - incomplete Oracle client install, 32/64-bit mismatch between Oracle client & SAS, incorrect Oracle client 
       version(Oracle client must match the version picked during post-install process), incompatible sasora for your OS or its 
       attribs don't permit SAS to load it.ERROR: Error in the LIBNAME statement.
3          options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;

From what I understand googling around the SAS machine must have an oracle client correctly configured. How do I check if everything is ok? How do I locate the oracle client on the SAS server (if it's even installed)? Any kind of help would be tremendously appreciated. Thanks 

[LinusH]

Just follow the steps in the installation and configuration guide.

[Crysis85]

there is no mention neither of Oracle client nor SAS/ACCESS and how it works.

[Crysis85]

So, tanking this one step of the time.

ERROR: Could not load /sas/sashome/SASFoundation/9.4/sasexe/sasora (65 images loaded)ERROR: libclntsh.so.11.1: cannot open shared object file: No such file or directory

Searching in the machine this file is located in

/sas/database/stage/ext/lib

and in

/home/oracle/app/oracle/product/11.2.0/dbhome_1/lib/

/home/oracle/app/oracle/pruduct/11.2.0/dbhome_1/inventory/Scripts/ext/lib

i supposed the revelant one was the first so i set the environment variable for sasinst user (owner of the sas installation)

LD_LIBRARY_PATH to that location.

restarted the sas server (should i have to do that?) and still the same error appear "no such file or directory" for that library file.

Since I'm not the one who installed this machine, what can i do to check that the Oracle client here was installed correcly and it's working? 

[LinusH]

https://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/66380/PDF/default/config.pdf

[Crysis85]

Thanks, went trought all the "Configure SAS/ACCESS Interface to Oracle" but still nothing works

I still can't figure out if an Oracle client is installed on this machine.

There is what seems to be the partial installation of an oracle database SERVER located in /home/app/oracle/product/11.2.0/dbhome_1 but it's a mess. Not even the ORACLE_HOME env. variable is set (I did set it myself following this page http://support.sas.com/documentation/cdl/en/bidsag/68193/HTML/default/viewer.htm#p1w3v98qca3sfzn1rzt...

I'd like to install an oracle client from scratch by myself but the s.o of the machine hosting the sas server is RedHat6 (no subscription) and I can't install any software on it.

Do you have any suggestion?

[LinusH]

You need to install something on your SAS server. I can't really value your system constraints.

[Crysis85]

Hello,

I still haven't solved this problem.

I managed to deploy the sas installation on a virtual machine with CentOS, so I could configure the oracle client properly.

Tested it with SQLPLUS on the same sas machine and it works like a charm.

Still when I try to import data using SAS/ACCESS i get the error

 Linux LIN X64 3.10.0-327.18.2.el7.x86_64 #1 SMP Thu May 12 11:03:55 UTC 2016 x86_64 CentOS Linux release 7.2.1511 (Core)  

You are running SAS 9. Some SAS 8 files will be automatically converted 
by the V9 engine; others are incompatible.  Please see 
http://support.sas.com/rnd/migration/planning/platform/64bit.html

PROC MIGRATE will preserve current SAS file attributes and is 
recommended for converting all your SAS libraries from any 
SAS 8 release to SAS 9.  For details and examples, please see
http://support.sas.com/rnd/migration/index.html


This message is contained in the SAS news file, and is presented upon
initialization.  Edit the file "news" in the "misc/base" directory to
display site-specific news and information in the program log.
The command line option "-nonews" will prevent this display.




NOTE: SAS Initialization used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
NOTE: The autoexec file, /sas/config/Lev1/SASApp/WorkspaceServer/autoexec.sas, was executed at server initialization.
1          
2                                                          The SAS System                                 11:24 Monday, May 30, 2016


1                                                          The SAS System                                 11:24 Monday, May 30, 2016

ERROR: Could not load /sas/sashome/SASFoundation/9.4/sasexe/sasora (63 images loaded)
ERROR: libclntsh.so.11.1: cannot open shared object file: No such file or directory
1          
2          
/* ************************************************* */
/* Assign the libref for the data in a Oracle table */
/*
2        ! ************************************************* */


LIBNAME db_ora ORACLE PRESERVE_COL_NAMES=YES
2        ! PRESERVE_TAB_NAMES=YES  PATH="oracle_test" USER=system PASSWORD="{sas002}F3E0253E3F9DE76035AD5101" ;


ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded. ERROR: Image SASORA   found but not loadable.. 
Please make sure Oracle environment is set correctly. 
Look in the install/Config doc for additional info for your platform. 
Other possible reasons - incomplete Oracle client install, 32/64-bit mismatch between Oracle client & SAS, incorrect Oracle client 
       version(Oracle client must match the version picked during post-install process), incompatible sasora for your OS or its 
       attribs don't permit SAS to load it.
ERROR: Error in the LIBNAME statement.
3          options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;

[LinusH]

If your is to prepare for a production environment you should choose a supported platform. For Linux it means the enterprise editions of Red Hat or Suse.
http://support.sas.com/kb/43/233.html

[Crysis85]

It's not a production environment, is for training purposes.

Still, anyone has any idea on how to solve this problem?

[TriciaAanderud]

Did you see this support note - it looks like your error?

http://support.sas.com/kb/11/728.html

[Crysis85]

Thank you.

Yes I stumbled upon this page before, I checked all the environment variables and seem to be correct.

Plus, on the same system I connected to the Oracle database using sqlplus so I'm guessing the client installation, connection parameters and env. variables are set up correctly.

[TriciaAanderud]

Maybe you should try asking this question in the Admin/Install section. This
issues seems less about SAS VA and more about SAS 9.4/Oracle Access.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Crysis85 · Accepted solution]

Turns out the old adage "Have you tried turning it off and on again?" works sometimes.

Yesterday I corrected the environment variable LD_LIBRARY_PATH that pointed to a wrong folder but didn't restart the system. SQLPlus worked so I didn't worry about it.

Now that I did reboot, it works and i managed to import tables from a remote database.

Thanks for the help everyone.