ORA-20001: You have exceeded the maximum number of web service requests per workspace.

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.

select apex_instance_admin.get_parameter
(‘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.


Oracle Digital Assistant

Oracle Digital Assistant (ODA) is the platform I mostly used for my Chatbot testing. Overall, it is functional and easy to setup. Usually, this means I can configure and deploy Chatbot within a day or two. As with all platforms, there are pros and cons. This is a quick summary if you are interested in ODA.

What I Like

These are some features which I like the most.

  • Sample templates and tutorials to get started.
  • Easy integration to translation services.
  • Quick setup for some Channels integration like Teams and Web
  • Extension via web hook and custom components.
  • Versioning support
  • Easy switch of versions on different channels
What I would Like

This will be some common configuration which Chatbot platform should have for the future.

  • Languages without consideration to native or non native.
  • Configurable connection to REST API.
  • Configuration for login services.
  • Rich UI for chat messages.
  • More samples chat with REST custom components.
  • Configuration for map services, camera, QR scan or document storage.
  • Web SDK for PWA (Progressive Web App)

In summary, ODA serves well to setup Chatbot quickly without much fanfare if you are creating basic needs like knowledge base or technical support. However, you will require advanced technical knowledge to integrate different services to ODA as they are still quick code driven than configuration.

OTM Dilemma

OTM (Oracle Transportation Management) can be a big dilemma if we are moving to cloud or upgrading. It is a configurable system that allows customisation from small degree of change to major one. A major part would be your decision to upgrade or migrate to cloud from the amount of configuration or customisation done to OTM.

Photo by Ethan Kwok
Cloud Dilemma

Many organisations would prefer to migrate to cloud for future support and upgrades. separate the customisation Cloud, like its name, really is cloud. You cannot foresee what will happen in Cloud until you are in Cloud. Your risk appetite will need to be high if you have done a lot of customisation on OTM. Prior your move to Cloud, a major task is to separate the customisation into REST services. How much time and effort will you invest? Should you forgo your customisation instead?

Upgrading Dilemma

Upgrading OTM major version can be quite a slow painful experience. The 6.4.3 is the last available version that is non Cloud based. The decision not to move to Cloud usually lies with the amount of customisation done. The biggest changes from old version to 6.4.3 version are architectural, user interface (UI) and report server. The dilemma comes in the sequence of upgrade you want to take. Report server is now mandatory after the removal of embedded reports. Should you move your embedded report first or do together with upgrade? How much training period should you allow for the new UI? How much time will it take to fix your customisation during the upgrade?

OTM upgrade or migration to cloud is a painful dilemma. There is always a temptation to status quo the version due to the massive efforts to factor risk and change management. In many cases, you need lots of patience and alternate plans while you are upgrading or migrating.

Oracle Email Delivery Services 101

I was testing Oracle email delivery services when I realise certain constraint on the receiving end. One of them is the email security policy of the organisation. This can be quite painful where email services are blocked constantly. Thus, you should not rely on email as your only mode of communication with your customers.

The Good Part

It is pretty simple to setup and test email delivery services. Oracle provide a sample send email on GitHub to be use together with its Cloud functions. Disclaimer that I mention easy after spending days to understand Oracle Cloud Functions and API Gatway. So, I will suggest that you start studying Oracle Functions and API Gateway first before testing Email Delivery Services.

The Constraints

Some constraints I found while testing and it a stark reminder to email delivery failures. A simple list here:

  • You cannot use public email like gmail as your sender.
  • You will need access to your email configuration to update your SPF records.
  • Majority of organisations blocked relayed email.

Some improvement I will like is the ability to setup its own simple mail sender or access to public email as sender. Other features that can be added will standard email test can be trigger on the console, template for responses. The test trigger will remove the need for functions and API gateway setup.

OTM Status Inital Value

Today I have an shocking discovery on a configuration of an initial value setting for OTM (Oracle Transportation Management) status. The initial value is set as Error! It is really amazing to think how this could be an initial value. Of course, valuable hours are wasted to debug an initial value which is actually correct by its initial“error state”.

Happy Path Design Approach

The state diagram is fundamental of engineering and software lifecycle if you are familiar. There should never be an initial value as error or exception. One must alway think of happy path and start with a perfect flow. Starting with an erroneous state gives confusion to users and SME (Subject Matter Expert), as we do not know what is wrong when nothing have started. Be Optimistic and always go for Happy Path Design Approach.

Design by Norms, Think like a Traffic Light

The best solution design is design by Norms and thinking like a Traffic Light. Green is a Go, Red is Stop and Yellow is proceed with caution. Thinking like traffic light greatly reduce learning curve from users and have high system adoption rate. It is one of the best design where users can easily learn with no user manual or training. Do not break the norms and do fanciful solution. You will end up confusing and these type of solution always get discarded due to its complexity.

Agile TMS with DevOps

Unknowingly, I have spend more than 5 years with TMS (Transportation Management System) using OTM (Oracle Transportation Management) system. Overall, I enjoy the ease of configuration to deploy Agile solution using technique of DevOps. The control of global TMS solution also create high degree of adaptability for the team.

How to Agile your TMS
  • TMS have short transit window. It is favorable to adopt Agile.
  • Do design for change rather than design on sign-off requirements.
  • Configuration solution with custom plugins can cater for all TMS scenarios.
  • Determine your Agile components.
When to DevOps
  • DevOps complement Agile approach. This suits TMS because TMS contains many exceptions in the real world.
  • Use DevOps to close your loop in Agile implementation.
  • Consider to include monitoring tools or reports and analyse your data to determine your solution effectiveness.
  • TMS is usually 20% localisation. You may choose to DevOps on these localisation.

In summary, TMS needs a good product platform. You will also need to train your your TMS team to be Agile and DevOps ready.

OTM Object Locks

OTM (Oracle Transportation Management) Object locks have been my nemesis lately. You can see my past battles with refactoring and code quality. In migration or upgrades, direct copy of old packages from old to new domain often do not work as expected. Of course, you will only realise it when you hit object locks.

Signs of Object Locks
  • If you see very long PLSQL packages, it will mean the codes will take a while to travel from end to end. Any delay will lock object or tables giving the user a non responsive feel in the UI.
  • Many UPDATE SQL are seen in the agent or PLSQL.
  • Locks in Agent are used in Modified agent.
  • Copying of data from different objects like Shipment to OR (Order Release).
  • Removal of PLSQL packages in Agent and DSU (Direct SQL Update) to bare minimal, especially Modified Agents.
  • Reduction of codes in PLSQL. Delete or comment out old codes.
  • Reduce your UPDATE to a single point.
  • Add exception handling in your PLSQL to allow graceful exit of your PLSQL.
  • Conduct unit testing to each Agent or procedures before enabling them. If in doubt, remove them.

It is a painful process and huge amount of mandays are spend. Of course, lots of practice and hair tearing are done before you realise these tips. Hopefully, they are of help to those doing migration or upgrades of OTM.

Oracle ODA and Apache FreeMarker

This is part 3 of my foray into Oracle ODA (Oracle Digital Assistant). After the intro in slaying ODA pizzaBot and then subduing Composite Bag, I managed to reach into a new uncharted water called Apache FreeMarker. ODA uses FreeMarker to display, evaluate or format the variables and entities.

Ok, so we are getting you a ${pizza.value.PizzaSize?lower_case} ${pizza.value.PizzaTopping?capitalize} pizza.

Biggest Challenge in FreeMarker

Handling missing values is the biggest challenge for me! I literally tear my hair trying to figure out how to handle missing or null values. Needless to sad, Oracle ODA documentation is not helpful and I have to refer to Apache FreeMarker documents. Many scripting languages allows you to display the variables even they do not exist. Unfortunately, FreeMarker is more strict and helpfully dedicate an entire section. Like all coding documents, help is cryptic. It takes a lot of trial and error to figure out where it goes wrong with its critical ! and (( )).

Default value and Formatting

FreeMarker have a funny way to evaluate default values. It is easy for simple variables in ODA or you are not formatting the variables. However, things gets very tricky when you are using composite bag with formatting. Below are some cheats for you to figure out the correct syntax.

${pizza!’Not Set’} – Simple variable with no formatting and default value after !.

${(pizza.value.PizzaSize)!’Not Set’} – Composite Bag with no formatting with ( ) to encapsulate the (composite bag variable)

${((pizza.value.PizzaSize)!’Not Set’)?upper_case}} – Composite Bag with formatting with ( ) to (encapsulate the variable and default value) and another ( ) to encapsulate (both values for formatting).

Hope these tips will save you a lot of time in formatting with FreeMarker in ODA. Just in case you are wondering, validation do not flag FreeMarket expressions. You only get the error when you test the skill. Below is a standard error shown due to above.

error is: freemarker.core.InvalidReferenceException: The following has evaluated to null or missing: ==> eReadyPickUpDateTime.value [in template “FlowTemplate” at line 1, column 324] —- Tip: It’s the step after the last dot that caused this error, not those before it. —- Tip: If the failing expression is known to legally refer to something that’s sometimes null or missing, either specify a default value like myOptionalVar!myDefault, or use when-presentwhen-missing. (These only cover the last step of the expression; to cover the whole expression, use parenthesis: (myOptionalVar.foo)!myDefault, (myOptionalVar.foo)??

Oracle ODA Composite Bag

After pulling my hair in Oracle ODA intro, I strive onwards and continue my foray into the deep pit of ODA (Oracle Digital Assistant). Today is my first attempt to try out the ODA entities. There are variables in ODA that are described as Entities like real world data format. In this module, there is a cool feature called Composite Bag.

Source: Oracle

Composite Bag allows you to fill in variables grouped in a “bag”. There is also a neat feature to let you auto resolve entities via the chatbot. It really sounds very cool but getting it to work is harder than I thought.

For example, a composite bag for a pizza might include entities for type, size, crust, and extra toppings. If a user enters “I’d like a large pepperoni pizza with a gluten-free crust”, the skill could extract “large”, “pepperoni”, and “gluten-free” from that input and not need to prompt the user for those values individually.


As this is a new concept to me, the sequence, flow and placement of the code is important. It takes me a while to reference the pizza template repeatedly and changing the variable to what you have. In the end, I manage to get the auto prompt working! Code wise, it is much elegant than my individual variables.

However, I hit issue to display the bag details. Grrr. I must retry pizza example to analyse how to display Composite Bag details. Another side note is that I have yet to test composite bag for REST or integration. That will be another battle!

Not sure if Oracle heard me, please include auto complete in the syntax and flag the flow error for YAML!

Oracle ODA Chatbot Development Intro

My latest “hobby” is playing with Oracle Digital Assistant (ODA) Chatbot. However, this is taking me longer than usual to get used to it. I will think that ODA is a simple configuration of ask and response. Sadly, it is still very code driven. Today is my nearly 3/4 of immersion day struggling and dedicated to master ODA.

Source: Oracle ODA
More Templates Please

I will love to have more templates to quick start my Chatbot beside ordering PizzaBot. By now, I am getting sick of looking at Pizza. Templates alway speed up development as we just need to change certain variables and tweak the flow. Oracle Help is not really very helpful too.

YAML flow error

Anyone playing with JS, NodeJs or PHP will be always expert in syntax troubleshooting? Seriously, I enjoy this loose coding too. For one moment, I and baffled by YAML flow error. YAML is suppose to be a human readable language. However, the error flagged is magician level. Guess what, the error is due to a missing close quote “. Level up!

Takeaway from Intro

In a way, it is a new paradigm way in trying code for a natural language. Somehow, I feel that the ODA environment is still seated in the old development model. I will prefer more drag and drop instead of mastering the syntax. It is definitely not for business or layman to configure. There is a sense of achievement to create the chat. I will get used with more days of development.