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.

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 <>
[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.

    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

ORDSORDS

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:

ORDSORDS

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.

Comments

  1. Chris Lesnar7 September 2021 at 03:16

    Hey there, thanks for compiling this information for all of us who are in need of these kind of information. I really enjoyed reading your post.

    Informatica Read Rest Api

    ReplyDelete

Post a Comment

Popular posts from this blog

Whitepaper - Mobile Application Testing

With the advent of smartphones, a mobile phone has evolved from being just a communication device to a highly sophisticated business utility equipment. With smartphones no longer remaining a conspicuous consumption, they have become an efficient tool for handling finances, managing mails & agendas, and executing other business functions. Statistics highlight that 1.6 billion smart phones are sold at a rate of 1.8 to 2 million devices a day since 2007, and 1.5 billion smartphones will be shipped in 2016 – a 5.7 percent increase from 2015 – and will reach 1.92 billion by 2020. Download whitepaper - Mobile application testing
Read more

Tableau CRM features and Setup guide

Image
What is Tableau CRM? Many enterprises have leveraged Salesforce out-of-the-box reports and dashboards to understand their data and performance for years. However, data size has been increasing rapidly daily; analyzing the data manually has become a bigger challenge; subsequently, analytics have become more critical than ever before. Learn more about Salesforce CRM implemetation   Tableau CRM (Formerly known as Einstein Analytics) was introduced to address this challenge. It's an AI (Artificial intelligence) powered advanced analytics solution which works with any data source. The AI-powered Einstein Discovery, Einstein Analytics apps analyze billions of data combinations and provide predictive insights and prescriptive recommendations to the users. Tableau CRM provides analytic experiences in sales, service, and marketing to quickly take business decisions and actions. This platform is also optimized for mobile use. Why Tableau CRM? The key differences between Tableau C...
Read more

5 Things You Must Know About Cyber Security in the Cloud

Image
At first cloud computing and cyber security look like they are not related to each other. However, we can see that they are related and the reason behind it is that the first requires storing your data off-site and the second requires building virtual walls around it, protecting your data at all costs. Cloud computing means outsourcing, trusting a vendor to keep your data and transactions safe. Cyber security means keeping it all close, trusting the staff, procedures and protocols to do the job. Check more information about Oracle Cloud Managed Services . Cloud Security is the new Cyber Security With every passing year, the number of businesses migrating to the cloud increases–and the number of cyberattacks increases, as if keeping pace. By end of 2018, almost 96 percent of organizations started using cloud computing in some way, according to CIO.com. At the same time, cyberattacks were on the rise, with almost twice as many ransomware attacks in 2017 (160,000) as compared t...
Read more