Writing to an Oracle table with Foreign Keys

Symptom

You encounter errors when trying to write to an Oracle table with enabled foreign keys.

Cause

Due to the way FME writes to Oracle it is possible to have different sessions writing to the tables in the workspace. This would happen if you are inserting new values to the foreign key tables in addition to a table with a foreign key. Because the commits do not happen in the correct order it is possible to receive an error running the translation.

Resolution

Resolution 1

One would think, understanding Oracle the easiest way around this would be to disable the foreign keys involved by using the advanced parameter (Oracle Writer directive) "SQL Statements To Execute Before Translation" on the Oracle Writer in FME and then enable the foreign keys with the "SQL Statements to Execute After Translation". However this also has problems as it seems FME will not do the commit when these statements run and we'll still receive errors from Oracle - i.e. "resource busy and acquire with NOWAIT".

NOTE: There is work ongoing to improve how FME writes to Oracle.

One of our customers came up with this idea to circumvent this problem. As he mentions it is not ideal but it does the job. A possible solution is to use the WorkspaceRunner and spin off another workspace. The first workspace will disable the foreign keys using the pre-SQL statement. The calls the workspace with the WorkspaceRunner to do the inserts. The called workspace will complete successfully - and also commits the transactions upon finishing. Once back into the first workspace FME completes the translation and will enable the foreign keys using the post-SQL Statement. Our Oracle database integrity is happy and so is our FME user.

This resolution would allow you to maintain the performance writing to Oracle - important with large numbers of features.

Resolution 2

Another resolution is to make use of an Oracle Writer directive under the advanced parameters within the FME Workspace. It's called "Features To Write Per Transaction".

The root cause of the problem is related to a commit instruction not being sent to the Oracle Database on the transaction. By default this value is set to 1000 and FME will only issue a commit once this has been reached. If we set this directive to 1 we'll issue a commit for every feature. This will get around original issue all together however this method will have an impact on performance especially with large numbers of features as anyone can appreciate that is familiar with Oracle

Another Oracle Writer directive to consider for this workaround is "Feature Per Bulk Write". By default this is set to 200. This means that FME will send 200 features to Oracle at a time, wait for a response and return a message to FME i.e. Inserts are successful and FME will continue. It will be necessary to set this to 1 as well and again a further impact of Oracle writing performance will be realized.

As mentioned we are working to improve this area. Once we have a better method we'll be sure to update this article and pass along the information.