Question

Create Unique ObjectId in Oracle out of different shapes, daily.


Badge

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


10 replies

Badge +10

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

Userlevel 6
Badge +31

This works for me in PostGIS.

Userlevel 4

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.

Badge

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.

Badge

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!

Badge +3

select Max(ObjectID) as LastId from etc.

Then use counter and set start from = LastID

Badge +10

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

Badge

It works. Thank you

Userlevel 4

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

Badge +10

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