Skip to main content
I have an SDE environment with Oracle and am trying to append rows to an existing table.  The primary key is defined in the database by an auto-incrementing Oracle integer sequence.  Normally for a Postgresql or Oracle writer, setting the key to strings like 'DEFAULT' or 'MYSEQUENCE.NEXTVAL' act like they would in SQL.

 

 

However I have not been able to get the SDE workbench writer to operate in a similar manner.  When using a 'MYSEQUENCE.NEXTVAL', FME tries to be smart and do a schema check -- it won't allow writing a string to an integer field.  If I try not populating the attribute, FME complains that the NULL constraint on the PK would be violated.

 

 

I also was going to try a hack to read in the max key value with the intention of having an FME counter set each subsequent value.  However that won't work because the equivalent of a mapping file @SDEsql call doesn't exist in workbench.

 

 

Suggestions on how to do this or other approaches to try out would be appreciated.

Graeme

 

 

Can't comment on the SDE writer but in the Oracle Writer there is a Format Parameter called Sequenced Table Columns.

 

If you set a value in there of

 

 

<pk_column>:<your_sequence _name>

e.g.

GID:PARCEL_GID_SEQ

 

 

it uses the Oracle Sequence to update that attribute value.
I am assuming you are using a versioned geodatabase. I will are you making sure to append the A table for the layer?
Graeme,

 

 

If you can not do this in the writer the SQLExecutor is allways an option.

 

Just past the Select <sequence>.nextval from dual into the SQL window.

 

It will be added as an attribute to the feature.

 

 

Kind regards,

 

Erik Jan Bodewitz
As Mark says, if you use the Oracle writer there is an option on the writer to specify the column and sequence to use. 

 

 

If you are using the SDE writer or Geodatabase writer against an SDE table the primary key should be the OBJECTID column and this should get auto-populated by SDE, unless you have specified it as a USER managed id through sde command-line.

 

 

Perhaps if you can explain a little more what you are trying to acheive it might help with a resolution. For example: 

 

 

a) Are you using Oracle Spatial storage or SDE Binary?

 

b) Are you using versioning in SDE (is this a versioned table)?

 

c) What is the primary key field you mention, if not the OBJECTID?

 

 

Regards

 

 

John
Thanks for the responses,

 

 

John: I was trying to avoid using the Oracle writer, but that may not be possible.  To answer your questions,

 

a) storage type is ST_GEOMETRY

 

b) tables are not versioned

 

c) primary key field is an integer column (not object ID)

 

 

For a little more background, the tables were created in Oracle from designer-created DDLs followed by all indices, constraints, sequences, etc.  Then "sdelayer -o add" was applied to add geometry columns (and an objectID) to spatially enable some of the tables.

 

 

I'm trying to get FME to do regular a truncate/load from a processing server.  The preference is to only use an SDE connect from FME so we don't have to set up all the Oracle connection cruft.

 

 

However should I be using the Oracle writer instead of the SDE to write to all of the database tables?

 


Reply