Establishing Connectivity to an Oracle Database

Overview of Establishing Connectivity to an Oracle Database

The following figure provides a logical view of using Oracle with SAS/ACCESS as a data source.
Establishing Connectivity to Oracle Databases
Establishing Connectivity to Oracle Databases
Setting up a connection from SAS to a database management system is a two-stage process:
  1. Register the database server.
  2. 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 by using the standard installation wizard for the database client. The following prerequisites have been satisfied:
  • installation of SAS/ACCESS Interface to Oracle. For configuration information, see the Install Center at http://support.sas.com/documentation/installcenter/93 and use the operating system and SAS version to locate the appropriate SAS Foundation Configuration Guide.
  • installation of a supported Oracle Database Client.
  • validation that the Oracle client can communicate with the Oracle server.
  • (UNIX only) configuration of SAS/ACCESS environmental variables. For more information, see Setting UNIX Environment Variables for SAS/ACCESS.

Stage 1: Register the Database Server

To register the Oracle database server, perform the following steps:
  1. Open the SAS Management Console application.
  2. Right-click Server Manager and select the New Server option to access the New Server wizard.
  3. Select Oracle Server from the Database Servers list. Then, click Next.
  4. Enter an appropriate server name in the Name field (for example, Oracle Server). Note that you can supply an optional description if you want. Click Next.
  5. Enter the following server properties:
    Server Properties
    Field
    Sample Value
    Major Version Number
    10
    Minor Version Number
    2
    Software Version
    10.2.0
    Vendor
    Oracle Corporation
    Click Next.
  6. Enter the following connection properties:
    Connection Properties
    Field
    Sample Value
    Path
    NEWSERVER10G (This value is contained in the tnsnames.ora file generated during the Oracle installation. The file is stored in an Oracle installation directory such as /opt/oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora. The alias for the connection information is contained in this file. See the following figure.)
    Authentication type
    User/Password
    Authentication domain
    OracleAuth (You might need to create a new authentication domain. For more information, see How to Store Passwords for a Third-Party Server in 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.
    The following figure shows a sample tnsnames.ora file:
    Example of a tnsnames.ora file that shows the Oracle system identifier
    Note that the correct Path value is circled. Click Next.
  7. Examine the final page of the wizard to ensure that the proper values have been entered. Click Finish to save the wizard settings.

Stage 2: Register the Database Library

After you have registered the database server, register the database library. To register the Oracle database library, perform the following steps:
  1. In SAS Management Console, expand Data Library Manager. Right-click Libraries. Then, select the New Library option to access the New Library wizard.
  2. Select Oracle Library from the Database Data list. Click Next.
  3. Enter an appropriate library name in the Name field (for example, Oracle Library). You can supply an optional description. Click Next.
  4. 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.
  5. Enter the following library properties:
    Library Properties
    Field
    Sample Value
    Libref
    ORAREF
    Engine
    ORACLE
    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.
  6. Enter the following settings:
    Server and Connection Information
    Field
    Sample Value
    Database Server
    OracleServer (Use the database server that you created in the New Server wizard.)
    Database Schema Name
    See your database administrator for the correct value.
    Default Login
    Use the default value of (None).
    Click Next.
  7. 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, register tables as explained in Registering and Verifying Tables.