Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Importing tables from Oracle database to SAS Visual Analytics

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Importing tables from Oracle database to SAS Visual Analytics

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 


Accepted Solutions
Solution
‎05-31-2016 07:09 AM
Contributor
Posts: 33

Re: Importing tables from Oracle database to SAS Visual Analytics

Posted in reply to TriciaAanderud

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.

View solution in original post


All Replies
Super User
Posts: 5,431

Re: Importing tables from Oracle database to SAS Visual Analytics

Just follow the steps in the installation and configuration guide.
Data never sleeps
Contributor
Posts: 33

Re: Importing tables from Oracle database to SAS Visual Analytics

[ Edited ]

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

Contributor
Posts: 33

Re: Importing tables from Oracle database to SAS Visual Analytics

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? 

Super User
Posts: 5,431

Re: Importing tables from Oracle database to SAS Visual Analytics

Contributor
Posts: 33

Re: Importing tables from Oracle database to SAS Visual Analytics

[ Edited ]

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?

Super User
Posts: 5,431

Re: Importing tables from Oracle database to SAS Visual Analytics

You need to install something on your SAS server. I can't really value your system constraints.
Data never sleeps
Contributor
Posts: 33

Re: Importing tables from Oracle database to SAS Visual Analytics

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;
Super User
Posts: 5,431

Re: Importing tables from Oracle database to SAS Visual Analytics

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
Data never sleeps
Contributor
Posts: 33

Re: Importing tables from Oracle database to SAS Visual Analytics

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

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

Frequent Contributor
Posts: 123

Re: Importing tables from Oracle database to SAS Visual Analytics

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

 

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


Tricia Aanderud
Zencos Consulting
Twitter: @taanderud - Follow me!

Contributor
Posts: 33

Re: Importing tables from Oracle database to SAS Visual Analytics

Posted in reply to TriciaAanderud

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.

Frequent Contributor
Posts: 123

Re: Importing tables from Oracle database to SAS Visual Analytics

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. -##

Tricia Aanderud
Zencos Consulting
Twitter: @taanderud - Follow me!

Solution
‎05-31-2016 07:09 AM
Contributor
Posts: 33

Re: Importing tables from Oracle database to SAS Visual Analytics

Posted in reply to TriciaAanderud

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 871 views
  • 1 like
  • 3 in conversation