In this simple example, we'll illustrate how to use the SAS query tool to examine information from the EMP table in the 'SCOTT' schema hosted on a remote Oracle database, using the well-known scott/tiger login. To keep things simple, we'll use only the most basic SAS capabilities.
Install the DataDirect Connect for ODBC Oracle Wire Protocol driver (available in both 32 and 64-bit versions) on your Linux client.
Please keep these important requirements in mind:
- This product has been certified on the Red Hat Enterprise Linux and SUSE Linux Enterprise Server distributions.
- Make sure that you have sufficient privileges on this computer to perform a software installation.
- You'll need to run the installer from within the Korn (ksh) shell.
- For the 32-bit version, the default installation directory is /opt/odbc32v53.
- Although these instructions were created using the 5.3 release of the DataDirect ODBC drivers, they can also be easily adapted to earlier or later versions of the drivers.
Set your environment variables.
To help you perform this task, DataDirect provides shell scripts for both the Bourne (odbc.sh) and C (odbc.csh) shells. You’ll find these scripts in your installation directory.
These scripts configure the following environment variables:
Use the DataDirect-provided ivtestlib utility to test load the driver.
You'll find this utility in the bin sub-directory of your installation. You'll need to provide it with the path to the Oracle driver as follows:
If your installation was successful and your environment variables are properly set, you should receive a message similar to the following:
/opt/odbc32v53/lib/ivora23.so successful, qehandle
File version: 05.30.0157 (B0108, U0075)
If you receive an error message at this point, there's a good chance that your environment variables aren't properly set.
Configure a connection to Oracle.
You may either edit the odbc.ini file (found in your installation directory) or use the graphical odbcadmin utility, which you'll find in the tools subdirectory. Note that to run odbcadmin, you’ll need to have configured Motif support on your computer.
Select the Oracle Wire Protocol driver, and click the Configure button:
Enter details about your connection to Oracle, including:
- The computer (i.e., host) where Oracle is running
- Oracle's port number (the default is 1521, but check with your Oracle administrator to be sure)
- The Oracle system identifier
When you've finished entering these details, it's a good idea to test the connection. Click on the Test Connect button, and you'll receive a login dialog as shown below. After filling in the necessary information, click OK and you should receive a message that the connection was successful.
If you're unable to successfully connect, double check your login settings with your database administrator.