Error when using FME to create a table into Oracle when the user is not the owner.

Hi all

I have a workspace that is managing a dynamic migration from one Oracle 11g database to Oracle 12c. The process is quite mature and has mainly be used to truncate and insert new data. We now have to extend this process to create tables if required.

The writer connection is a different user (USER1) to then one where the table is being created into (USER2). USER1 has heightened privileges and is used for all maintenance tasks. Grants include 'create any table', among others.

We have started to encountered exceptions when attempting to drop/create a table into the destination instance.

Using Feature Writer or a writer/Feature Type, when we try to 'drop/create' or 'Create if needed', we get the following exception raised:

"Cannot create table 'TABLE_FROM_FW' within schema 'USER2', when logged in user is 'USER1' ".

Full log with debug here:

I have tested with Oracle Spatial Object and Oracle Non-Spatial writers both withe same affect.

Interestingly as a test we used SQLExecutor to create the table using the same connection user and destination schema and it worked!

If i connect as USER2, the destination owner i do not get an exception but this is not an option for our process.

in SQL Developer if i connect as USER1, I can create a table into USER2 without issue.

My instinct was that this was a privileges issues but the fact I can create using other methods makes me doubt this.

I am working in 2018.1 due to the customer version but have tested in 2019.0 with the same affect.

Has anyone else seen behavior like this?

Many thanks in advance

Adrian

Page 1 / 1

Hi Adrian,

My first thought was privileges too.

I tested with FME 2019.2 and got the same Error message.

Looking at the log file, it almost seems the table creation is not done using the SQL interface, but using OCI (Oracle Call Interface).

I would reach out to www.safe.com/support and file a case for this.

This seems like unwanted behavior.

Hope they can help you solve this.

Erik

Hi Erik

That very useful thanks you, good to know its not an obvious user error on my part.

I was thinking along similar lines that the writer and sqlexecutir were using different communication methods to the database.

Many Thanks

Adrian

Hi Adrian!

I can confirm this is the current behavior. You can write to an existing table (be aware of Case Sensitive schema table names) but not create a new one. It is not on the radar to be fixed.

I don't see any Ideas posted on this one but we do have an ISSUE filed.

Feel free to create a ticket and we can attach your case to it. I'll add your post to it - so don't feel like you need to file a Case.

See you in June!

Hi Adrian!

I can confirm this is the current behavior. You can write to an existing table (be aware of Case Sensitive schema table names) but not create a new one. It is not on the radar to be fixed.

I don't see any Ideas posted on this one but we do have an ISSUE filed.

Feel free to create a ticket and we can attach your case to it. I'll add your post to it - so don't feel like you need to file a Case.

See you in June!

Hi Steve

Bit surprised that this is accepted behavior, I must admit i has just assumed it would not be an issue. Appreciate the confirmation on this though.

A ticket has been raised following on from the post

ref:_00D30ePES._5004Q1yFdRc:ref

See you soon

Cheers

Adie

I should add to the reason why this is currently not possible in FME.

It has to do with the creation of Spatial Metadata in the Oracle system table called USER_SDO_GEOM_METADATA. This is the only metadata table that is updatable by the connected user to populate the necessary spatial metadata. Behind the scene is another system table called ALL_SDO_GEOM_METADATA table that contains a column that stores the SCHEMA owner. This column is not available in the USER_SDO_GEOM_METADATA table and thus, metadata must be inserted by the schema owner user into USER_SDO_GEOM_METADATA table. It is not possible to insert data into the system table ALL_SDO_GEOM_METADATA table, and this is where the problem comes from.

See Oracle's documentation for more information: https://docs.oracle.com/database/121/SPATL/geometry-metadata-views.htm#SPATL545

So, when the FME Writer is connected to the Oracle Database as USER1, it cannot then connect as the schema owner (USER2) to generate the necessary metadata (that feature just doesn't exist in FME at this time).

This created the issue in FME that when the logged in Oracle 'user' did not equal the schema owner it became not possible to create a table, ex. USER1 attempting to create a table in schema USER2. At that time, a decision was made to not allow tables to be created in this way.

This resolved the issue for the Spatial Writer, whereby spatial metadata could then be inserted by the user creating the new table because it had to the same schema user as well.

Unfortunately, this restriction has also affected the non-spatial writer.

I suppose one idea would be to allow this for the spatial writer and produce a warning message in the log file - indicating missing metadata - and log the SQL to update the metadata table by the appropriate user. There could be implications of this... that might not be wanted.

In addition, FME could be made to allow the non-spatial writer to do this type of table creates ('cross schema').

Sorry - this is long-winded.

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded