Oracle Libname assignment Issue

[KishoreKaushal]

Hi All,

We have recently set up the SAS 92 EBI Environment wherein we are having 1 Windows Server Catering to DI Server,2nd  Windows server to EBI Server and  the 3rd Server to Web Aplication Server i.e.JBOSS.All the  Servers are  Windows Server 2008 R2 -Enterprise Edition(64-bit).

Before setting up the Oracle Library through SMC or DI Studio tested the Oracle Libname statement through PCSAS installed on the DI Server but getting the error mentioned below .We have got the Oracle client 10g (10.2.0)  Installed on the DI Server and able to connect the Oracle DataBase through Command Prompt on Windows Server in DI Server.

We are getting the below Error Logs:

/**************************

libname oralib ORACLE User="sastest" Password=XXXXXXXXX Path="@testdb";

ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded. ERROR: Image SASORA  found but not  loadable.

ERROR: Error in the LIBNAME statement.

**************/

In the environment variables we have set the below variables as well:

LD_LIBRARY_PATH=C:\oracle\product\10.2.0\client_1\lib

ORACLE_HOME=C:\oracle\product\10.2.0\client_1

TNS_ADMIN=C:\oracle\product\10.2.0\client_1\network\ADMIN

Path=C:\oracle\product\10.2.0\client_1\bin;C:\oracle\product\10.2.0\client_1\network/Admin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT\System32\WindowsPowerShell\v1.0\;C:\ProgramFiles\SAS\SharedFiles(32)\Formats;C:\Program Files(x86)\Java\jre6\bin;C:SAP_RFC_LIB

In the Path Variable,we have specified the LD_LIBRARY_PATH abd TNS_ADMIN Path ;also we have provided the "Read and Execute " permission to the ORACLE_HOME folder.

Please let me know how to resolve the above issue.

Thanks in advance!

kaushal

[Technolero]

I had a similar issue and resolved it by adding my environment variables to my WorkspaceServer_usermods.sh startup script located at sas92config/Lev1/SASApp/WorkspaceServer (UNIX). 

[KishoreKaushal]

Thanks Technolero for the prompt response.But before making the change in the WorkspaceServer_usermods.bat script (as its a Windows Server 2008);would i need to restart any SAS Services also for making the changes into effect or the changes would reflect invariably and how to set the environment variables in it ?

[Technolero]

I believe that since we are not changing the server definition (just changing the startup script), that the updated script (batch file in your case) will execute with the included environment variables, because the standard workspace server opens a process with each new client request.  The object spawner should not need to be restarted.

[Ksharp]

First of all, Are you sure you can connect with ORACLE by using oracle client software?

In fact , SAS/ACCESS used oracle client to communicate with ORACLE.

So if you can connect, then copy the contain of PROFILE file(in oracle client) into file '/sas/SASFoundation/bin/sasenv_local' ,and test it again.

GoodLuck.

Ksharp

[jonam]

libname psdptgt1 oracle user='userid' password='password'

path='(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = Host100)(PORT = 1521)) )(CONNECT_DATA = (SERVICE_NAME = DB_ALIAS)))';

Not sure if this what you wanted. But please check this out.

I did connected from SAS EG to oracle to test the database connectivity.

I used the above method to connect to a new database and tested queries.

IF thisworks directly then you would be able to fix your batch file accordingly.

This is just an example.  you should modify the host, port(if not default), service_name.  user,password.

Once the above method works then you can verify and fix your parameters on batch file.

Le me know how it goes!!

[SASKiwi]

The usual starting point for this type of problem are the SAS support notes. I note you have set the read and execute permission that is one of the possible causes mentioned in SAS notes. I suggest, if you haven't already done so, work through the other causes documented in the SAS notes.

[KishoreKaushal · Accepted solution]

Hi All,

Thanks for the response.I was able to resolve the issue.It was basically the Oracle Version issue that was installed on the DI server.We were using Oracle version 10.2.0.3 initially which was incompatible with the Windows Server 2008 R2 Edition-64 bit.Because of it, the Oracle Drivers were not visible leading to no interaction between the SAS Application (64-bit) and the Oracle client installed as it was not able to find the drivers.

Issue Resoultion Step:Applied the Patchset on the Oracle Client installed ,thereby,upgrading it to 10.2.0.5 which was compatible with the Windows Server 2008 R2 version-64-bit.Once it was not we were able to check the Oracle drivers.

We reassigned the Oracle Libname statement and it was successfully connected.