What you should know when extending SaaS with VBCS – Part 3 Exposing the Database

VBCS strictly speaking only supports REST APIs so accessing the database via SQL*Net is not possible. To access the data in the Database from VBCS you will need to create a REST Façade to access your data in the database.  

To achieve this we we have a number of options available to us including :

1. Oracle REST Data Service (ORDS)
2. A 3GL facade using Java, JavaScript etc
3. Oracle Integration Cloud

1. Oracle REST Data Service (ORDS)

This is a nice and easy REST data service which runs on top of the Oracle database and allows you to easily expose a database table, or stored procedure/function, as a REST API. This approach works well for small datasets and depending on your use case you can also do some coding in PLSQL for advanced queries or implementing data security.  If you have experience of PLSQL then this could be the one for you as it would allow you to reuse your existing skills.

The main disadvantage of using ORDS is that it is not easily possible to do single sign on from a web user to a database user and thus this approach is only really viable for APIs which are either public APIs (no authentication) or when used in conjunction with a 3GL client side façade which does support Web SSO and then use an APPID to access the database via REST(ORDS).

Additionally not only is it possible to access database tables via REST, it is also possible to create a full blown api , using PLSQLas the implementation language – again a bonus if PLSQL is familiar to you.

If your API needs to access multiple data sources outside of the database, like SOAP Services, other REST Services, then a 3GL façade, described below, may be a better approach.

For more information on the Oracle REST Data Services (ORDS), see this link

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/

For more information on how to connect ORDS with Oracle VBCS checkout this blog article

2. A 3GL façade

Oh boy here there are so many options to choose from that it’s really up to the developer to decide which façade is best for them based on their experience, company standards, and preference. Generally, you will need to create a REST Façade which exposes a well-designed REST API, supporting business logic, data security, AND deals with security/authentication.  There are so many products and technologies out there that can fulfil the role of building a REST façade that it would be impossible to list them all here however the table below lists some of the most common we have seen in our projects.

The primary advantage of using a 3GL middle-tier, using say Java or JavaScript, is the ability to scale this tier horizontally, elastically at will. 3GLs are also very well suited for accessing datasources of multiple protocols, e.g. SOAP,REST,RPC etc, and the developer can use some of the latest developments in the application development space like microservices (Helidon) and even Serverless technologies.

3. Oracle Integration Cloud

Whilst Oracle Integration Cloud (OIC) is primarily an integration tool it can be very handy as a data access layer. Now from the start its worth stating that we would not recommend using OIC as the primary “middle tier” technology but It can be used for occasional or edge cases.

Oracle Integration Cloud (OIC) can be used in a declarative way and very easily :

1. Take a SOAP Service and convert it to a REST Service
2. Create a REST Façade against a database, e.g. one on premise, and expose this as a REST service
3. Create a composite service call which when called executes a number of back-end calls to full fill some sort of business logic (e.g. Place an order)

Generally, it makes sense to use OIC when:

• You already have an integration flow within OIC and you want to expose this to the User Interface
• Accessing a database which is on-premise and you are OK with the asynchronous nature of the OIC agent. The OIC “On premise agent” is asynchronous in nature and will add some latency for any calls.
• Your integration flow uses the “map my data” pattern (which is stateless and very fast)
• You do not need identity propagation or complex synchronous business logic in the middle tier (complex async alls are perfectly OK)

Sample Architecture using the database as a temporary/stage data store

Next Steps

Click here to proceed to the next article in this series, What you should know when extending SaaS with VBCS – Part 4 To middle-tier or not to middle tier