Administrator Privileges Needed

SAS 9.4 and ODBC

Administrator privileges needed

Start=>Control Panel=>System

Select ‘Advanced System settings’ from menu on LHS

Select ‘Environment Variables’ button

Under systems variables you should have:

Variable Value

TNS_ADMIN k:\ora_tns_admin

(save copy of old PATH value somewhere, just in case, before editing)

Install Oracle Client (instructions courtesy of Latha)

Login to the PC with Administrator privileges.

Run Full client install K:\ora112full\Win_X64\client\setup.exe

Few tips:

Choose Administrator install option

Choose Skip software updates option

Choose path where the Oracle client would be installed (eg: C:\app\Oracle)

Click on Install.

Let it go through the full cycle of installation until you get ‘Full installation is successful’ dialog box.

After installation of Oracle Client

PATH looks like this C:\app\KCLOHESSY\product\11.2.0\client_2\bin;C:\Windows\system32\UWA_OracleClient

But with your username and possibly ‘client’ or ‘client_1’ rather than ‘client_2’

Setting up the new ODBC System Data Source

Start=>Control Panel=>Administrative Tools=>Data Sources (ODBC)

Select ‘System DSN’ tab, then ADD

Make sure you select ‘Oracle in instantclient_11_2 as the driver

Set Data Source Name to SIMS or SIMSPROD

Select ‘SIMSPROD.ACS.EDU.AU’ from the TNS Service Name drop-down-list

Set user ID to Q_username (e.g. Q_kclohessy). Note that the TNS Service Name list should be populated.

Hopefully SAS can now see the Callista tables (with the use of an appropriate libname statement).

Sometimes this message appears (ignore it):

TNS COULD NOT RESOLVE THE CONNECT IDENTIFIER SPECIFIED etc.

To test if the Oracle component is working, at the DOS prompt, key “SQLPLUS Q_CLOHESSY@SIMSPROD” and you should be prompted by a Pheme password. If this works, then the Oracle client is successfully installed.

Also note that only a PC’s administrator can do this. A temporary power-user cannot do this.

More hints from Denis

To get Oracle ODBC working on my computer, after installation I had to change the Windows Registry by adding information from the file "K:\ora112ic_64\ODBC_INSTALL_UWA_K_Drive_X64.reg"

Here are the contents of that file:

Windows Registry Editor Version 5.00

; Refer to PCL - Installing Oracle Instant Client Version 11.2.0.3-64 Bit and 11.2.0.3-32 Bit on Win7(64Bit)

;You wil need to setup Symbolic Links for this to work

; Start a command prompt as Administrator

; MKLINK /d "C:\Windows\System32\UWA_OracleClient" "K:\ora112ic_64"

; MKLINK /d "C:\Windows\SysWOW64\UWA_OracleClient" "K:\ora112ic_32"

;

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Oracle in instantclient_11_2]

"APILevel"="1"

"ConnectionFunctions"="YYY"

"CPTimeout"="60"

"Driver"="C:\\Windows\\System32\\UWA_OracleClient\\SQORA32.dll"

"DriverODBCVer"="03.51"

"FileUsage"="0"

"Setup"="C:\\Windows\\System32\\UWA_OracleClient\\SQORAS32.DLL"

"SQLLevel"="1"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers]

"Oracle in instantclient_11_2"="Installed"