Skip to main content
  • EN

Hello,

I have a problem to generate a unique ObjectId in Oracle. This is a Number.

I want to copy data from a shape to Oracle. No problem for the data, but Oracle requires a Unique ObjectId. I can generate this once with a Counter, but I want to run the workbench daily with different shapes. So if I use the counter and let it start at 10, the next day it will also start with 10 and then Oracle doesn't accept te data because the ObjectId isn't unique.

How can I let FME generate a Unique ObjectID each transformation?

Thank you, Marlies

You could use an sqlcreator to get the max ObjectID in the existing table and then add this figure to the count attribute to gain the ObjectID

This works for me in PostGIS.

This works for me in PostGIS.

Unfortunately, Oracle does not support the implicit creation of sequences, like PostgreSQL does. Something like it (IDENTITY column) was introduced in Oracle 12c, but as far as I know that syntax it isn't supported by FME.

You could use an sqlcreator to get the max ObjectID in the existing table and then add this figure to the count attribute to gain the ObjectID

Thank you for your fast reaction. Do you have written this down once?

My SQl is not very good. So if you have an example, I can adapt this to my case.

This works for me in PostGIS.

I've tried, but for Oracle the above will not work. I'm going to try the SQL-script.

Thank you!

select Max(ObjectID) as LastId from etc.

Then use counter and set start from = LastID

Thank you for your fast reaction. Do you have written this down once?

My SQl is not very good. So if you have an example, I can adapt this to my case.

Within an sqlcreator something like the following

select max(ObjectID) as maxObjectID from table1

This will return a single record with an attribute called maxObjectID which you can use.

You could then join this with your other records and using an attributecreator set ObjectID to equal count + maxObjectID for example

It works. Thank you

Within an sqlcreator something like the following

select max(ObjectID) as maxObjectID from table1

This will return a single record with an attribute called maxObjectID which you can use.

You could then join this with your other records and using an attributecreator set ObjectID to equal count + maxObjectID for example

Just a small detail: Since Oracle is upper case by default, the attribute you need to expose will be called MAXOBJECTID

Just a small detail: Since Oracle is upper case by default, the attribute you need to expose will be called MAXOBJECTID

But if you expose attributes using "populate from SQL query" FME will helpfully sort this out for you, and also confirm you've got your sql right. :-)

Reply


Community Stats

30,107
Posts
114,275
Replies
37,740
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.