span8
span4
span8
span4
Intro: Getting Started with Oracle|Previous Section: Converting from Oracle|Next Section: Converting to Oracle: Write to an Existing Table
Note: The above video has been filmed using FME 2016.1
FME has two feature type writer parameters to allow users to control how data is written into a database;Feature Operation,and Table Handling.
Updates and deletions to a database can be primarily controlled through a Writer parameter called Feature Operation.This parameter informs FME what action to carry out on the database.The values that can be used for the feature operation are INSERT,UPDATE,DELETE,and fme_db_operation.
Table Handling parameters exist to help create or update existing database tables.Table Handling makes the following options available:
In this tutorial we will load data to create a new table,then demonstrate how to replace a table using a Table Handling parameter.
This demonstration will create a new table by loading Downtown Vancouver public art data,reading the information in as points,and writing a new table in Oracle.The data for use has been provided in the downloads section.
(1) Add a CSV Reader
Start FME Workbench,and select the New workspace option.
Add a new reader to the canvas.In the Add Reader dialog,select CSV for the source format.Select the Downtown.csv file for the Dataset.
Click on the Parameters button.In the Attributes panel,set the Attribute Definition to Manual.You can now set the columns ‘Longitude' and ‘Latitude' as type ‘x_coordinate' and ‘y_coordinate' respectively.This tells the reader to generate point features from the CSV data as it reads the file.Click OK to dismiss the CSV parameters dialog,but do not exit out of the Add Reader dialog.
Set the coordinate system to LL84.Each point will be tagged with this coordinate system as it's read.Click OK to dismiss the Add Reader dialog.
(2) Add the Oracle Spatial Writer
Add a new writer,and select the Oracle Spatial Object format.For the dataset,select ‘add database connection',and Connect to your Oracle database instance.Refer to theViewing and Inspecting Oracle Data articleif you need additional details for connecting to Oracle.
(3) Set the Table Name and Table Qualifier parameters
After adding the Writer,access the feature type general parameters.Set the Table Name to ‘Downtown_PublicArt' and the Table Qualifier to a Schema being used in the Oracle database.(For our demonstration workspace,we will set the Schema to ‘SUPPORT') The Feature Type writer is specified per database table,as one could write to multiple schemas using the same database writer.
(4) Set the Feature Operation and Table Handling parameters
Because we are creating a new table in the Oracle database and adding data to it,the Feature Operation and Table Handling parameters will be left at their default setting;Feature Operation: ‘Insert',and Table Handling: ‘Create If Needed'.
(5) Map Attributes
Connect the CSV feature type reader and the Oracle Spatial feature type writer.On the connection,right-click and select Auto Connect Attributes.Auto Connect Attributes is used to replace attribute names defined as lowercase to uppercase as defined in the Oracle output.
(6) Run the Workspace
Run the workspace,and view the result by right-clicking the writer and choosing ‘inspect'.
It is possible to replace the entire contents of a table by using a Table Handling parameter.The Table Handling parameters to accomplish this are,‘Drop and Create' and ‘Truncate Existing'.
‘Truncate Existing' is used when the table needs to be emptied of existing data,but does not otherwise need an update to its schema.‘Drop and Create' is used when the table needs to be emptied AND an update is to be made to the database schema.For example,use a ‘Drop and Create' when you wish to update a table with new content and require a new column to be added to the table.
It is also necessary to set the Feature Operation parameter to INSERT.UPDATE and DELETE will be of no use when the existing table has been emptied first.
Continue to Part 4: Converting to Oracle: Write to an Existing Table
Let the Database Do the Work: Reading
Performing spatial queries on database tables using the FeatureReader
Converting to PostGIS: Write to an Existing Table
Forcing Upper Case attribute names when writing to Oracle DB
Oracle reader error ora-00904 invalid identifier MI_STYLE
Working with Synonyms in Oracle
Adding metadata entries for Oracle spatial tables or views
Viewing and Inspecting PostGIS Data
© 2019 亚搏在线Safe Software Inc |Legal