Cannot update Oracle table using Feature Operation in Oracle Non Spatial Writer, DatabaseUpdater works fine

Hi @ebygomm​ ,

It appears you are setting the fme_db_operation which is required. It also appears you are setting the columns to match... in the Feature Type (you might also consider creating an fme_where clause attribute). However, for updates or deletes to hit the writer correctly you need to ensure the Writer mode or the Feature Type Writer Mode is set to UPDATE or DELETE. Either mode will work just fine and handle both updates or deletes. With the Writer Mode set to the UPDATE|DELETE you can then pass fme_db_operation (INSERT, UPDATE, or DELETE) to the feature type and this will perform the desired operation with the attributes being passed. Make sure this helps the situation. I think there is something a miss and hopefully it was the Writer Mode.

Before I go, it might be helpful if you can pass along the Oracle Database client and database version. You might also consider creating a Case with us and share your workspace in the off chance there is something else going on here. From the behavior you are describing... I might check in with your DBA to check in on your session (while FME is in this 'paused' logging state) to see what might be going on at the database end... there could be some heavy meaningless queries being run.

Hope this pulls you back from pulling your hair out.

If this isn't clear let me know.

I suspect this was some sort of issue with the database (stats table or index maybe) as the issue resolved itself.

"However, for updates or deletes to hit the writer correctly you need to ensure the Writer mode or the Feature Type Writer Mode is set to UPDATE or DELETE."

Curious about this sentence though, where do you set the writer mode? Feature Operation is set to fme_db_operation

@ebygomm​ ,

See screengrab below...

If you are setting the fme_db_operation then this is fine... alternatively you can set it to update or delete to drive those actions. A few years back we did some changes with this. You used to be able to set it at the Writer level and/or the Feature Type Level and that was a subtle I was speaking to before. You can ignore this.

Now, the feature type needs to be using fme_db_operation to be able to deal with, inserts, updates and deletes.

If you set it to update or delete, you'll get the writer attempting to do an update or delete operation for all features (this is if you haven't created the attribute fme_db_operation to control the operation).

Keep it simple and make use of the Feature Operation and set it to the attribute fme_db_operation. Create an attribute fme_db_operation and populate it with UPDATE, INSERT, or DELETE.

Hope that helps.