After weeks of tearing hair over Oracle Apex and REST, I finally understand the basic approaches of using REST in Apex. Now, I finally can have fun with Apex Dashboard. Transactional data can now be presented nicely with Apex Dashboard. This is not to be mistaken with analytical Dashboard like Tableau. These are my review of Apex Dashboard which I manage to setup within a day or two.
Why Apex Dashboard?
Data always comes in two flavors: a list or diagram. Thus, it is nice to see dashboard as a standard template in Apex. We do not really need complex dashboard like Tableau. This is because we are dealing with operational data. Therefore, a chart presentation in dashboard format can easily relate to the user. A flexible part of Apex dashboard is the ability to use SQL. Viola! That is why I can churn out a decent dashboard within a day!
Agile your Dashboard
The quick and lightweight dashboard suits operational users and agile method. I can easily pilot the dashboard and update user requirements with ease. The rapid iteration allows users to visualise the data in an efficient manner. Sometimes, I even redo my dashboard quickly because the older version is not to my liking. You can even mix and match dashboard in region in Apex page.
It is good to have a tool with simple lightweight features like dashboard. I do not need to separate dedicated dashboard tools for operational users. Finally, the dashboard template adds a nice additional and visual appeal to users. After all, we like to be appealed with good UI and visual data.
Today, I got a rude shock when our Oracle Apex workspace display this error ORA-20001. Thankfully, this seems to be a common occurrence when setting up REST.
ORA-20001: You have exceeded the maximum number of web service requests per workspace.
There are actually two solutions to tackle this issue. Interestingly, the solution are applied differently. The first solution is to login Apex Instance with admin. You can select administration services link below the standard workspace login.
Go to Workspace > Security settings > Workspace Isolation > Maximum Web Service Requests
Increase the number for maximum web service requests. That will solve this issue. However, you may not find this option in the UI. If this is the case, you will need to amend this parameter with SQL apex_instance_admin functions. Start your database console for your apex autonomous database.
To view the parameters, you can run below SQL command.
(‘MAX_WEBSERVICE_REQUESTS’) as max_service_requests from dual;
To amend the parameters, you can run below SQL.
(‘MAX_WEBSERVICE_REQUESTS’, ‘500000’); — increase to 500000
To our relief, this works and I wonder why is this necessary. REST is a common requests and perhaps this is good from a security standpoint. However, the error is too technical in nature to be solved by standard user. Another error to clear and we are good to go.
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
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.
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.
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.
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 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.
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
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.
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.
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!
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.
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!
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.
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.
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
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.