Connecting to Common Data Sources

Establishing Connectivity to an Oracle Database by Using ODBC

Overview of Establishing Connectivity to an Oracle Database by Using ODBC

The following figure provides a logical view of using Oracle as a data source and connecting to the database with a SAS/ACCESS ODBC interface.

Establishing Connectivity to Oracle Databases By Using ODBC

[Establishing Connectivity to Oracle Databases By Using ODBC]

Setting up a connection from SAS to an Oracle database management system by using ODBC is a three-stage process:

Define an ODBC data source.
Register the database server.
Register the database library.

This example shows the process for establishing a SAS connection to an Oracle database. It assumes that the software for the database has already been loaded with the standard installation wizard for the database client. Before you begin, satisfy the following prerequisites:

installation of SAS/ACCESS Interface to ODBC. For requirements information, go to the Install Center at http://support.sas.com/documentation/installcenter/92/documents/index.html and use the operating system and SAS version to locate the appropriate SAS Foundation Configuration Guide.
installation of a supported Oracle Database Client if your ODBC driver requires a client. Refer to the ODBC driver vendor's documentation to determine whether an Oracle client is required.
validation that the Oracle client can communicate with the Oracle server.
(UNIX only) configuration of SAS/ACCESS environmental variables. For information about setting environmental variables when you use SAS/ACCESS to connect to data on UNIX systems, see Setting UNIX Environment Variables for SAS/ACCESS.

Stage 1: Define the ODBC Data Source

First, you must define the ODBC data source. To define the ODBC data source on Window systems, perform the following steps:

Open the Windows Control Panel. Then, double-click Administrative Tools. Double-click Data Sources (ODBC) to access the ODBC Data Source Administrator dialog box.
Click Add to access the Create New Data Source dialog box. Click the Oracle driver listed in the window (for example, Oracle in OraClient10g_home1). Click Finish to access the Oracle ODBC Driver Configuration dialog box.

Note: System data sources and user data sources store information about how to connect to the indicated data provider. A system data source is visible to all users with access to the system, including Windows services. A user data source is visible only to a particular user, and it can be used on the current machine only. For this example, we are creating a system data source.

Enter the following configuration settings:

Configuration Settings
Field	Sample Value
Data Source Name	Oracle_newserver
TNS Service Name	NEWSERVER10G (Select the name entered in the tnsnames.ora file created during installation of the Oracle database from the drop-down menu. See the following figure.)
User	User Name

The following display shows the tnsnames.ora file:

[untitled graphic]

Click OK to save the configuration settings and return to the ODBC Data Source Administrator dialog box. Then, click OK to save the data source.

Stage 2: Register the Database Server

To register the database server, perform the following steps:

Open the SAS Management Console application.
Right-click Server Manager and select the New Server option to access the New Server wizard.
Select ODBC Server from the Database Servers list. Click Next.
Enter an appropriate server name in the Name field (for example, ODBC Server). You can supply an optional description. One server is required for each DSN. Click Next.

Enter the following server properties:

Server Properties
Field	Sample Value
Major Version Number	3
Minor Version Number	7
Data Source Type	ODBC - Oracle
Software Version	10
Vendor	Oracle
Associated Machine	newserver.na.sas.com This is the server where the database is running. (Select this value from the drop-down list. If the value that you need is not available, click New to access the New Machine dialog box. Then enter the appropriate value in the Host Name field.)

Click Next.

Enter the following connection properties:

Connection Properties
Field	Sample Value
Datasrc	Oracle_newserver (Use the value entered in the Data Source Name field in the ODBC Data Source Administrator dialog box.)
Authentication type	User/Password
Authentication Domain	ODBCAuth (You might need to create a new authentication domain. For more information, see How to Store Passwords for a Third-Party Server in the SAS Intelligence Platform: Security Administration Guide.) Click New to access the New Authentication Domain dialog box. Then enter the appropriate value in the Name field and click OK to save the setting.

Click Next.

Examine the final page of the wizard to ensure that the proper values have been entered. Click Finish to save the wizard settings.

Stage 3: Register the Database Library

After you have registered the database server, you can register the database library. To register the database library, perform the following steps:

In SAS Management Console, expand Data Library Manager. Right-click Libraries and select the New Library option to access the New Library wizard.
Select ODBC Library from the Database Data list. Click Next.
Enter an appropriate library name in the Name field (for example, ODBC Library). Note that you can supply an optional description if you want. Click Next.
Select a SAS server from the list and use the right arrow to assign the SAS server. This step makes the library available to the server and makes the library visible to users of the server. Click Next.
Enter the following library properties:

Library Properties
Field Sample Value

Libref
ODBCREF

Engine
ODBC

You can also click Advanced Options to perform tasks such as pre-assignment and optimization. Click Next to access the next page of the wizard.

Library Properties
Field	Sample Value
Libref	ODBCREF
Engine	ODBC

Enter the following settings:

Server and Connection Information
Field	Sample Value
Database Server	ODBCServer (Use the database server that you selected in the New Server wizard.)
Database Schema Name	See your Database Administrator for the correct value.
Connection	Use the default value of Connection: server_name.
Default Login	Use the default value of (None).

Click Next.

Examine the final page of the wizard to ensure that the proper values have been entered. Click Finish to save the library settings. At this point, you can register tables, as explained in Registering and Verifying Tables.

Top of Page