topic Re: Importing tables from Oracle database to SAS Visual Analytics in SAS Visual Analytics

Importing tables from Oracle database to SAS Visual Analytics

Crysis85 — Tue, 03 May 2016 16:44:51 GMT

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

Re: Importing tables from Oracle database to SAS Visual Analytics

LinusH — Tue, 03 May 2016 20:38:57 GMT

Just follow the steps in the installation and configuration guide.

Re: Importing tables from Oracle database to SAS Visual Analytics

Crysis85 — Wed, 04 May 2016 09:34:53 GMT

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

Re: Importing tables from Oracle database to SAS Visual Analytics

Crysis85 — Wed, 04 May 2016 09:29:55 GMT

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?

Re: Importing tables from Oracle database to SAS Visual Analytics

LinusH — Wed, 04 May 2016 10:43:31 GMT

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

Re: Importing tables from Oracle database to SAS Visual Analytics

Crysis85 — Wed, 04 May 2016 15:18:11 GMT

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#p1w3v98qca3sfzn1rzty2tngrfyq.htm)

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?

Re: Importing tables from Oracle database to SAS Visual Analytics

LinusH — Wed, 04 May 2016 18:50:01 GMT

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

Re: Importing tables from Oracle database to SAS Visual Analytics

Crysis85 — Mon, 30 May 2016 09:25:24 GMT

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;

Re: Importing tables from Oracle database to SAS Visual Analytics

LinusH — Mon, 30 May 2016 17:51:43 GMT

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

Re: Importing tables from Oracle database to SAS Visual Analytics

Crysis85 — Tue, 31 May 2016 07:00:02 GMT

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

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

Re: Importing tables from Oracle database to SAS Visual Analytics

TriciaAanderud — Tue, 31 May 2016 09:50:39 GMT

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

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

Re: Importing tables from Oracle database to SAS Visual Analytics

Crysis85 — Tue, 31 May 2016 10:18:08 GMT

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.

Re: Importing tables from Oracle database to SAS Visual Analytics

TriciaAanderud — Tue, 31 May 2016 10:26:54 GMT

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

Re: Importing tables from Oracle database to SAS Visual Analytics

Crysis85 — Tue, 31 May 2016 11:08:31 GMT

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.