Hi
It might be possible, but I'm not sure I'd recommend it. Even if it works now, you risk that it does something subtly different down the road.
To improve the Geodatabase writer performance, have you looked at this article?
David
Yes, you can do it this way as long as you are using SDO_GEOMETRY and not the SDE GEOMETRY type.
You could even have FME create the tables, but then you will need to use the ESRI tools to register the tables afterwards.
I noticed this approach can be much faster than the direct SDE writing.
Obviously you can not use versioning on the SDE data if you use the Oracle Spatial writer.
I would also look at the spatiallite. It seems to act like the file geodatabase and is easily read by programs like QGIS. I have switched to them and really like it because it works well with ogr2ogr when sending to a postgres databases, i am sure it would work just as well going to a oracle database also.
As mentioned above you can amend SDO_GEOMETRY and it works fine in production. It is the the ESRI recommended way if data manipulation is required by non-ESRI 3rd party software.
However you have to take care that any geometry is valid for SDE.
SDE validation is stricter:
http://desktop.arcgis.com/en/arcmap/10.3/manage-data/using-sql-with-gdbs/geometry-validation.htm
than SDO:
https://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objgeom.htm#BGHFDDBF
If geometries may not be always valid it is worth to send them through a GeometryValidator. I don't know if there is any rule combo that is suitable for SDE validation (there is a request for that: https://knowledge.safe.com/content/idea/18827/geometryvalidator-3.html ) but the OGC rules should do the vast majority of the job.
The tool that can be used in FME to do real SDE validation is an ArcSDE command line tool:
http://help.arcgis.com/en/geodatabase/10.0/admin_cmds/Support_files/datamgmt/sdelayer.htm
sdelayer -o feature_info -r invalid ...
It reveals the FID of invalid geometries only but no information about where the error is.
Thanks for everyones responses so far!
I have now tried this practically and am struggling when using the Oracle Spatial writer when writing to the same registered table using parallel processing (via the WorkspaceRunner). It seems that I have to manually enter a unique value for the 'OBJECTID' field for each record. This would be ok if running just a single workspace by using a Counter transformer. But when using the WorkspaceRunner which spawns multiple processes that are INSERTING at the same time, I cannot ensure that a unique numeric value is entered. Any ideas?
Hi
You should never define the value of OBJECTID yourself, it is bad practice that can lead to primary key collisions, as you've seen. If you use the ESRI family of writers they will automatically use the appropriate SDE functions to get the next value from the OBJECTID sequence for that particular table, and in this case any OBJECTID that you send to the writer will simply be ignored.
However, if you use the Oracle Spatial writer (i.e. a non-ESRI writer) you've effectively short-circuited this mechanism and all bets are off: You will have to ask SDE to give you the next OBJECTID value for that particular table yourself. The proper way of doing this is to call the SDE function next_row_id(), which will enable SDE to know that this OBJECTID has been used and not try to re-use it later.
The important bit is to know that, for performance reasons, the OBJECTID chosen by SDE isn't based on MAX(OBJECTID)+1, but on the last known value used in the sequence associated with that feature class.
Example that you can use in e.g. an SQLExecutor to get next OBJECTID for a given table:
SELECT sde.version_user_ddl.Next_row_id('MY_SCHEMA_NAME',
(SELECT registration_id
FROM sde.table_registry
WHERE table_name = 'MY_TABLE_NAME'
AND owner = 'MY_SCHEMA_NAME')) AS OBJECTID
FROM dual
Using this method you ensure that all processes will get unique OBJECTID values, even when running concurrently, and you will avoid the SDE sequence coming out of sync with the actual values used in the feature class table.
David
