Image
ORDS

ORDS (Oracle REST Data Services)

Sachin Patake
February 5, 2021

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.

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.

ORDS

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.

ORDS

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 <<You will get the hostname>>
[oracle@xxxprod params]$ ^C Ctrl + C
[oracle@xxxprod params]$ vi
ords_params.properties
 
[oracle@xxxprod params]$ cat
ords_params.properties
#
db.hostname=<<Add your hostname>>
db.port=<<Port Number>>
db.servicename=<<Service Name>>
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=<<XXXX>>
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:@// <<Instance details>> <<port number>> / <<Instance details>>
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= <<Add hostname>>
db.port=<<Add portnumber>>
db.servicename=<<Add 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.

    ORDS
  • 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:

ORDS

Define privilege

ORDS ORDS

Authentication user creation

Go to the ORDS directory and execute the below command

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

User information gets stored in the credential file.

Example:

ORDS ORDS

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.

About the Author

Sachin Patake, Solution Architect

Sachin Patake holds a master's degree in computer science and has worked as a Solution Architect with Jade Global for the last 12+ years. He has 13+ years of experience in EBS & Fusion enhancement with the PaaS platform and more than 12+ years of domain experience.

He is an Oracle cloud platform Application integration, 2019 certified associate. He has provided multiple solutions for Order to Cash & Integration Projects in Oracle 11i, R12 & Fusion during his work experience.

Add New Comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.