Viewing and Inspecting Oracle Data

Introduction

Connecting to a database differs slightly from selecting a file/folder based format. Establishing a database connection requires first that database connection parameters be supplied. The basic database connection parameters include:

• Host (Server) Name
• Network Port Number
• Database (Service) Name
• Username
• Password

Parameters will differ depending on the database format being used, but changes to parameter settings will always be made by clicking on the “Parameters..” button on the dataset selection dialog.

Connecting to Oracle: Step-by-Step Instructions

Follow these steps to connect to an Oracle Spatial database in FME.

1. Start FME Data Inspector

• Select File > Open Dataset from the menu bar.
• From the Select Dataset to View dialog, choose “Oracle Spatial Object”

2. Define the Dataset

There are two methods that can be used to connect to database services; Embed Connections, and Database Connections (also referred to as Named Connections). Embed connections requires the user to enter connection parameters each time a user reads from or writes to a database. Database connections provide a simple and secure way to store and reuse connection parameters. Database connections are specified once, and then it is possible to reuse a previously saved connection, rather than having to re-enter connection parameters each time. For more information regarding database connections, please see Using Database Connections.

• For this example, we will be using Database connections, so that our connection is available for use in the upcoming examples.

3. Set the Connection Parameters

It is possible to connect to an Oracle database using different methods.

This tutorial series does not provide public access to an Oracle database; rather, it is expected that you follow along through the exercises using your own Oracle instance. Therefore, you will need to replace the database connection parameters with your system-specific parameters.

Connect with the Easy Connect Connection String

The Easy Connect connection string is the easiest way of connecting to your Oracle database. Use this type of connection string as your "dataset" in FME. When using this string it is not necessary to enter a username or password - these are included in the Easy Connect string.

user/password@//hostname:port/sid

For example

scott/tiger@//amidala:1521/orcl

Using the Oracle Easy Connect Connection String

Connect with a TNSNAMES File to configure your connection

Another method for configuring an Oracle database connection involves the use of a tnsnames.ora file. The tnsnames.ora file could reside anywhere on the system, but for convenience it should be placed in the same directory as where the instant client was installed. A tnsnames.ora text file consists of a series of service definitions, which are of the form:

<net_service_name> = (DESCRIPTION =
   (ADDRESS_LIST = 
	(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <1521>))
   )
   (CONNECT_DATA =(SERVICE_NAME=<oracle_sid>)	
   )
)

For example:

AMIDALA = (DESCRIPTION =
   (ADDRESS_LIST = 
	(ADDRESS = (PROTOCOL = TCP)(HOST = AMIDALA)(PORT = 1521))  
   )
   (CONNECT_DATA =(SERVICE_NAME = ORCL)
   )
)

Once you've created the tnsnames.ora file, you must then set the TNS_ADMIN environment variable to be the path of the tnsnames.ora, (NOT including the tnsnames.ora filename itself). For example,

TNS_ADMIN=C:\win32apps\oracle\instantclient_10_2

TNS_ADMIN may have problems resolving path names with spaces in them, and it is recommended to avoid paths that include them. Also, there can be an issue with tab characters in a tnsnames.ora file, so rather than copying and pasting the text from the example provided above, it is recommended that a new file be created.

Using the TNSNAMES (tnsnames.ora) file to Connect to Oracle

4. Oracle Spatial Object Parameters

• From ‘Select Dataset to View’ click “Parameters..”
• Select an Oracle Workspace for use. If the name of the Oracle Workspace parameter is omitted, or left blank, the default LIVE workspace will be used.
• Table List: Clicking from the ellipsis from the table list will allow you to choose which tables to import. For this example we will make use of the "FIREHALLS" tables.
• Note: A copy of the FME sample datasets are available for download (as a zip file) if you would like to load the data into your Oracle database.

5. Result

The Firehalls table, which includes both geometry and attributes, is displayed in the FMEData Inspector.