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 :
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
For more information on how to connect ORDS with Oracle VBCS checkout this blog article
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.
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 :
Generally, it makes sense to use OIC when:
|Façade Technology||3GL Language||Comments|
|Oracle ADF Business Components
|Fully featured Java based framework for querying data out of the Oracle Database. Designed for “Database” style interactions and implements a ton of good design patterns. The tool uses JDeveloper 12c as its design environment and is coincidentally the engine which runs Oracle Fusion.
Note: Here we are discussing ADF Business Components not ADF Faces
Note : ADFB only supports deployment on Weblogic or Java Cloud Service
|Custom Code with Jersey & Java Persistence Architecture (JPA)
Jersey is the reference implementation of a REST API for java. Using Java Code and Java annotations you can easily create a java façade which exposes an API and use native JDBC to access the database.
For the ORM side of things you can use Java Persistence Architecture, which is a standard ORM for java.See the official java tutorials for more details :
|NodeJS provides a database driver for the Oracle Database, this can be used together with the express framework to create a REST Façade
|New Modern set of libraries from Oracle for building microservices in Java
For more information see the helidon website http://www.helidon.io
Sample Architecture using the database as a temporary/stage data store
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