8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle REST Data Services (ORDS) : Use SQLcl to Display ORDS Web Service Definitions
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.
- Assumptions
- Setup
- Create RESTful Web Services
- Basic Information (schemas, modules, privileges)
- Web Service Definitions (export)
Related articles.
- Oracle REST Data Services (ORDS) : SQLcl and ORDS
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- SQLcl : All Articles
Assumptions and Comments
This article assumes the following.
- You already have a functioning 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,'SMITH','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,'ALLEN','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,'WARD','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,'JONES','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,'MARTIN','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,'BLAKE','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,'CLARK','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,'SCOTT','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,'KING','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,'TURNER','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,'ADAMS','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,'JAMES','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,'FORD','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,'MILLER','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 => 'hr', 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 here, but for this demonstration we will just create two simple services.
CONN testuser1/testuser1@pdb1 BEGIN ORDS.define_service( p_module_name => 'rest-v1', p_base_path => 'rest-v1/', p_pattern => 'employees/', 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 => 'rest-v2', p_base_path => 'rest-v2/', p_pattern => 'employees/', 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 hr ENABLED SQL> SQL> CONN testuser1/testuser1@pdb1 Connected. SQL> REST modules NAME PREFIX STATUS ITEMS_PER_PAGE ------- --------- --------- -------------- rest-v1 /rest-v1/ PUBLISHED 0 rest-v2 /rest-v2/ 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 19.2.1.0 -- Exported REST Definitions from ORDS Schema Version 19.2.0.r1991647 -- Schema: TESTUSER1 Date: Sat Nov 30 14:07:56 GMT 2019 -- BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'TESTUSER1', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE); ORDS.DEFINE_MODULE( p_module_name => 'rest-v1', p_base_path => '/rest-v1/', p_items_per_page => 0, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'rest-v1', p_pattern => 'employees/', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'rest-v1', p_pattern => 'employees/', 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 => 'rest-v2', p_base_path => '/rest-v2/', p_items_per_page => 0, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'rest-v2', p_pattern => 'employees/', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'rest-v2', p_pattern => 'employees/', 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 rest-v1 -- Generated by SQLcl REST Data Services 19.2.1.0 -- Exported REST Definitions from ORDS Schema Version 19.2.0.r1991647 -- Schema: TESTUSER1 Date: Sat Nov 30 14:09:06 GMT 2019 -- BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'TESTUSER1', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE); ORDS.DEFINE_MODULE( p_module_name => 'rest-v1', p_base_path => '/rest-v1/', p_items_per_page => 0, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'rest-v1', p_pattern => 'employees/', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'rest-v1', p_pattern => 'employees/', 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 /rest-v2/ -- Generated by SQLcl REST Data Services 19.2.1.0 -- Exported REST Definitions from ORDS Schema Version 19.2.0.r1991647 -- Schema: TESTUSER1 Date: Sat Nov 30 14:10:06 GMT 2019 -- BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'TESTUSER1', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE); ORDS.DEFINE_MODULE( p_module_name => 'rest-v2', p_base_path => '/rest-v2/', p_items_per_page => 0, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'rest-v2', p_pattern => 'employees/', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'rest-v2', p_pattern => 'employees/', 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:
- SQL Developer Command-Line Quick Reference
- Oracle REST Data Services (ORDS) : SQLcl and ORDS
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- SQLcl : All Articles
Hope this helps. Regards Tim...