Sometimes there are specific requirements which make the above solutions not viable. For example if you have complex custom authentication logic implemented elsewhere which might be difficult to translate to ORDS or the embedded PL/SQL gateway. ORDS also runs in stand-alone in a Docker container but this is not so easy for the PL/SQL gateway. Also if you are looking for a product or framework which can be used for multiple flavors of database, these solutions might be too Oracle specific.
You can consider creating your own custom service in for example Java. The problem here however is that it is often tightly coupled with the implementation. If for example parameters of a database procedure are mapped to Java objects or a translation from a view to JSON takes place in the service, there is often a tight coupling between the database code and the service.
In this blog post I'll provide a solution for a transparent Spring Boot REST service which forwards everything it receives to the database for further processing without this tight coupling, only to to a generic database procedure to handle all REST requests. The general flow of the solution is as follows:
- The service receives an HTTP request from a client
- Service translates the HTTP request to an Oracle database REST_REQUEST_TYPE object type
- Service calls the Oracle database over JDBC with this Object
- The database processes the REST_REQUEST_TYPE and creates a REST_RESPONSE_TYPE Object
- The database returns the REST_RESPONSE_TYPE Object to the service
- The service translates the REST_RESPONSE_TYPE Object to an HTTP response
- The HTTP response is returned to the client
