Skip to main content
Question

Errror creating index or primairy key in oracle writer

edvdl
Contributor
Forum|alt.badge.img+1
  • edvdlContributor
  • Contributor

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

david_r
Evangelist
  • david_rEvangelist
  • March 15, 2018

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)
edvdl
Contributor
Forum|alt.badge.img+1
  • edvdlContributor
  • Author
  • Contributor
  • March 15, 2018
david_r wrote:

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

 

 

 

david_r
Evangelist
  • david_rEvangelist
  • March 15, 2018
edvdl wrote:
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?
edvdl
Contributor
Forum|alt.badge.img+1
  • edvdlContributor
  • Author
  • Contributor
  • March 15, 2018

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

david_r
Evangelist
  • david_rEvangelist
  • March 15, 2018
edvdl wrote:

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?
edvdl
Contributor
Forum|alt.badge.img+1
  • edvdlContributor
  • Author
  • Contributor
  • March 15, 2018
david_r wrote:
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

 

 

david_r
Evangelist
  • david_rEvangelist
  • March 15, 2018

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


Helpful Members This Week

Community Stats

31,105
Posts
117,925
Replies
38,857
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.