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

myrestpoint/getresult/?q={{“myid”:”$eq”:”123”}}

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_DEBUG

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.

APEX_DEBUG.ENABLE (
p_level IN T_LOG_LEVEL DEFAULT C_LOG_LEVEL_INFO );

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!

Troubleshooting APEX REST Synchronisation

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.

APEX REST Data Synchronisation to Local Table

Oracle APEX has an interesting feature to let you synchronise your external REST API to local database. This helps in caching and logic processing in your APEX. You can easily enable this feature without coding.

A Plus for APEX

The REST data synchronisation feature is a plus point for developers to use APEX. This is because it is common to consume or send processed data via REST API. Data replication for faster read access is also a useful feature. After learning about this feature, I find that it is very simple to enable data synchronisation.

Maintenance

It is not difficult to maintain this feature when the data can be cached to local database. You can also schedule refresh at different time interval. Majority of the configuration can be maintained via APEX page. It is important to take note of the following points for future maintenance.

  • Response JSON format
  • REST endpoints
  • Primary key
  • Field type
  • Parameters

If you are looking for an application for consuming multiple REST data sources. You can trial APEX data synchronisation. This feature is really useful to setup without additional coding.

RAD with Apex

Rapid Application Development (RAD) is around since the days of Visual Basic (VB). Since then, there is a preference of lightweight and low code framework with Agile approach. APEX (Application Express) is one such framework offered free with Oracle database. What does this signal for heavyweights like Spring or J2EE framework? Should you switch to low code like APEX?

Ease of Maintenance

The key push factor to switch to low code framework like APEX is the ease of maintenance. This helps users to focus on solving business problem rather then creating technical solution. Changes can be made and deploy quickly. This suits high complexity and exploratory problems where scope is largely unknown.

Business Driven Skillset

Low code environment places emphasis on outcome instead of being expert in thr programming language. Thus, platform like APEX suits users who are not technically inclined to setup entire infrastructure like servers and database. This emphasis business driven skillset like problem solving instead of technical skills. Users can also realised business solutions quickly with low code deployment.

The switch to low code platform like APEX will be growing. This is driven by the need for agile solutions to solve changing requirements. Usage of APEX also allows citizen developers to co-own and maintain the development.