Category: Oracle

Installing an Oracle ODBC-driver

Go to https://www.microsoft.com/en-us/download/details.aspx?id=104113 and download the Microsoft Connector for Oracle V1.2 that fits your OS. Here we’ll be using MicrosoftSSISOracleConnector-15.0.2000.215_x86.msi.

MicrosoftSSISOracleConnector-15.0.2000.215_x86.msi is the 32-bit version of the ODBC-driver.
MicrosoftSSISOracleConnector-15.0.2000.215_amd64.msi is the 64-bit version of the same ODBC-driver.

Install MicrosoftSSISOracleConnector-15.0.2000.215_x86.msi on the machine that needs to connect to Oracle.

When the installation is done you should be able to go to create an ODBC Data Sources with the below new Oracle ODBC driver.

Now you can enter the information needed to get access to Oracle-server through ODBC.

If you don’t have or can’t get access through a TNSNames Connection. Then you can go to another location/server/PC and you TNSPing to get the information needed to use Standard Connection.

NB! It is possible to set e.g. the Initialization String in the Advanced-tab.

SAS libname for Oracle

Below SAS-code shows how to make a libname for Oracle directly in SAS-code by providing information about the Oracle server, that you want to access.

libname <YOUR CHOICE> oracle user=<USERNAME> pw=<PASSWORD> Schema=<SCHEMA ON ORACLE>
path="(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <NAME/IP-ADRESSE OF ORACLE SERVER)(PORT = <PORT FOR ORACLE SERVER. DEFAULT ORACLE PORT IS 1539))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <DATABASE ON ORACLE SERVER>))
)";

Using TNSPING to get info about Oracle server

The command TNSPING can be used to get information about an alias for an Oracle server. Below you can see a screenshot from the command being executed in the command prompt for Windows.

In the command prompt you write: tnsping <the alias of your Oracle server>

TNSPING will return the

HOST Showing you the name of the physical host of the Oracle server.
PORT Showing you the port of the Oracle server on the physical host.
1521 is the default port for an Oracle server.