Indexing Oracle Databases

Dependencies

Voyager uses the library cx_Oracle for communicating with Oracle. This library is recommended by the Python community.

Although Voyager includes the cx_Oracle library in its setup, the following requirements are still necessary to be performed by the customer before indexing:

Python (32-bit) must be installed on the Voyager Server machine. Ideally, the Python version should be 2.7.
DO NOT USE EARLIER VERSIONS OF PYTHON
Alternatively, the Python installer shipped with ArcGIS 10.1/10.2 can be used to install Python. NOTE: If ArcGIS 10.1 or 10.2 is installed, Python is likely to already be installed.
Oracle Instant Client is required to be installed on the Voyager Server. Download the client version that matches the version of Oracle being used. See the section below for Oracle Client Installation Instructions.
After the Oracle client is installed, add the oracle client folder location to the start of path variable (i.e. c:\oracle).

Oracle Client Installation Instructions

Download client from (i.e. win32_11gR2_client): http://www.oracle.com/us/industries/public-sector/112010-win32soft-098987.html
Unzip the file to directory named win32_11gR2_client or oracle.
Browse to win32_11gR2_client\client folder location and double click on setup.exe.
Select Yes for User Account Control permissions to install software.
Select Administrator Install option and click Next.
Select Language option (default is English) and click Next.
Select the Oracle Base and Software Location and click Next.
Check Ignore All for Environment variable:”PATH” and click Next.
Click Finish.

Summary of Installation:

Oracle Base: D:\app\
Software Location: D:\app\product\11.2.0\client_32\
Tnsnames Directory Location: D:\app\product\11.2.0\client_32\network\admin.
tnsnames.ora file: Ask your DBA to provide tnsnames.ora file to connect to Database Server. You can also create tnsnames.ora file using following syntax: Replace SERVICE_NAME, HOST and PORT information to match your Oracle Instance. Create tnsnames.ora under Tnsnames Directory

# tnsnames.ora Network Configuration File:
D:\app\oracle\product\11.2.0\client_32\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
voyager =
(DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = odn.voyagersearch.com)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = voyager)
 )
 )

Testing

Test tnsnames.ora Connection Using tnsOpen cmd prompt and go to the 32-bit client bin directory and type tnsping and the service name (e.g. voyager).
Test Connection to Oracle Using tnsnamesEnter sqlplus <username>.
Enter Password: (Enter your Oracle password followed by @Instance name). For example, if your password is “password” and Instance is “voyager”, you should enter password@voyager. You should then connect to the Oracle Database as shown below.

Configuring Locations

After verification of dependencies, a custom location must be configured. To do this, follow these steps:

Go to Manage Voyager > Locations
Click New Location
Select Databases (Advanced)
Select Oracle in the Connections drop-down list
Edit the configuration.

Sample Configurations

Example 1: Index tables in an Oracle database

This sample indexes all user tables in an Oracle database. The sql connection must be specified by providing the driver (Oracle), server name, database/instance, user name and password.

{
  "name": "A Oracle location",
  "type": "python",
  "config": {
    "fields": {
      "include": ["*"]
    },
    "tables": [
      {
        "name": "*",
        "action": "INCLUDE"
      },
      {
        "name": "*",
        "map": {" NAME": "name"},
        "query": ""
      }
    ],
    "sql": {"connection": {
      "driver": "Oracle",
      "server": "localhost",
      "database": "sde",
      "uid": "jp",
      "pwd": "jp"
    }
   }
  }
}

Example 2: Index all user tables and views in an Oracle database

In this example, the connection is made as user “gdb1” where all user tables for gdb1 will be indexed. As well, all views for the owner named “test” will be indexed. Oracle views are configured in the “layers” section of the configuration.

{
  "name": "PYTHON_GDB1_TEST",
  "type": "python",
  "config": {
    "fields": {"include": ["*"]},
    "tables": [
      {
        "name": "*",
        "action": "INCLUDE"
      },
      {
        "name": "*",
        "map": {"ID": "id"}      }
    ],
    "layers": [
      {
        “name”: “*”,
        "action": "INCLUDE",
        “owner”: “test”
      },
      {
        "name": "*",
        "map": {"ID": "id"}
      }
    ],
    "sql": {"connection": {
      "driver": "Oracle",
      "server": "Localhost",
      "database": "sde",
      "uid": "jp",
      "pwd": "jp"
    }}
  }
}

Example 3: Index only views in an Oracle database

This sample indexes only views where owner is test and the view name ends wit _EVW. It excludes all user tables.

NOTES:

Use a percent sign (%) for the wild card when indexing Oracle tables
Specifying queries and constraints are not yet supported for Oracle