Multiple oracle writers: rollback on workspace failure

Hi @annetdeboer, did you try the writer parameter Features to write per transaction?

My initial observation is that you are using multiple writers - assuming we're using the term "writer" in the same way.

Can you not have a single writer that writes to multiple tables? I don't know about David's limitation (do you have a PR number David?) but I would think a single writer with multiple tables is more likely to rollback everything than multiple writers, each writing to a single table.

My initial observation is that you are using multiple writers - assuming we're using the term "writer" in the same way.

Can you not have a single writer that writes to multiple tables? I don't know about David's limitation (do you have a PR number David?) but I would think a single writer with multiple tables is more likely to rollback everything than multiple writers, each writing to a single table.

Hi @annetdeboer,

Well how about using the FeatureWriter and then validating the results via a follow up SQLExecutor?

This is something displayed in the FMEWT this year, so I guess the demo workspaces might be available somewhere and if not its quite straightforward to set up.

Aside from the rollback issue, did you look into the different Oracle parameters in the Readers and Writers manual? In particular I'm thinking about Features Per Bulk Write and Features to Write Per Transaction.

If you set Features Per Bulk Write to a high number, FME will cache the data in memory and send it to Oracle as a large chunk. Make sure it is the same as Features to Write Per Transaction and set that to a high number too. Then the data will only be sent to Oracle and committed when it is correct.

Both of these are Writer parameters (not feature type parameters) so if you only have one "writer" writing to several "feature types" (tables) then they may help. Only if you truly have multiple "writers" will they not help.

This is a known limitation when writing multiple tables using transactions in FME. I think that Safe is working on alternative strategies to improve it, but I have no idea what their timeline is.

If consistency is very important to you, I highly recommend you write your data into a temporary staging schema first, validate it for consistency and then transfer it into your production schema if all is ok. This is not only very robust, but also more in tune with database best practices when doing bulk imports.

The last transfer must be done in a single atomic transaction, which e.g. can be done using an anonymous block inside a SQLExecutor. 

Example (notice that you must not set FME_SQL_DELIMITER here):

BEGIN
  SAVEPOINT start_tran;
  INSERT INTO .... ; -- first DML
  UPDATE .... ; -- second DML
  BEGIN ... END; -- some other work
  UPDATE .... ; -- final DML
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO start_tran;
    RAISE;
END;