X

Best Practices from Oracle Development's A‑Team

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

Angelo Santagata
Architect

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)
Façade Technology 3GL Language Comments
Oracle ADF Business  Components
(aka ADFBC)
Java

 

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
  • The generation of a REST Façade is very easy and a basic facade can be produced without writing a single line of code
  • ADFBC supports business logic written as Java and can be exposed via the REST API
  • Extensive REST query API to allow the querying of data, e.g.  hostname.com/data?q=Employee Like ‘Anna’&fields=name&limit=5
  • Shares the same API that is used with Oracle Fusion
  • VBCS Natively supports the ADF BC Describe annotation (like a WADL or a WSDL for SOAP people) and thus automatically knows how to optimize the queries
  • OOTB Single SignOn supported (needs configuring for JCS)
  
Note : ADFB only supports deployment on Weblogic or Java Cloud Service
Custom Code with Jersey & Java Persistence Architecture (JPA)

 

Java

 

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 :
  • Jersey is well established and the reference implementation of REST for java
  • Lots of articles on the internet on how to expose and use Jersey
  • Code can be deployed on many application servers such as Weblogic, Helidon (Jersey Support) etc
  • If you deploy the code into Weblogic then authentication and single sign on is done for you. You can also use OPSS to propogate the credentials back to Fusion.
Custom Code with JavaScript (e.g. express) JavaScript

 

NodeJS provides a database driver for the Oracle Database, this can be used together with the express framework to create a REST Façade
  • Multiple Database connectivity options are available such as the oracle database drivers and opensource persistence engines like NodeDB, TypeORM and many others
  • Typically very fast due to its asynchronous nature
  • Uses JavaScript, same framework as VBCS
Helidon 

 

Java

 

New Modern set of libraries from Oracle for building microservices in Java
  • Simple and effective, no nonsense framework using Netty
  • Supports functional (SE and MP Micro Profile Support)
  • Multiple security provider support, including Oracle IDCS
  • Supports industry standard and known APIs like JAX-RS, JSON-P and JPA for persistence

For more information see the helidon website http://www.helidon.io

 

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

 

Join the discussion

Comments ( 1 )
  • Chetan Durg Sunday, May 12, 2019
    The information which is provided in all the six series are very very informative and useful and it really helps to decide on various approaches when it comes to VBCS for the extensions.

    Thanks
    Chetan
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha