Question

Errror creating index or primairy key in oracle writer


Badge

When writing a table with oracle writer (type: spatial object) fme cannot create primairy key or index when the name of index is getting long. For example :

CREATE INDEX "SPOT.ACCRES_KNOOP_OLVS_ID01" ON "SPOT.ACCRES_KNOOP_OLVS" ("ID")

It gives a error: SQL Error: ORA-00942: table or view does not exist

However , the table does exist.

Copying pasting the command from fme -logs to sqldeveloper gives the same error as in FME.

However, if i shorten the indexame in command like e.g. ACCRES_KNOOP_OLVS_ID the command executes fine: index created

Should FME not fix the indexname by itself so it does not give error messages when making indexes ?

Is this a bug ? Current FME version is Desktop 2017.1

Best regards, Ed


7 replies

Userlevel 4

The problem isn't FME, it's the quotation marks that's confusing Oracle. You'll have separate your schema name and your object (table/index) name, either:

CREATE INDEX "SPOT"."ACCRES_KNOOP_OLVS_ID01" ON "SPOT"."ACCRES_KNOOP_OLVS" ("ID")

or, as long as everything is upper case and the name contains no special characters or spaces, you can simply drop the quotation marks altoghether:

CREATE INDEX SPOT.ACCRES_KNOOP_OLVS_ID01 ON SPOT.ACCRES_KNOOP_OLVS(ID)
Badge

The problem isn't FME, it's the quotation marks that's confusing Oracle. You'll have separate your schema name and your object (table/index) name, either:

CREATE INDEX "SPOT"."ACCRES_KNOOP_OLVS_ID01" ON "SPOT"."ACCRES_KNOOP_OLVS" ("ID")

or, as long as everything is upper case and the name contains no special characters or spaces, you can simply drop the quotation marks altoghether:

CREATE INDEX SPOT.ACCRES_KNOOP_OLVS_ID01 ON SPOT.ACCRES_KNOOP_OLVS(ID)
Thank you for repy David , but: 

 

Those question marks are being generated by FME, at one off the attribute options in the writer , colom index, options are  Btree, Primairy key,  etc etc

 

 

 

Userlevel 4
Thank you for repy David , but:

 

Those question marks are being generated by FME, at one off the attribute options in the writer , colom index, options are Btree, Primairy key, etc etc

 

 

 

That sounds weird. Can you post your workspace here?
Badge

I can i think :) , It is nothing much, this all is just a little test from an oracle migration to a new dataplatform. All it does is read 3 spatial tables , and then writes it to the same database wiht new tablenames. But setting indexes / primairy keys. migratie-test-olsv1-spot.fmw

Userlevel 4

I can i think :) , It is nothing much, this all is just a little test from an oracle migration to a new dataplatform. All it does is read 3 spatial tables , and then writes it to the same database wiht new tablenames. But setting indexes / primairy keys. migratie-test-olsv1-spot.fmw

Thanks, I couldn't find anything unexpected, so unsure what it could be. Do you have a log file from the last run you could post?
Badge
Thanks, I couldn't find anything unexpected, so unsure what it could be. Do you have a log file from the last run you could post?
It's zipped...migratie-test-olsv1-spot.zip

 

 

Userlevel 4

After having looked at both the log and the workspace I'm starting to think this might be a bug in FME.

I recommend trying the following:

  • Upgrade to the latest version of FME 2017 and see if you're able to reproduce the issue
  • Upgrade to FME 2018 and see if you're able to reproduce the issue
  • If the bug still persists, send the workspace and the log to support@safe.com and link to this thread

Reply