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.

Requirements

• Read access to an Oracle Spatial database and table
• If your database requires data, follow the instructions in Converting to Oracle: Create, Drop or Truncate a Table with the attached FIREHALLS.csv dataset. 

Step-by-Step Instructions

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

1. Start FME Data Inspector

Open FME Data Inspector. Go to Select File > Open Dataset from the menu bar.

From the Select Dataset to View dialog, choose Oracle Spatial Object as the Format. In the next step, we will define the Connection. 

2. Define the Connection

Two methods can be used to connect to database services: Embedded Connections and Database Connections (also referred to as Named Connections).

• Embedded connections require 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. They 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 will be available in the upcoming examples.

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. To create a database connection, click the drop-down arrow in the Connection parameter and select Add Database Connection. 

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

Connect with a TNSNAMES File to Configure the 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 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:\oracle\instantclient_23_5

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.

Once the database connection has been created, click Test to test the connection. If successful, click Save. 

3. Oracle Spatial Object Parameters

1. With the database connection created, now a table needs to be selected. From the Select Dataset to View dialog, click Parameters.

2. 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.

3. Table List: Clicking from the ellipsis from the table list will allow you to choose which tables to import. This example is using the FIREHALLS table. 

4. Once a table has been selected, click OK twice to finish selecting a dataset to view. 

4. Result

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

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.