Oracle REST Data Services 21.3 Installation

October 24, 2021 admin Oracle database 21c, 2

Oracle REST Data Services 21.3 Installation

In this post, we will cover the installation and configuration of Oracle REST Data Services 21.3, create and enable some restful services to access data, store json data returned by theses services in the database and finally, we will see some use-cases of SQL/JSON Condition JSON_EXISTS  in our demo.

Oracle REST Data Services

ORDS provides a Database Management REST API and the ability to publish RESTful Web Services for interacting with the data and stored procedures in your Oracle Database

ORDS is a Java application used to develop REST APIs for the Oracle, the Oracle Database 12c, JSON Document store, and the Oracle NoSQL Database. There is no need to install and maintain client drivers. APIs are used from any language environment. For more info, refer to oracle documentation

Environment and prerequisites

• Oracle database version:  Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
• Operating System: Oracle Linux Server 8.4
• Download and install demo-schemas on Oracle database21c

Note: The following installations are not part of this post

• Installation of Oracle Linux Server 8.4
• Installation of Oracle  Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
• Installation of demo schemas

Oracle REST Data Services 21.3 Installation and configuration

• Step 1. Download Oracle REST Data Services 21.3 Installation latest version
• Step 2. Unzip to <work_dir>

unzip ords-21.3.0.266.1100.zip -d $ORACLE_HOME/ords

Step 3. Ensure that SYS user and common public users involved during the installation are unlocked. (Lock the SYS user after installation completes)

Check the SYS user and common public users are unlocked and you know their passwords.
Remember to lock the SYS user when the installation is complete.

$ sqlplus / as sysdba 
SQL*Plus: Release 21.0.0.0.0 - Production on Sun Oct 10 23:16:47 2021 
Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. 
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - 
Production Version 21.3.0.0.0 
SQL> alter user sys identified by [yourPassw] account unlock; 
User altered. 
SQL>

• create ORDS tablespace

CREATE BIGFILE TABLESPACE ORDS
DATAFILE 'bigtbs_f1.dbf'
SIZE 20M AUTOEXTEND ON;

STEP 4. Make some setup changes in ords_params.properties file to reflect our environment settings
This file is in the sub-directory called params, the changes we will make to the file refer to the silent installation.
If any parameter is not set in the file, the installation will prompt us for its specific value
Make a backup of the file first before any change.

-rw-rw-rw-. 1 oracle oinstall 338 Sep 23 08:16 ords_params.properties 
-rw-r--r--. 1 oracle oinstall 338 Oct 10 22:00 ords_params.properties_bkp

• ords_params.properties-file’s Content  after changes

db.connectionType=tns
db.hostname=ol8-21.localdomain
db.port=1521
db.servicename=ords_requests
db.sid=ora21
db.tnsDirectory=/u01/app/oracle/product/21.0.0/dbhome_1/network/admin
db.tnsAliasName=pdb1
rest.services.ords.add=true
plsql.gateway.add=false
standalone.mode=true
standalone.http.port=8080
standalone.use.https=false
db.username=ORDS_PUBLIC_USER
user.public.password=ords4Ever!
schema.tablespace.default=ORDS
schema.tablespace.temp=TEMP
user.tablespace.default=ORDS
user.tablespace.temp=TEMP
sys.user=SYS
sys.password=YourPasswd

STEP 5. Configuration Directory
create an extra directory to hold all configuration data, called config directly under the ORDS home directory.
All configuration data used during setup are stored in this directory.
Create config – directory under $ORACLE_HOME/ords

mkdir -p $ORACLE_HOME/ords/config

• Verify what value of configdir is set

$ java -jar ords.war configdir 2021-10-11T17:10:33.611Z 
INFO The config.dir setting is not set

• Set the value of configdir

oracle@ol8-21 ords]$ java -jar ords.war configdir $ORACLE_HOME/ords/config 

2021-10-11T17:15:23.214Z INFO Set config.dir to /u01/app/oracle/product/21.0.0/dbhome_1/ords/config
in: /u01/app/oracle/product/21.0.0/dbhome_1/ords/ords.war 
[oracle@ol8-21 ords]$

• Check what value of configdir has been set!

[oracle@ol8-21 ords]$ java -jar ords.war configdir 2021-10-11T17:21:50.241Z 
INFO The config.dir value is /u01/app/oracle/product/21.0.0/dbhome_1/ords/config

• Run ORDS-Setup

After all configuration is done, we can run the setup, which installs the Oracle metadata objects necessary for running ORDS in the database. 

The setup creates 2 schemas called:

ORDS_METADATA

ORDS_PUBLIC_USER

The setup is run in silent mode, which uses the parameter values previously set in the ords_params.properties file.

This section lists the parameters required for installing Oracle REST Data Services schema.

To install Oracle REST Data Services schema, following parameters must be specified:

Username and password of the user with ORDS Installer privileges or with SYS AS SYSDBA account.

ORDS_PUBLIC_USER password

Existing default and temporary tablespaces for the ORDS_METADATA schema and ORDS_PUBLIC_USER

CREATE USER "ORDS_PUBLIC_USER" identified by "ords4Ever!" 
DEFAULT TABLESPACE "ORDS" 
TEMPORARY TABLESPACE "TEMP" 
ACCOUNT UNLOCK ; 
ALTER USER "ORDS_PUBLIC_USER" DEFAULT ROLE "DBA","AQ_ADMINISTRATOR_ROLE";

• Run the Setup

java-jar ords.war install --silent --parameterFile $ORACLE_HOME/ords/params/ords_params.properties

• Verify the log in /home/oracle

oracle@ol8-21 ~]$ cat ords_validate_core_2021-10-15_233028_00253.log
[*** script: ords_alter_session_script.sql]

PL/SQL procedure successfully completed.

[*** script: ords_version.sql]

Session altered.

PL/SQL procedure successfully completed.

[*** script: ords_schema_mapping.sql] 
INFO: Configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS schemas

Session altered.

Configuring APEX and ORDS schemas for url mapping
APEX_PUBLIC_USER does not exist
APEX_REST_PUBLIC_USER does not exist
APEX is not installed, or could not be detected, stubbing out
ORDS_METADATA.APEX_WWV_FLOW_POOL_CONFIG

PL/SQL procedure successfully completed.

INFO: Completed configuring ORDS_PUBLIC_USER to map APEX Workspaces and ORDS Schemas

Session altered.

[*** script: ords_repair_proxy_connect.sql] 
INFO: Checking ords enabled schemas and its proxy user

Session altered.

PL/SQL procedure successfully completed.

[*** script: ords_alter_schema_no_auth.sql]

PL/SQL procedure successfully completed.

[*** script: ords_migrate_grant_priv.sql]

Session altered.

INFO: Verify if Application Express exists to setup the migration privileges for
ORDS.

PL/SQL procedure successfully completed.

Session altered.

[*** script: ords_validate_objects.sql] 
INFO: 23:30:31 Validating objects for Oracle REST Data Services.
VALIDATION: 23:30:31 Starting validation for schema: ORDS_METADATA
VALIDATION: 23:30:31 Validating objects
VALIDATION: 23:30:31 Validating roles granted to ORDS_METADATA and
ORDS_PUBLIC_USER
VALIDATION: 23:30:31 Validating ORDS Public Synonyms
VALIDATION: 23:30:31 Total objects: 276, invalid objects: 0, missing objects: 0
VALIDATION: 23:30:31 79 INDEX
VALIDATION: 23:30:31 3 LOB
VALIDATION: 23:30:31 16 PACKAGE
VALIDATION: 23:30:31 15 PACKAGE BODY
VALIDATION: 23:30:31 1 PROCEDURE
VALIDATION: 23:30:31 49 PUBLIC SYNONYM
VALIDATION: 23:30:31 1 SEQUENCE
VALIDATION: 23:30:31 28 TABLE
VALIDATION: 23:30:31 28 TRIGGER
VALIDATION: 23:30:31 20 TYPE
VALIDATION: 23:30:31 6 TYPE BODY
VALIDATION: 23:30:31 30 VIEW
VALIDATION: 23:30:31 Validation completed.
INFO: 23:30:31 Completed validating objects for Oracle REST Data Services.

PL/SQL procedure successfully completed.

[*** script: ords_alter_session_script.sql]

PL/SQL procedure successfully completed.

[*** script: ords_version.sql]

Session altered.

PL/SQL procedure successfully completed.

[oracle@ol8-21 ~]$

• To start Standalone mode, execute the following command:

java -jar ords.war standalone

• Before starting ords, ensure that your database is up and running
• Check if ords is running

[oracle@ol8-21 ~]$ ps -ef | grep ords
oracle 5291 3305 11 13:06 pts/0 00:00:07 java -jar ords.war standalone

• Now the service is started, it can be accessed vi a web-browser

http://<hostname>:8080/ords or http://<ip>:8080/ords (Check the tnsnames.ora - file)
http://ol8-21.localdomain:8080/ords

Now, we will enable Oracle REST Data Services to access the SH- schema. (provided in oracle demo-schema)

SQL> DECLARE PRAGMA AUTONOMOUS_TRANSACTION; 

BEGIN ORDS.ENABLE_SCHEMA(
p_enabled => TRUE, 
p_schema => 'SH', 
p_url_mapping_type => 'BASE_PATH', 
p_url_mapping_pattern => 'sh', 
p_auto_rest_auth => TRUE); 

commit; 

END; 
/

ORA-06598: insufficient INHERIT PRIVILEGES privilege

[oracle@ol8-21 ~]$ sqlplus system/systemPasswd@ords_requests

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 16 13:48:05 2021

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Sat Oct 16 2021 13:40:16 -04:00

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> show user
USER is "SYSTEM"

SQL> grant inherit privileges on user SYSTEM to ORDS_METADATA;

Grant succeeded.

commit;

SQL> declare
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4
5 ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
6 p_schema => 'SH',
7 p_url_mapping_type => 'BASE_PATH',
8 p_url_mapping_pattern => 'sh',
9 p_auto_rest_auth => TRUE);
10
11 commit;
12
13 END;
14 /

PL/SQL procedure successfully completed.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - 
Production Version 21.3.0.0.0 
SQL> show user USER is "SH" 
SQL> 
SQL> DECLARE 
2 PRAGMA AUTONOMOUS_TRANSACTION; 
3 
4 
BEGIN 
5 ORDS.define_service( 
6 p_module_name => 'customers', 
7 p_base_path => 'customers/', 
8 p_pattern => 'Get100Allcustomers/', 
9 p_method => 'GET', 
10 p_source_type => ORDS.source_type_collection_feed, 
11 p_source => 'SELECT * FROM customers where rownum < 101', 
12 p_items_per_page => 25 ); -- default 
13 
14 commit; 
15 
16 END; 
17 / 
PL/SQL procedure successfully completed.

http://ol8-21.localdomain:8080/ords/sh/customers/Get100Allcustomers/

Let’s define another service (Getcustomerscountries) to get all customers and their countries and then use the output for our json-demo.

DECLARE 

PRAGMA autonomous_transaction; 

sql_stmt VARCHAR2(1000) := 'SELECT cust_id, 
cust_first_name, 
cust_last_name, 
cust_gender, 
cust_year_of_birth, 
cust_marital_status, 
cust_street_address, 
cust_postal_code, 
cust_city, 
cust_city_id, 
cust_state_province, 
cust_state_province_id, 
cstm.country_id, 
cust_main_phone_number, 
cust_income_level, 
cust_credit_limit, 
cust_email, cust_total, 
cust_total_id, 
cust_src_id, 
cust_eff_from, 
cust_eff_to, 
cust_valid, 
-- 
cntr.country_id, 
country_iso_code, 
country_name, 
country_subregion, 
country_subregion_id, 
country_region, 
country_region_id, 
country_total, 
country_total_id, 
country_name_hist 
FROM customers cstm, countries cntr 
WHERE cstm.country_id = cntr.country_id';

BEGIN 

ords.define_service(p_module_name => 'customers', 
p_base_path => 'customers/', 
p_pattern => 'Getcustomerscountries/', 
p_method => 'GET', 
p_source_type => ords.source_type_collection_feed, 
p_source => sql_stmt, 
p_items_per_page => 0); 

commit; 

END; 
/ 

COMMIT; 
END;

• Let’s create a table to store the json data returned by the previous service

http://ol8-21.localdomain:8080/ords/sh/customers/Getcustomerscountries/

• Create j_cntrycustomers  table

CREATE TABLE j_cntrycustomers ( 
id NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP ) 
NOT NULL, date_loaded TIMESTAMP(6) WITH TIME ZONE DEFAULT sysdate, 
po_document CLOB CONSTRAINT ensure_json CHECK ( po_document IS JSON ) 
);

• insert customers data into j_cntrycustomers.

insert into j_cntrycustomers (po_document) 
values 
( 
' { "custitems": { 
"cust_id": 24562, 
"cust_first_name": "Abner", 
"cust_last_name": "Kenney", 
"cust_gender": "M", 
"cust_year_of_birth": 1960, 
"cust_marital_status": "married", 
"cust_street_address": "77 North Page Avenue", 
"cust_postal_code": "72860", 
"cust_city": "Koeln", 
"cust_city_id": 51735, 
"cust_state_province": "Nordrhein-Westfalen", 
"cust_state_province_id": 52684, 
"country_id": 52776, 
"cust_main_phone_number": "498-711-5332", 
"cust_income_level": "K: 250,000 - 299,999", 
"cust_credit_limit": 7000, 
"cust_email": "Kenney@company.example.com", 
"cust_total": "Customer total", 
"cust_total_id": 52772, "cust_src_id": null, 
"cust_eff_from": "1998-01-01T05:00:00Z", 
"cust_eff_to": null, "cust_valid": "I" } }' 
); 

insert into j_cntrycustomers (po_document) 
values ( 
' { "custitems": { 
"cust_id": 28117, 
"cust_first_name": "Abner", 
"cust_last_name": "Kenney", 
"cust_gender": "M", 
"cust_year_of_birth": 1957, 
"cust_marital_status": null, 
"cust_street_address": "87 West Covington Avenue", 
"cust_postal_code": "83967", 
"cust_city": "Tilburg", 
"cust_city_id": 52369, 
"cust_state_province": 
"Noord-Brabant", 
"cust_state_province_id": 52682, 
"country_id": 52770, 
"cust_main_phone_number": "636-716-9795", 
"cust_income_level": "K: 250,000 - 299,999", 
"cust_credit_limit": 15000, 
"cust_email": "Kenney@company.example.com", 
"cust_total": "Customer total", 
"cust_total_id": 52772, 
"cust_src_id": null, 
"cust_eff_from": "1998-01-01T05:00:00Z", 
"cust_eff_to": null, 
"cust_valid": "I" } }' 
); 

commit;

Example of SQL/JSON condition use
SQL/JSON condition: select rows based on the content of json documents.
use SQL/JSON path expression as a row filter
can be used in CASE expression or the WHERE clause of a SELECT statement.

SELECT po.po_document 
FROM j_cntrycustomers po
WHERE json_exists(po.po_document, '$.items.cust_city');

SELECT po.po_document 
FROM j_cntrycustomers po
WHERE json_exists(po.po_document,
'$?(@.items.cust_city == "Scheveningen")');

• the scope of the filter is the context cust_city, that is, an entire customer info. @ refers to the context cust_city.

SELECT po.po_document 
FROM j_cntrycustomers po
WHERE json_exists(po.po_document,
'$.items?(@.cust_city== "Scheveningen")');

• The examples 2 & 3 show equivalent ways to select documents, in our example all customers from Scheveningen city

Oracle Advanced Queuing (AQ) demo How to use and run Oracle Database Security Assessment Tool (2.2.2) on Oracle PDB with Wallet