• EN
Question

Oracle/SDE sequence increment

  • 16 November 2012
  • 5 replies
  • 44 views
G
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.

5 replies

M
Badge +2

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.
wisegis
Rank
Badge +14
I am assuming you are using a versioned geodatabase. I will are you making sure to append the A table for the layer?
Clay Wise
erik_jan
Rank
Userlevel 2
Badge +16
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
john_gis4
Rank
Badge +9
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
G
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


Community Stats

28,869
Posts
109,985
Replies
36,673
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.