Oracle APEX REST can be a pain if it is not working as expected. Today was the day we delved deeper to REST to why data source is not synchronised to the target table. It was another day of hair pulling and lots of testing to figure out how synchronisation should be working for APEX.
Synchronisation is One Way
There is a misconception that synchronisation will keep the target table to be similar with the local table. This is the mindset we have while troubleshooting. However, we finally realise that the synchronisation is to local table and not vice versa. You should not change the local table and expect it to be synch back to your target table. The synchronisation type also shows that it is one way with 3 types – Append, Merge and Replace.
Design for Two Way
The synchronisation approach could mean that the local table should not be used as a transactional table. This will mean that you need to design your application for a two way data transfer between your local table and target table. One method is to direct REST POST to your target table and let APEX auto synch to your local table. Another method is to separate your transactions to be different from your local table. However, we will need to test this approaches in details.
Synchronisation can be straightforward if this is a direct table processing. If there are conditional logic and preprocessing, you will hit synchronisation issues. This is because of the one way synch from target table to your local table. So, it is back to drawing board for design of two way synch.