Implementing Microservices in the Database Using PL/SQL

Implementing a microservice in the database bypasses the middle tier’s complexity. You can code in PL/SQL or JavaScript and deploy on an Oracle Autonomous database. This blog gives a quick example of using PL/SQL. See Implementing Microservices in the Database Using JavaScript for a similar example using JavaScript.

PL/SQL Example

Here is an example:

create or replace procedure get_inventory (
itemid in out varchar2,
inventorycount out varchar2)
authid current_user
is
begin
  select inventorycount into inventorycount from inventory where inventoryid = itemid;
end;

We use a feature called AUTO-PLSQL to deploy as an HTTP service with JSON document inputs and outputs. The input and output JSON attributes are defined by the “in” and “out” PL/SQL parameter definitions. This code deploys the service and names it “getInventory”:

ords.enable_object (
  p_enabled      => true,
  p_schema       => 'INVENTORY_USER',
  p_object       => 'GET_INVENTORY',
  p_object_type  => 'PROCEDURE',
  p_object_alias => 'getInventory'
);

New Workshop

To learn more about building microservices in the database and try it out yourself I would recommend our new workshop: Simplify Microservices with a 2-tier Converged Database Architecture.