​Full disclosure, I have absolutely no experience in trying to convince my boss to try APEX or ORDS. I do, however, have experience in basking in the glow of clients who have reaped the benefits of implementing mission critical systems using these two no-cost options of the Oracle database.
 
TL;DR; If you are a boss, try out APEX and ORDS today. If you already have an Oracle database, it costs you nothing extra and the upsides for you and your business are tremendous.

​If you are a developer looking to convince your boss to adopt APEX and ORDS, then this post is for you. My goal is to arm you with the material you need to make a compelling case for adopting APEX and ORDS. I have broken the post into several major talking points. At the start of each talking point, I have a headline statement (for bosses with short attention spans). I then go on to explain my reasoning in a little more detail (for bosses that need some evidence).

​Data Centric - APEX, because of its architecture, is very data centric. Both the code and the data exist in the same database enabling zero data access latency. This architecture did no come about by accident; there are good reasons why your code should be right next to your data. I have been using APEX for a while now, but I love this new term ‘zero data latency’. Of course, this has been true since the days of HTMLDB. But in this world of super complex, mega frameworks that are so abstracted from the data they need a map to find it, it is very relevant today.
 
Stateless – Much of the scalability of APEX comes from its stateless nature. The API calls to the Oracle APEX engine use the standard Oracle database connection pool. This means that once an API call is processed and the response is sent back to the browser, the connection is returned to the database connection pool and can be used by any other request. The database sessions are only active when performing a request, otherwise, the they are inactive and do not consume any database resources. This allows APEX to handle tens of thousands of users simultaneously.

APEX is very stable, easy to upgrade and new features are coming thick and fast.
 
You may think stability and innovation don’t belong in the same sentence. When you are talking about APEX, however, they really do. I have never undertaken a large JavaScript framework project. I can imagine, however, that as a boss it is a little un-nerving heading into a multi-year project with half a dozen technologies, many of which may not exist by the time the project is over. APEX has been around for 15+ years and there is no sign of it going anywhere. You may think, “So what, Windows has been around for longer and it is still awful.”. APEX is different. The pace of new (and useful) features over the past 2-3 years (especially in the areas of UI, Single Sign On and integration capabilities) has been fantastic. BTW, there is no sign of that pace letting up. With multiple releases a year you get the option of up taking these improvements at regular intervals.
 
Hold on a minute, that doesn’t sound very stable. What I haven’t mentioned is that the APEX development team is world famous for providing upgrades that do not break your existing Apps. One of the ways they achieve this is by allowing apps to run in previous versions with ‘Compatibility Mode’. This means that in most cases, you can do the upgrade and then switch on new functionality in individual apps, as and when you need it. There are stories every month of APEX users who have upgraded from APEX 3 (released in 2007) to the current version without anything breaking. Can you imagine doing that with any other development framework?

Your users will love using APEX Applications!
 
If you had experience of APEX from version 4 (or before), boy are you in for a treat. Applications built with APEX look as good as any other framework and are quite frankly a joy to use. We are long past the dystopian days of automaton workers using systems they are forced to use. When people use Enterprise systems these days they expect more. While you may feel like dark mode is a fad, Apple is about to release it across all of the Operating Systems. Believe me, your Enterprise users will be using dark mode soon enough. Wouldn’t it be great if your development platform could make dark mode happen at the flick of a switch? APEX has your back. Dark mode is already available in APEX Builder and will be coming soon as an end user theme style. It goes much further than that too. APEX’s Universal Theme delivers UI components that would otherwise consume hundreds of hours of custom development. For a peak at what is available, check out the Universal Theme demo App https://apex.oracle.com/ut
 
Finally, with accessibility concerns finally being taken seriously, it is good to know that the APEX team has baked accessibility checks right into the product. They even have accessibility tested themes available. Check out this video for more details on Accessibility in APEX.

​APEX and ORDS provide you everything you need to host and consume REST based web services to meet all of your integration needs.
 
Oracle REST Data Services (ORDS) has two roles in an APEX world. One is to service APEX pages to end users and the other is to host REST based web services. The ability to host REST based web services based on SQL and PL/SQL is extremely powerful. It allows you to provide external systems secure access to the data in your Oracle database. This can be useful for integrations and even for hosting web services to be consumed by native mobile applications. See our post on ORDS for details.
 
APEX comes with a number of features to facilitate consumption of any REST based web services. For example, Web Source Modules provide a wizard-based approach to configuring components that allow you to create reports and forms directly on web services. This extends the reach of APEX outside the database and prevents un-necessary syncing of data to and from other systems. 

With APEX Social Sign-In you can use just about any OAuth identity provider to authenticate your users.

Authentication can be a complex proposition. If you store usernames and passwords yourself you really need to know what you are doing. The last thing you want is for your user account data to be compromised. It is generally far safer to outsource this task to a trustworthy identity provider such as Okta or Auth0. You could also outsource this task to platforms your users already use such as Office 365, Google and even Facebook. 

​By providing the ability to create custom authentication schemes, APEX has always provided flexibility in authenticating users. Custom authentication schemes allow you to create code that could use just about anything to authenticate a user. Since APEX Social Sign-In was introduced in APEX 18.1, APEX authentication has reached a whole new level. You can now setup declarative authorization schemes for OAuth-capable identity providers and APEX handles calling the necessary web services to authenticate your user. Taking Okta for example. Okta can now be your source of truth for handling all authentication in your company. All of your other systems (Email, ERP and your APEX Apps) can then use Okta to authenticate your users. See our post on APEX Social Sign-In for more details.

​APEX provides many useful features and utilities out of the box, that would otherwise require numerous third-party libraries.
 
To name a few:

• Out of the Box Authorization
• Multi-Language and globalization features
• Sending Emails
• Email Templates
• Extensible plugin framework with hundreds of available plugins
• Building and parsing JSON
• Excel and CSV parsing
• Zipping and Unzipping files
• JWT Handling
• String manipulation utilities
• and many more…

 
The benefit of having these utilities bundled with APEX are that fixes and enhancements are handled by Oracle and released with each new version of APEX. In contrast, having these types of utilities spread out amongst a number of providers means you get fixes when they feel like. It also means that as those providers drop by the wayside you are left having to merge new libraries into your applications.

​APEX applications can be hosted in the cloud just as easily as on-premise.
 
The APEX architecture lends itself well to running in the cloud and there are numerous options for hosting APEX in the cloud.
 
PaaS - There are turn key PaaS solutions (e.g. Exadata Express from Oracle) that host the entire APEX and ORDS architecture at a very reasonable price. All you need is a credit card and a browser to get started. See our post on using APEX to extend Oracle ERP Cloud.
 
IaaS - You can also install APEX and ORDS yourself on infrastructure from just about any cloud provider. This allows you complete flexibility to size the environment as you wish, but you will need to perform much of the maintenance (backups, upgrades etc.) yourself.

​APEX is building on its high productivity heritage to establish itself as a legitimate contender in the Low Code space.
 
APEX has always been known for allowing developers to build useful, scalable applications in a very short period of time. Over the past year and a half, more and more features have been added to establish APEX as a top contender in the low code pace. These features include wizard-based development of web service integrations and pre-built components that can be added to existing applications or used to build brand new applications. APEX is already at the stage where you can build fully functional applications starting from the upload of an Excel spreadsheet. These kinds of features are allowing APEX to break out of the IT department and into the business. It is here where (handled the right way), citizen developers can become productive with APEX with just a small amount of oversight from IT. 

​Many of our customers run Oracle e-Business Suite and have a number of PL/SQL developers on staff. How to leverage this talent and still provide your users with applications they will love using? An experienced PL/SQL developer can be up and running on APEX in a matter of days. Five years of Oracle database, SQL and PL/SQL experience gives you about 70% of the skills to be a high performing APEX developer. Sprinkle in a little JavaScript, CSS and web services experience and you are 100% of the way there.

​APEX and ORDS are no cost options of the Oracle Database.  If you own an Oracle database license you own APEX and ORDS.
 
I purposely left this item to last because in my opinion, APEX and ORDS stand on their own against any enterprise-ready development platform. Having said that, if you own an Oracle database license you really should be using APEX and ORDS because it isn’t going to cost you a penny extra. In addition to the no extra cost for the software, you will also need very little additional hardware.  A VM with 4GB of RAM and 15GB disk is more than enough to host ORDS, which is the only other component you need.

It’s very hard to write a pithy conclusion to summarize everything I have said in this post. There are also, no doubt, a number of important APEX benefits that I have not included. If you read this post and you are a developer keen to bring APEX and ORDS into your organization, I encourage you to use some of this material to make a case. If you are a boss and you are looking to adopt a development platform that will deliver real value to your business, then I urge you to visit the following links to learn more about what APEX and ORDS can offer your organization:

• APEX Shortcuts
• APEX Office Hour Videos
• APEX Learning

​Oracle REST Data Services (ORDS) provides many options for authenticating users. These include OAuth client, APEX User, Database Schema User and OS User. While it is important to ensure your ORDS web services are secured, you also need to consider what a client has access to once authenticated. As a quick reminder, authentication confirms your identity and allows you into the system, authorization decides what you can do once you are in. In this post, I will dive into roles and privileges and explain their part in authorizing access to ORDS web services for OAuth clients.

​Let’s start with a diagram to show how roles and privileges fit into the overall OAuth security model for ORDS.

A role acts as the link between one or more users and one or more privileges. You assign a role to one or more OAuth clients.
 
A privilege details what is secured by that privilege. When creating a privilege, you can secure either an entire module or secure based on URL patterns.
 
A URL pattern is part of the full URL for a web service or group of web services. If you have a module called ‘ev’ then you can secure all services in that module using the URL pattern ‘/ev/*’.

Take the following three modules in an ORDS enabled schema called ‘XXORDS’.

Let's say we want to do achieve the following:

• Allow services in the twillio module to be completely open (i.e. no authentication)
• Protect the 'utils' and 'ev' modules
• Allow the OAuth client ‘ADMIN_CLIENT’ access to any services in the 'utils' and 'ev' modules
• Allow the OAuth client ‘EV_CLIENT’ access to services in the 'ev' module

 
Note: Prior to creating any privileges, all of your ORDS web services will be publicly accessible.
 
Roles
Based on the above, we will need to create two roles ‘ADMIN_ROLE’ and ‘EV_USER_ROLE’.

We will also need to create two privileges. One to protect web services in the ‘ev’ module and one to protect web services in the ‘utils’ module.

Privilege 1 - EV_DATA_ACCESS
While creating this privilege, we will at the same time, link the privilege to both the 'EV_USER_ROLE' and the 'ADMIN_ROLE' roles. An OAuth client associated with either of these roles will be able to call the charge points web service:

• https://example.com/ords/xxords/ev/charge_points

​Note: The pattern ‘/ev/*’ starts after the base URL for your ORDS enabled schema. In our case, this is anything after https://example.com/ords/xxords.
 
This is what the privilege looks like from SQL Developer:

Privilege 2 - UTILS_DATA_ACCESS
While creating this privilege, we will only link the privilege to the 'ADMIN_ROLE' role. Only OAuth clients associated with this role will be able to call the 'dbinfo' web service:

• https://example.com/ords/xxords/utils/dbinfo

​If we stopped here, then there would be no way to access any web services in the ‘ev’ or ‘utils’ modules. The act of associating a privilege with a module or URL pattern effectively locks them down. We need to create OAuth clients and link them to the roles we created so that these clients can access web services in these modules.

OAuth Client 1 - EV_CLIENT
This script creates the OAuth client EV_CLIENT using the grant type ‘client_credentials’. It then grants the newly created client access to the EV_USER_ROLE. At this point the EV_CLIENT can call web services in the ‘ev’ module.

​OAuth Client 2 - ADMIN_CLIENT
This script creates the OAuth client ADMIN_CLIENT using the grant type ‘client_credentials’. It then grants the newly created client access to both the EV_USER_ROLE and the ADMIN_ROLE. At this point the ADMIN_CLIENT can call web services in the ‘ev’ and ‘utils’ modules.

In this post, I demonstrated how to use roles and privileges to provide fine grained access to your ORDS web services. ​​This is important when ensuring OAuth clients only have access to the web services they are supposed to. Unless your web service has to be public (e.g. if it is a Web-hook) then you should seriously consider adding OAuth security. 

Click here for more on OAuth security in ORDS.

​A new configuration file parameter showed up in Oracle REST Data Services (ORDS) version 18.3. The parameter is called ‘procedure.rest.preHook’. Whilst this may seem like a pretty innocuous change, I believe it opens up a number of new possibilities for ORDS, which I will explore in this post.

​The parameter essentially allows you to identify a PL/SQL procedure which is called by ORDS prior to every web service call in a particular connection pool. This includes GET, POST, PUT and DELETE services. This gives you a spot where you can execute any code you want just before (and in the same database session as) every web service. ORDS expects the function to return a Boolean, and it will automatically return a HTTP status of 403 ‘forbidden’ and stop the execution of the handler code if the function returns a value of false.
 
Per the documentation, the parameter ‘procedure.rest.preHook’ : “Specifies the function to be invoked prior to dispatching each Oracle REST Data Services based REST Service. The function can perform configuration of the database session, perform additional validation or authorization of the request. If the function returns true, then processing of the request continues. If the function returns false, then processing of the request is aborted and an HTTP 403 Forbidden status is returned.”

Adding the parameter to the ORDS parameter file is pretty straight forward. Add the following entry to your defaults.xml (or add it to a specific connection pool if applicable):
<entry key="procedure.rest.preHook">xxrest.rest_utl.pre_hook</entry>
 
Note: The above entry assumes I have a package called rest_utl with a function called pre_hook, which I created in the schema xxords. Here is the package specification for the above example:

​Grants
The procedure needs to be executable from all schemas that your ORDS services are run from. For example, if you create a Prehook function called REST_UTL_PK.PRE_HOOK in the schema XXORDS, and you are running ORDS services from schemas XXORDS and XXREST, then you must grant execute on XXORDS.REST_UTL_PK.PRE_HOOK to the schema XXREST. If you do not, you will get the following error when running services from XXREST:

​Logging ORDS activity to a database table is probably the subject of a longer post. In short, there are two primary reasons you may want to log invocations of your ORDS services. The first is to track usage. Knowing what methods and resources are being utilized is extremely helpful. You can keep track of which services are and are not being used, which clients are using which services etc.
 
The second reason is to track, and trouble shoot errors that occur during web service execution. Obviously, the Prehook function won’t help us with logging errors raised by our web services because at the time it is called, the service has not yet run. It can, however, be used to perform all of our usage logging.
 
In some cases, we could already do this, by adding the log table insert to our handler logic. This did not, however, work for GET services where our handler was a SQL statement.
 
Let’s look at how we could go about adding table usage logging for all our ORDS services using the Prehook function.

​First you will need to create a logging table with columns that can capture information about the service invocation. Let’s consider the following table called rest_log:

​This table is by no means comprehensive but serves the purpose for this example. Our Prehook function to populate this table could look something like this:

​That’s not a lot of code to log every ORDS web service call!
 
You may notice in the above example that I am tracking a custom HTTP Header ‘X_USER_NAME’. This is to illustrate that you may want to introduce custom http headers so that you can track additional attributes about the client.

​Setting database session context is a particularly good use of this parameter. Let’s say you are operating in an Oracle e-Business Suite (EBS) environment. Each call to a web service needs to set the EBS session context based on the user and responsibility. This ensures the correct user and operating unit is used when querying data or calling EBS public APIs to create and update records. This is typically performed using the EBS API fnd_global.apps_initialize.
 
Before now, we could do this for POST services by including a call to fnd_global.apps_initialize at the start of the handler logic. This was not possible, however, for a GET based on a SELECT statement. This meant we had to build GET services using a PL/SQL block and emit the JSON manually using APEX_JSON.
 
The Prehook function allows us to call fnd_global.apps_initialize at the start of the session and have that context applied when the web service is called. This is much cleaner.

Of course, there are use cases outside of the EBS world where you need to set session information especially if you are using VPD (Virtual Private Database) or something similar to perform row-based security on your data.

​As the documentation suggests, the primary use for the Prehook function is to perform custom authorization. All you have to do is have the function return false, and ORDS will automatically return a 403 ‘Forbidden’ HTTP response code to the caller. If you use a custom token for authorization, you can use this function to verify the token, user name and password etc.

For example, you may want client X to have access to the GET and POST handler for a resource but not the DELETE handler. In this example, you can use the ‘REQUEST_METHOD’ CGI environment variable to determine the method (GET, POST, PUT or DELETE) and a combination of ‘PATH_INFO’ and ‘SCRIPT_NAME’ to determine the resource. Assuming you pass an identifier for the client in a http header, based on these values you can then decide if the call should be allowed or not.
 
I still recommend you use ORDS standard OAUTH token security as your primary method of authorization. If, however, the client cannot support OAUTH, or you need to supplement standard OAUTH with some additional checks, the Prehook function is a great option.

​I would also like to see a postHook function parameter, this would hopefully give developers access to the resultant http status and allow us to log the final status of each web service call. Ideally, it would also have access to the :body_text and response payloads. This would allow developers to implement a complete table logging solution for ORDS.

​The Prehook function capability is a well thought out feature that will be a big help to ORDS web service developers. Hopefully this post gave you some ideas on how it could be used, although I have no doubt there are many more uses cases out there.

There is an entire section of the ORDS documentation dedicated to this feature which includes examples.
 
P.S. It will be interesting to see if and how the ORDS development team plans to expose parameters like these to multi-tenant PaaS offerings such as Exadata Express. This may prove a challenge given the parameter is currently set in the parameter file on the server.