📋Steps in this guide1/5

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 theREST command

oracle miscconfigurationintermediate

by OracleDba

13 views

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. Create and populate a copy of the EMP table. Enable ORDS for the new schema.

Code/Command (click line numbers to comment):

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;

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;

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.

Code/Command (click line numbers to comment):

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 command can provide some basic information, including REST-enabled schemas, modules and privileges.

Code/Command (click line numbers to comment):

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 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 call and uses the full creation syntax, not the call. The output can be limited by specifying module of interest. Alternatively the base path of the module of interest can be used. 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...

Code/Command (click line numbers to comment):

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>

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!