Data Synch for Cloud

Data synchronisation for Cloud will be a standard because of residing data in different platform. The ideal synchronisation timing is always real time. Unfortunately, this can be costly and unrealistic. This is because data changes only happens for a particular moment. Thus, a full synchronisation is not efficient. How should we design for real time synchronisation?

User Behavioural Pattern

All data went through a lifecycle process from creation to modification and/or view only. A way to synch your data is to understand user behavioural pattern. This helps you to add in your trigger when there are user actions. The trigger to synch is usually to maintain modification consistency across the source and target system. It is also wise to close off the data and move these data as read only. That way, costly modification will not be needed for these data.

Scheduled Synchronisation

The most common synchronisation is schedule job. This method is brute force approach and helps ensure data integrity. However, it can be costly if data grows exponentially. It is usually cost effective to synch for changes instead of a full data synchronisation. A targeted schedule synchronisation is the most effective.

User behavioural patterns help to determine the data synchronisation you required in Cloud. A full synchronisation is costly and you should add filter to synchronise efficiency.

RFP 101

Request for Proposal (RFP) is a good information gathering process. It is also one of the most annoying part in acquiring a new business. For the incumbent, RFP sounds like a formality because they have home advantage. Thus, RFP plays an important role in soliciting information and understanding of the vendors.

Setting Expectations

RFP is a good way of setting expectations. Sometimes, these expectations can translate to answers that you may not have thought of it. There are also instances where RFP is seeking answers to all available aspects. This will make you ponder the scope of RFP. Thus, you must always make use of this stage to align expectations. An experienced team in RFP will know how to clarify and balance expectations leading to a higher chance of success.

Template your RFP Responses

It is surprising to see many teams scramble to answer RFP at the last minute. RFP is a pretty standard process with minor variations across the board. If your team is constantly dealing with RFP, it is worth investing to template your RFP responses. There should be a cookbook for dealing with RFP. You should not approach each RFP as though it is new. There should also be a set of common clarification to review the RFP.

Creating and responding to RFP will get easier as you build up your templates and knowledge base. Unfortunately, not all teams are well adept to handling RFP. It pays to seek advice if you do not handle RFP often. With practice, RFP will be a breeze to win!

Why I Need a Cloud Platform

It is my third year of having a cloud platform. Year 1 happened due to major project. Year 2 was exploratory and I started to use simple features like object storage. It was only on the third year that I managed to explore and build an entire cloud architecture. There is no turning back because Cloud platform has provided me freedom to Agile and configured applications quickly. These is a reflection on why I need a Cloud Platform.

Self Service

The reality of self service finally reached the IT community. That self service comes in a Cloud Platform. As product owner, we usually need a box in the past to initiate any new product features. Such request takes a long time from procurement to deployment of the application module. All these efforts can now be self service at a cloud platform. You can quickly spin up a compute within minutes. You can even setup entire eCommerce architecture in a cloud platform.

Finally Agile

Another favourite advantage in having a cloud platform is to be fully agile. I am no longer constrained by dependencies like network, computes or storage. These are now easily available with cloud platform. In another words, you can agile innovations with high uncertainty. You can also RAD (Rapid Application Development) or DevOps your changes because cloud is configurable.

The freedom of being agile and ability to self service are key reasons on why I will continue to need a cloud platform. If is also rare to find organisations that are still on-premise. I am excited that the next years will be spend to migrate the needed to cloud platform one way or another!

REST and Filter

I just found a simple solution to improve the latency for REST in Oracle APEX. One way is to set filter criterion in REST GET during the data synchronisation. You can use query string “q” in the parameters to filter your GET results like SQL. E.g. myid=“123” is equivalent to


Targeted Synchronisation

Unlike previous synchronisation method, setting these criteria helps to reduce amount of data. A GET of 500 records typically loads within 10s. At the UI, 10s is a lot of time to consider. Thus, it is advisable to keep your GET in less than 5 seconds. In our design, I changed to this approach of setting up filter in the query parameter to optimise synchronisation. This help to shaved 7s from 10s.

Further Tuning

The REST is best kept simple for speed. Therefore, you should not have complicated extraction for GET. Adding validations during the REST transmission is also a big no. Each milliseconds adds up and creates a slowness slowness effect for the UI .

It helps a lot to add filter for GET during the synchronisation. You will need to be careful in each stage from GET. Everything will adds up if you are not careful with latency.

Time to REST for APEX

Creating and setting up REST API in Oracle APEX is becoming the easier part. After deploying the REST to APEX, it is “time to REST”; or so I thought! The coding inside REST PLSQL is creating a latency. This is because I added another procedure call within the PLSQL. For starter, the issues feedback is that UI did not refresh when user create or update the data. A closer look at APEX debug shows error 504 gateway timeout.

Debugging 504 Gateway Timeout in APEX

Till this day, HTTP status codes are the most cryptic in nature. This is because it can point to a variety mix of root cause. Previous unit test did not reveal any issues pertaining to this timeout. This happens when more testers have starting testing the system. After that, the 504 gateway timeout occured and APEX REST did not refresh the UI correctly.

Track and Trace 504

In order to track 504 gateway timeout error, we added a series of debug logs on APEX and target application. We saw that the payload was REST correctly to the target system. We also found that the target system was able to complete the PLSQL. So, what went wrong? Thus, we start to trace back the changes for the past 24 hours. It is a painstakingly process and we need to agilely rollback and retest the UI. We finally found the root cause when we comment out a procedure within the REST PLSQL. The codes have introduced a latency that causes a timeout to happen at the API gateway. Therefore, a quick fix is to increase the timeout at the API gateway.

Many will tear their hair and be sleepless over HTTP error codes. I am certainly one of them. This is a simple case where full testing will introduce latency and REST http errors. Unexpected behavior includes failure for APEX procedures to run its course. The result is UI or refresh issues. You can also see slowness in saving your data. Alas! It is time to tune for REST and there goes my REST time too.

APEX Format URL and Passing Parameters for Outside Context

Oracle APEX URL is a strict syntax. This is something I realise after days of trying to link to Apex page. You cannot shift the position of the format as it is using f?p syntax to pass the arguments. This includes parameters which you wanted to pass to the page. This is even more challenging if you want to pass parameters from outside context.

Passing Parameters

A deformed URL syntax will still load Apex page. However, you will find that the parameters cannot be received. You can check via the Javascript in Apex by reading the parameters argument and console.log the values. Another reason is authenticated page vs public page. It seems that the parameters can only be passed via public page.

Passing parameters from outside context to Apex will require a different configuration. For the time being, public page is friendly to receiving these parameters. I will still need go search on how to get it working via authenticated page.

APEX REST Enabled Interactive Grid

Hurray! I finally REST enabled Interactive Grid for Oracle APEX. It took a week to realise Interactive Grid cannot use REST data source fully. You will need to custom PLSQL for DML processing to save Interactive Grid data. Thanks to this helpful blog for giving a good example on how to do it. The coding is much easier than direct ORDS codes as APEX provide utility API to exec the REST data sources. You can refer to the blog for implementation but I will share the pitfalls to look out for.

Turn On Debug

The most important steps is to turn on APEX debug and enable level 8 debugging. You must also add debug log to your procedure and PLSQL code. Level 8 give you detailed logging. You can always switch back to level 4 when you find the error. Debug will show the JSON payload when you exec the REST service. Do copy this payload to Postman to test that it is formed correctly. A common mistake is the typo and missing quotes in the JSON format.

t_log_level := 8; — Application Express engine: messages when procedures/functions are entered

c_log_level_engine_trace constant

Validation Checks

REST JSON have no data types formatting during http transmission. Thus, you need to format and validate your data before you send and receive at target. If you are using date types, you will often hit character to number conversion error. Be sure to format your date to the desired date mask “DD-MM-YYYY H24:MM:SS” and convert to varchar. Do be careful of null values and put in validation checks to prevent this error. You should also validate for unique primary key. REST status code is notoriously not helpful. Do add loggers at TARGET system to check where the errors occurs.

It will take a few version upgrade before Interactive Grid can be REST out of the box. In the meantime, you just have to follow this custom PLSQL method for REST connectivity between APEX and target system.

APEX Debug 101

Today, I finally figured out how to work the APEX debug. It was not straightforward due to a little fine print that we need to do get it working. This is a pretty good reference that I used to get started. However, I only manage to make it work after a few hours of tweaking and a single line of essential code!


APEX have a debug utility functions. This helps you to instrument your PLSQL code to log your errors and messages. The most important debug I find is ENABLE procedure and the level information. Without this debug function, you will not be able to see your logging appear.


Another key reason why I am not able to see my logging appears is the p_level setting. You need to read the fine print carefully below.

For example, setting p_level to 2 logs any message at level 1 and 2.

Initially, I did not think much of the level as this is usually not needed for dbms_log. I enable the p_setting to level 2. Then, I keep wondering why apex_debug messages are not showing. It is only when I set the p_level to 4. This means you can only see apex.debug only at level 4. The error constant is not a criticality view but have a secondary meaning in showing the types of debugging logs.

c_log_level_error constant t_log_level := 1; — critical error
c_log_level_warn constant t_log_level := 2; — less critical error
c_log_level_info constant t_log_level := 4; — default level if debugging is enabled (for example, used by apex_application.debug)
c_log_level_app_enter constant t_log_level := 5; — application: messages when procedures/functions are entered
c_log_level_app_trace constant t_log_level := 6; — application: other messages within procedures/functions
c_log_level_engine_enter constant t_log_level := 8; — Application Express engine: messages when procedures/functions are entered
c_log_level_engine_trace constant t_log_level := 9; — Application Express engine: other messages within procedures/functions

APEX have a cool utility APEX_DEBUG. However, you need to enable as least level 4 to view meaningful logs in your PLSQL. I also notice that logging level 9 slows down your application a lot. Do remember to turn off the logs for production. With logs, we are back in business to troubleshooting APEX!

APEX Interactive Grid and REST

Oracle Apex Interactive Grid is a cool feature. You can quickly deploy CRUD (Create Read Update Delete) in a matter of minutes. Unfortunately, it does not work well using out of box REST data source. We realise this after a week of troubleshooting as to why our REST endpoints keep failing.

REST Enabled

I will be looking forward to products with REST enabled capabilities. In the Cloud world, it is common to find data sources with various REST endpoints across different cloud platforms and/or on premises data sources. Thus, it is surprising to see interactive grid that is not fully REST enabled.

Custom Coding Required

You will need to REST enabled interactive grid with PLSQL coding. I found a step by step blog with an example to do so. However, I am still trying to get this to work as I have faced a variety of issues. In summary, the solution is to use custom PLSQL code to invoke POST, PUT or DELETE REST methods.

The challenges of low code and product like APEX is the understanding on constraints of each feature. You can only learn this via extensive usage for your use cases. In this case, we realise interactive grid are not fully enabled for REST data sources. I will not wish myself luck to get it working with custom PLSQL. Fingers crossed!


The standard REST methods in ORDS (Oracle REST Data Services) are GET, POST, PUT and DELETE. We usually started testing REST with GET because it is usually the easiest for SELECT statement. POST will be next for INSERT for new creation. The complex PUT will be the last for any UPDATE process. DELETE is seldom provided to preserve data integrity. So, why should you leave PUT to the last? This is because no modification process often requires exception flow and handling.

Photo by Ethan
Handling PUT

You should relate PUT to modification process. REST does not have constraints to modify data unless you add the conditions. There are two approaches to handle PUT. One way is the trigger that will allow user to modify the data. Audit trail should be handled at source and/or target application if you want to keep track of the changes. Another way is to set conditions in your PUT services to prevent unauthorised modification.

PUT Response

A key characteristic of PUT is primary key (PK). You will need the PK to amend the correct data. So, what happens if PUT cannot find the PK? There are two ways to handle the PUT response.

  1. You can give a valid response or 201 status if PUT is successful.
  2. You can choose to INSERT if PK cannot be found.

PUT is the last and most complex REST service that you may want to expose. Handling PUT and its responses will need careful consideration on how you want to manage data modification. In some cases, you may choose to omit PUT service to preserve data integrity like DELETE.