Reading and Writing Oracle Autonomous Databases

Introduction

As of FME 2020.1, you can read and write data from Oracle Autonomous Databases. In this article, you will learn how to write data to an Oracle Autonomous Database, and then in Part 2, you will learn how to read that data into FME. Before you begin, please ensure you have met all of the requirements.

Video

Requirements

• Oracle Client and SQL*Plus Packages 18.5 or higher
• FME Desktop 2020.1 or higher
• Oracle connection created

Downloads

WritingOracleAutonomous.fmwt completed workspace for Part 1

PublicArt.xlsx data for Part 1

Step-by-step Instructions

Before we can read or write to Oracle Autonomous Databases, we need to create a connection. If you do not have a connection already created, please go to Connecting to Oracle Autonomous Databases and follow the instructions.

Part 1: Writing to Oracle Autonomous

1. Add Excel Data

In a blank workspace, add a Microsoft Excel reader to the canvas. Browse to the PublicArt.xlsx file, set the Coord. System to LL84 and the Workflow Options to Single Merged Feature Types.

2. Add an AttributeManager

We need to rename the attribute that gets created when the Single Merged Feature Type option is used. Add an AttributeManager to the canvas, in the parameters, rename fme_feature_type to Neighborhood.

3. Add an Oracle Autonomous Writer

Now we can write out our data. Add an Oracle Autonomous Non-Spatial writer to the canvas. For Spatial Data, see the tip at the end of Part 1.

In the writer parameters, select your Oracle connection that you have previously set up. If you do not have a connection set up, please see Connecting to Oracle Autonomous Databases. As a basic example, we do not have any additional parameters to set up. If you would like to explore the parameters further, please see the Oracle Autonomous documentation.

Change the Table Definition to Automatic and then click OK. In the Feature Type dialog, set the Table Name to PublicArt and then for Table Qualifier, enter in the table you will be writing to; for this example, this is ADMIN. If you are not sure what table you will be writing to contact your database administrator. Ensure that the Feature Operation is set to Insert and click OK.

Connect the PublicArt writer feature type to the AttributeManager.

4. Run the Workspace

Save the workspace and then run it.

If you get the following error message, ensure that you have the correct table name or the credentials you used in your connection have sufficient privileges:

Writing Spatial Data

To write spatial data to Oracle Autonomous, the Oracle Autonomous Spatial Object writer needs to be used. For more details on the Oracle Autonomous Spatial Object parameters, please see the documentation.

Part 2: Reading from Oracle Autonomous

1. Complete Part 1

Before you can read from Oracle Autonomous, ensure that you have completed Part 1 of this tutorial, or you already have data in your database that you can read.

2. Add an Oracle Autonomous Reader

In a blank workspace, add in an Oracle Autonomous Non-Spatial Reader to the canvas. Select your Oracle Connection that you have previously created. If you do not have a connection already created, please see the article Connecting to Oracle Autonomous Databases.

In the parameters, click on the ellipsis next to Table Qualifier and select the table that you want to read in. For this example, we are reading in ADMIN.PublicArt.

Click OK three times to add the reader to the canvas.

For information about additional parameters, or how to set a WHERE Clause, please see the documentation.

3. Continue Workflow

Now that your Oracle Autonomous data has been read in, you can continue building your workspace.

Reading Spatial Data

To read spatial data from Oracle Autonomous, the Oracle Autonomous Spatial Object reader needs to be used. For more details on the Oracle Autonomous Spatial Object parameters, please see the documentation.

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.