Tutorial: Getting Started with Oracle

Introduction

This tutorial will provide you with all the information necessary to begin working with Oracle/Oracle Spatial in FME. Databases are useful for long-term data storage, rather than short-term data transfers, so the key operations are getting data into and out of that store. This tutorial will focus on how to move data into and out of Oracle databases, and introduce new users to key concepts, as well as the Oracle Reader and Writer parameters.

FME provides non-spatial data support through the Oracle reader/writer, and spatial data support through the Oracle Spatial reader/writer. This tutorial series will focus on the use of the Oracle Spatial database.

The three key operations that occur within a spatial database, including Oracle Spatial are:

• Data Imports
• Data Updates
• Data Distribution

These activities involve transforming the data into the correct format, as well as transforming the data into the correct schema (data model).

Database Terminology

When working with FME and databases, it is important to first understand the basic database terminology used by FME, as terms may differ depending on the particular database package being used.

• Schema: Also known as the Data Model. In the Oracle table list, the table name is prefaced by the schema (schema.table)
• Authentication: Confirming the user trying to access the database has the right to do so
• Geometry: Spatial Type, used for planar data such as UTM coordinates
• Geography: Spatial Type, used for ellipsoidal data such as latitude/longitude
• Coordinate System: Also known as Spatial Reference System or Spatial Reference Identifier (SRID)

The Oracle Client

(1) Before you can begin working with Oracle database connections you must first have the Oracle Client, or Oracle Instant Client, installed.

(2) If the Oracle Client is missing, all the Oracle reader and writer formats will appear greyed-out, and unavailable for use.

(3) Also note that the Oracle client must be the same platform as your version of FME, (i.e. 32 bit FME requires a 32 bit Oracle Client; and 64 bit FME requires a 64 bit Oracle Client).

(4) Please refer to this Knowledge Center article for step-by-step instruction on setting up the Oracle Instant Client for use with FME on Windows, Linux, UNIX, and Mac OS X operating systems.

Tutorials

Please continue along with the following resources to learn how to get started with Oracle in FME.

Viewing and Inspecting Oracle Data

This article will demonstrate how to connect to an Oracle database using the Data Inspector

Converting from Oracle

This article will demonstrate how to read from an Oracle database

Converting to Oracle: Create, Drop, or Truncate a Table

This article will demonstrate how to write to an Oracle database

Converting to Oracle: Write to an Existing Table

This article will demonstrate how to write to a database that already exists through importing table schemas.

Converting from Oracle to Shapefile

This article will demonstrate how to translate a table stored in an Oracle database into an ESRI Shapefile (*.shp) format.