ORDS (Oracle REST Data Services)

What is ORDS?

ORDS offers you the power to publish a REST API for your information residing in the database. ORDS makes it simple to develop a REST interface/service for relative information. This comparative information is often held on either Associate in Nursing Oracle information, Associate in Nursing Oracle 12c JSON Document Store, or Associate in Nursing Oracle NoSQL information. Learn more Oracle Cloud solutions

ORDS could be a java-based utility employed to wrap your information DML operative with web-service like GET, PUT, POST, etc., and result in a JSON format.

Use Case

The primary use case is to expose existing data as a REST service. Data resides typically in a database, and with the help of ORDS, we can disclose data with secure REST API.

REST API can be consumed in applications, integrations, or any third-party tool which allows REST API call.

The Audience:

Recommended users are those who have a technical background in REST service. This document will help organizations/users who need to install ORDS on their VM

Download ORDS

Download the file ords.zip from the Oracle REST Data Services page.

https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads.html

Installation

Deploy Oracle REST Data Services. Deployment options include:

• Standalone Mode
• Oracle WebLogic Server
• GlassFish Server
• Apache Tomcat

This section describes how to run Oracle REST Data Services in standalone mode.

Standalone mode is suitable to design web-service base functionality, and we can consume these services in VBCS, OIC, or third-party applications.

You can install ORDS into one or more pluggable databases PDBs in a multitenant database or the container database (CDB). The installation choices are as follows:

• If you want the same ORDS version available in all the PDBs, then install it into the CDB. Instructions are used in this topic to be installing into CDB.
• If you want only some PDBs to use ORDS, or if you wish to different PDBs to use different versions of ORDS, then install into the desired PDBs.
• The below command is used to verify multitenant instances.

SELECT name, cdb, con_id FROM v$database;

The CDB column will contain a 'Y' if the instance is multitenant. There is an option to install APEX in the CDB or as a standalone install for PDB. The following query lists the PDBs in a multitenant instance:

select name, open_mode, restricted from v$pdbs;

In general, the standalone APEX installation for the PDB is recommended.

Installation commands:

Download Oracle REST Data Services (ORDS) file from - ords-20.2.1.227.0350.zip

https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads.html

Copy ords-20.2.1.227.0350.zip to /home/opc.

Connect with OPC user to your VM.

Command	Output
[opc@xxxprod ~]$
[opc@xxxprod ~]$ pwd	/home/opc
[opc@xxxprod ~]$ sudo cp ords- 20.2.1.227.0350.zip /home/oracle
[opc@xxxprod ~]$ sudo cd /home/oracle
[opc@xxxprod ~]$ sudo su -
[root@xxxprod ~]# cd /home/oracle
[root@xxxprod oracle]# ls -l	total 63992 -rw-r--r-- 1 root root 65501948 Oct 22 16:39 ords- 20.2.1.227.0350.zip
[root@xxxprod oracle]# chown oracle:oinstall ords-20.2.1.227.0350.zip
[root@xxxprod oracle]# sudo su - oracle
[oracle@xxxprod ~]$ mkdir ords
[oracle@xxxprod ~]$ cd ords
[oracle@xxxprod ords]$ pwd	/home/oracle/ords
[oracle@xxxprod ords]$ mv /home/oracle/ords- 20.2.1.227.0350.zip .
[oracle@xxxprod ords]$ ls -l	total 63968 -rw-r--r-- 1 oracle oinstall 65501948 Oct 22 16:39 ords- 20.2.1.227.0350.zip
[oracle@xxxprod ords]$ unzip ords- 20.2.1.227.0350.zip	This command will unzip the ords-20.2.1.227.0350.zip
[oracle@xxxprod ords]$ ls	docs index.html ords-20.2.1.227.0350.zip params examples installer ords.war
[oracle@xxxprod ords]$ cd params/
[oracle@xxxprod params]$ ls	ords_params.properties
[oracle@xxxprod params]$ cat ords_params.properties	# db.hostname= db.port= db.servicename= db.sid= db.username=APEX_PUBLIC_USER migrate.apex.rest=false rest.services.apex.add= rest.services.ords.add=true schema.tablespace.default=SYSAUX schema.tablespace.temp=TEMP standalone.http.port=8080 standalone.static.images= user.tablespace.default=USERS user.tablespace.temp=TEMP
[oracle@xxxprod params]$ lsnrctl	LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-OCT-2020 16:44:03 Copyright (c) 1991, 2019, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information.
LSNRCTL> ^C	Ctrl + C
[oracle@xxxprod params]$ vi ords_params.properties
[oracle@xxxprod params]$ hostname -f	<>
[oracle@xxxprod params]$ ^C	Ctrl + C
[oracle@xxxprod params]$ vi ords_params.properties
[oracle@xxxprod params]$ cat ords_params.properties	# db.hostname=<> db.port=<> db.servicename=<> db.username=APEX_PUBLIC_USER migrate.apex.rest=false rest.services.apex.add=false rest.services.ords.add=true schema.tablespace.default=SYSAUX schema.tablespace.temp=TEMP standalone.http.port=8080 #standalone.static.images= user.public.password=<> user.tablespace.default=USERS user.tablespace.temp=TEMP
[oracle@xxxprod params]$ cd ..
[oracle@xxxprod ords]$ pwd	/home/oracle/ords
[oracle@xxxprod ords]$ ls	docs index.html ords-20.2.1.227.0350.zip params examples installer ords.war
[oracle@xxxprod oracle]$ mkdir ords
[oracle@xxxprod oracle]$ cd ords/
[oracle@xxxprod ords]$ mkdir conf
[oracle@xxxprod ords]$ cd conf/
[oracle@xxxprod conf]$ pwd	…/app/oracle/ords/conf
[oracle@xxxprod ords]$ java - Dconfig.dir=/u01/app/oracle/ords/conf -jar ords.war install simple	Requires to login with administrator privileges to verify Oracle REST Data Services schema. Enter the administrator username: sys Enter the database password for SYS AS SYSDBA: Confirm password: Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@// <> <> / <> Retrieving information. Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step. If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2 Enter a number to select a feature to enable: [1] SQL Developer Web (Enables all features) [2] REST Enabled SQL [3] Database API [4] REST Enabled SQL and Database API [5] None Choose [1]:1 2020-10-22T16:58:54.189Z INFO reloaded pools: [] Installing Oracle REST Data Services version 20.2.1.r2270350 ... Log file written to /home/oracle/ords_install_core_2020-10-22_165854_00694.log ... Verified database prerequisites ... Created Oracle REST Data Services proxy user ... Created Oracle REST Data Services schema ... Granted privileges to Oracle REST Data Services ... Created Oracle REST Data Services database objects ... Log file written to /home/oracle/ords_install_datamodel_2020-10-22_165922_00884.log ... Log file written to /home/oracle/ords_install_apex_2020-10-22_165925_00371.log Completed installation for Oracle REST Data Services version 20.2.1.r2270350. Elapsed time: 00:00:32.265 Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1 Enter 1 if using HTTP or 2 if using HTTPS [1]: 2020-10-22T17:00:27.725Z INFO HTTP and HTTP/2 cleartext listening on host: localhost port: 8080 2020-10-22T17:00:27.773Z INFO Disabling document root because the specified folder does not exist: /u01/app/oracle/ords/conf/ords/standalone/doc_root 2020-10-22T17:00:30.118Z INFO Configuration properties for: |apex|pu| database.api.enabled=true db.connectionType=basic db.hostname= <> db.port=<> db.servicename=<> feature.sdw=true restEnabledSql.active=true db.password=****** db.username=ORDS_PUBLIC_USER resource.templates.enabled=true 2020-10-22T17:00:30.122Z WARNING *** jdbc.MaxLimit in configuration |apex|pu| is using a value of 10; this setting may not be sized adequately for a production environment *** 2020-10-22T17:00:30.123Z WARNING *** jdbc.InitialLimit in configuration |apex|pu| is using a value of 3; this setting may not be sized adequately for a production environment *** 2020-10-22T17:00:36.135Z INFO Oracle REST Data Services initialized Oracle REST Data Services version : 20.2.1.r2270350 Oracle REST Data Services server info: jetty/9.4.28.v20200408

Installing Trusted SSL Certificate

https://blogs.oracle.com/dbcs/installing-a-trusted-ssl-certificate-on-oracle-database-cloud-service-for-apex

or

You can generate a certificate with your own- or a third-party tool. Oracle recommends Comodo.

Move cert and key to the /home/opc path and edit the. properties file.

Secure port registration

• Register port in OIC console – Add ingress rule for your port > in OIC console.

  Network – VCN Cloud Networks – click on Subnet and add ingress rule.

  

• Register your port at OS level.

  Connect to the VM with OPC and register your port.

  Sudo -i
  To list the existing rules run:

  # iptables -L INPUT -n --line-number

  To add the rule at the end of the list, it should be added before the last "REJECT" line:

  # iptables -I INPUT 12 -m state --state NEW -m tcp -p tcp --dport >-j ACCEPT

  To save the configuration to make it permanent, so it gets automatically applied after reboot, run:

  # service iptables save

  Reboot the system (optional)

Authorization

To protect the web service from the outside world, we need to create a role with an associated privilege and then map the privilege to the web service. Typically, we would expect a role to be a collection of privileges and permissions, and of course, a single privilege can be part of multiple roles.

Example:

Connect to ORDS enabled schema with PDB service.

Create Role:

Define privilege

Authentication user creation

Go to the ORDS directory and execute the below command

$JAVA_HOME/bin/java -jar ords.war user <> <>

User information gets stored in the credential file.

Example:

Useful Commands

Start ORDS (VM)	java -jar ords.war standalone &
Stop ORDS (VM)	ps -ef |grep ords kill -9 (process ID from above command)
Get ORDS Version	Java -jar ords.war version

Summary:

Standalone ORDS is useful to expose PL/SQL APIs, Custom table using RESTfull web services. This would be particularly useful when creating an integration/VBCS application with REST API and want to get data in the desired pattern.