/oracle-rest-data-services-ords-use-sqlcl-to-display-ords-web-service-definitions

Oracle REST Data Services (ORDS) : Use SQLcl to Display ORDS Web Service
Definitions

[46]SQLcl is a lightweight Java-based replacement for SQL*Plus, which
amongst other things allows you to display ORDS web service definitions
using the REST command.
* [47]Assumptions
* [48]Setup
* [49]Create RESTful Web Services
* [50]Basic Information (schemas, modules, privileges)
* [51]Web Service Definitions (export)

Related articles.
* [52]Oracle REST Data Services (ORDS) : All Articles
* [53]Oracle REST Data Services (ORDS) : Create Basic RESTful Web
Services Using PL/SQL
* [54]SQLcl : All Articles

Assumptions and Comments

This article assumes the following.
* You already have a functioning [55]installation of ORDS and
associated database.
* You already have a functioning SQLcl installation.

Setup

We need a new database user for our testing.
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1
DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE, CREATE TYPE TO testuser1;

Create and populate a copy of the EMP table.
CONN testuser1/testuser1@pdb1

CREATE TABLE EMP (
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0),
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);

insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMI
TH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALL
EN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WAR
D','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JON
ES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MAR
TIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLA
KE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLA
RK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCO
TT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KIN
G','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TUR
NER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADA
MS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAM
ES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FOR
D','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MIL
LER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

Enable ORDS for the new schema.
CONN testuser1/testuser1@pdb1

BEGIN
ORDS.enable_schema(
p_enabled => TRUE,
p_schema => 'TESTUSER1',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'testuser1',
p_auto_rest_auth => FALSE
);

COMMIT;
END;
/

Create RESTful Web Services

We need some RESTful web services to test with. You can learn more
about this subject [56]here, but for this demonstration we will just
create two simple services.
CONN testuser1/testuser1@pdb1

BEGIN
ORDS.define_service(
p_module_name => 'testmodule1',
p_base_path => 'hr1/',
p_pattern => 'emp/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM emp',
p_items_per_page => 0);

ORDS.define_service(
p_module_name => 'testmodule2',
p_base_path => 'hr2/',
p_pattern => 'emp/',
p_method => 'GET',
p_source_type => ORDS.source_type_collection_feed,
p_source => 'SELECT * FROM emp',
p_items_per_page => 0);

COMMIT;
END;
/

Basic Information (schemas, modules, privileges)

The REST command can provide some basic information, including
REST-enabled schemas, modules and privileges.
CONN sys/OraPassword1@pdb1 AS SYSDBA
Connected.
SQL> REST schemas
PARSING_SCHEMA PATTERN STATUS
-------------- --------- -------
TESTUSER1 testuser1 ENABLED
SQL>


SQL> CONN testuser1/testuser1@pdb1
Connected.
SQL> REST modules
NAME PREFIX STATUS ITEMS_PER_PAGE
----------- ------ --------- --------------
testmodule1 /hr1/ PUBLISHED 0
testmodule2 /hr2/ PUBLISHED 0
SQL>


SQL> CONN testuser1/testuser1@pdb1
Connected.
SQL> REST privileges
NAME LABEL DESCRIPTION COMMENTS
------------------------------- ----- ----------- --------
oracle.soda.privilege.developer
SQL>

Web Service Definitions (export)

The export option displays the definition of one of more ORDS module
definitions. With no additional qualifier, the definition of all ORDS
modules in the current schema are displayed. Notice the output includes
the ORDS.ENABLE_SCHEMA call and uses the full creation syntax, not the
DEFINE_SERVICE call.
SQL> REST export
-- Generated by SQLcl REST Data Services 17.4.0.354.2224
-- Exported REST Definitions from ORDS Schema Version 17.4.1.353.06.48
-- Schema: TESTUSER1 Date: Tue Jan 15 20:51:09 GMT 2018
--
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'TESTUSER1',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'testuser1',
p_auto_rest_auth => FALSE);

ORDS.DEFINE_MODULE(
p_module_name => 'testmodule1',
p_base_path => '/hr1/',
p_items_per_page => 0,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'testmodule1',
p_pattern => 'emp/',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'testmodule1',
p_pattern => 'emp/',
p_method => 'GET',
p_source_type => 'json/collection',
p_items_per_page => 0,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'SELECT * FROM emp'
);

ORDS.DEFINE_MODULE(
p_module_name => 'testmodule2',
p_base_path => '/hr2/',
p_items_per_page => 0,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'testmodule2',
p_pattern => 'emp/',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'testmodule2',
p_pattern => 'emp/',
p_method => 'GET',
p_source_type => 'json/collection',
p_items_per_page => 0,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'SELECT * FROM emp'
);


COMMIT;
END;
SQL>

The output can be limited by specifying module of interest.
SQL> REST export testmodule1
-- Generated by SQLcl REST Data Services 17.4.0.354.2224
-- Exported REST Definitions from ORDS Schema Version 17.4.1.353.06.48
-- Schema: TESTUSER1 Date: Tue Jan 15 20:53:18 GMT 2018
--
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'TESTUSER1',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'testuser1',
p_auto_rest_auth => FALSE);

ORDS.DEFINE_MODULE(
p_module_name => 'testmodule1',
p_base_path => '/hr1/',
p_items_per_page => 0,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'testmodule1',
p_pattern => 'emp/',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'testmodule1',
p_pattern => 'emp/',
p_method => 'GET',
p_source_type => 'json/collection',
p_items_per_page => 0,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'SELECT * FROM emp'
);


COMMIT;
END;
SQL>

Alternatively the base path of the module of interest can be used.
SQL> REST export /hr2/
-- Generated by SQLcl REST Data Services 17.4.0.354.2224
-- Exported REST Definitions from ORDS Schema Version 17.4.1.353.06.48
-- Schema: TESTUSER1 Date: Tue Jan 15 20:54:23 GMT 2018
--
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'TESTUSER1',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'testuser1',
p_auto_rest_auth => FALSE);

ORDS.DEFINE_MODULE(
p_module_name => 'testmodule2',
p_base_path => '/hr2/',
p_items_per_page => 0,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'testmodule2',
p_pattern => 'emp/',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'testmodule2',
p_pattern => 'emp/',
p_method => 'GET',
p_source_type => 'json/collection',
p_items_per_page => 0,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'SELECT * FROM emp'
);


COMMIT;
END;
SQL>

For more information see:
* [57]SQL Developer Command-Line Quick Reference
* [58]Oracle REST Data Services (ORDS) : All Articles
* [59]Oracle REST Data Services (ORDS) : Create Basic RESTful Web
Services Using PL/SQL
* [60]SQLcl : All Articles

Hope this helps. Regards Tim...

--------------------------
Source .... : https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-use-sqlcl-to-display-ords-web-service-definitions
--------------------------