Assumptions and Comments
This article assumes the following.
- You already have a functioning installation of ORDS.
- The paths for the ORDS configuration match those from the ORDS installation article listed above.
- You have an Oracle database available. In this article I will be using a 12c database, but it works just the same with an 11g database.
- You have a way to call the web services. Some GET web services can be called from a browser, but the other methods require some coding, or a REST client. I used "curl" and the "Advanced REST client" extension for Chrome.
The examples in this article are based around single operations against a single table. I don't consider these "realistic" tests by any means. From a performance perspective, web services that perform DML should call transactional APIs, which may perform multiple operations in a single call, rather than effectively exposing table APIs, like we are here.
Create a Test Database User
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 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
There are a number of steps that are necessary for ORDS to publish REST web services from a database user.
Installing ORDS configures a default database connection called "apex", available from the base URL "/ords/". We could use this existing database connection, but instead we will create a new database connection. If you already have a connection you are happy to use ignore the following database connection and URL mapping ORDS commands.
$ cd /u01/ords $ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1 Enter the name of the database server [ol7-121.localdomain]: Enter the database listen port [1521]: Enter 1 to specify the database service name, or 2 to specify the database SID [1]: Enter the database service name [pdb1]: Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]: Enter the database password for ORDS_PUBLIC_USER: Confirm password: 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 Nov 29, 2016 11:04:54 AM INFO: Updated configurations: pdb1_pu Nov 29, 2016 11:04:54 AM oracle.dbtools.rt.config.setup.SchemaSetup install INFO: Oracle REST Data Services schema version 3.0.8.277.08.01 is installed. $
We create a URL mapping to the new database connection.
$ cd /u01/ords $ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /pdb1 pdb1 Nov 29, 2016 11:06:25 AM INFO: Creating new mapping from: [base-path,/pdb1] to map to: [pdb1,,] $
Web services from the connection can now be referenced using the following base URL.
http://ol7-121.localdomain:8080/ords/pdb1/
The final step is to enable REST web services for the test schema itself. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name as we have done here.
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; /
Web services fr