8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : AutoREST

This article gives an overview of the AutoREST functionality of Oracle REST Data Services (ORDS), which allows you to automatically expose tables and views as RESTful web services.

I believe REST enabling tables and views is a mistake. You should make PL/SQL APIs available as RESTful web services, not individual tables. A RESTful web services should expose a real-world interaction, not individual commands against a table.

Related articles.

Assumptions and Comments

This article assumes the following.

The AutoREST functionality targets individual tables and views, effectively providing a table API. Applications that make multiple calls from the application server to the database to perform what should be a single transaction are a bit of a disaster from a performance perspective. In my opinion it is better to code manual handlers that call transactional APIs, which may perform multiple operations in a single call.

Create a Test Database User

We need a new database user for our testing.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

DROP USER testuser2 CASCADE;
CREATE USER testuser2 IDENTIFIED BY testuser2
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE TABLE TO testuser2;

Create and populate a copy of the EMP table.

CONN testuser2/testuser2@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 and AutoREST

There are a number of steps that are necessary to enable ORDS and the AutoREST functionality.

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
Dec 24, 2016 5:15:23 PM
INFO: Updated configurations: pdb1_pu
Dec 24, 2016 5:15:23 PM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.9.348.07.16 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
Dec 24, 2016 5:16:11 PM
INFO: Creating new mapping from: [base-path,/pdb1] to map to: [pdb1,,]
$

Web services can now be referenced using the following base URL.

http://ol7-121.localdomain:8080/ords/pdb1/

Enable REST web services for the test schema. 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 testuser2/testuser2@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'TESTUSER2',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'testuser2',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

Web services from the schema can now be referenced using the following base URL.

http://ol7-121.localdomain:8080/ords/pdb1/testuser2/

The final step is to enable AutoREST for the EMP table.

BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE, -- Default  { TRUE | FALSE }
    p_schema       => 'TESTUSER2',
    p_object       => 'EMP',
    p_object_type  => 'TABLE', -- Default  { TABLE | VIEW }
    p_object_alias => 'employees'
  );
    
  COMMIT;
END;
/

Notice the object is called EMP, but we want the web service to refer to it as "employees", hence the object alias. To disable AutoREST repeat the call with the P_ENABLED parameter set to FALSE.

We are now ready to start.

GET Web Services (READ)

By default browsers use the GET method for HTTP calls, so the following URLs can be called from a browser URL bar.

The following URLs return JSON documents containing metadata about the objects in the test schema the specified object structure respectively.

Available Objects : http://ol7-121.localdomain:8080/ords/pdb1/testuser2/metadata-catalog/
Object Description: http://ol7-121.localdomain:8080/ords/pdb1/testuser2/metadata-catalog/employees/

There are a variety of ways to query data from an AutoREST enabled table or view. The following URL returns all the data from the EMP table. Remember, the object alias was set to "employees".

http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/

The data from an individual row is returned using the primary key value. A comma-separated list is used for concatenated keys.

http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/7521

It's possible to page through data using the offset and limit parameters. The following URL returns a page of 5 rows of data from the EMP table, starting at row 6.

http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/?offset=5&limit=5

There are a variety of operators that can be used to filter the data returned from the object (here). A few examples are shown below.

# job = 'MANAGER'
http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/?q={"job":"MANAGER"}

# salary >= 3000
http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/?q={"sal":{"$gte":3000}}

# job = 'MANAGER' AND salary >= 2000
http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/?q={"job":"MANAGER","sal":{"$gte":2000}}

# Top paid manager.
http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/?q={"job":"MANAGER","$orderby":{"sal":"desc"}}&offset=0&limit=1

POST Web Services (INSERT)

New records are created using the POST method. The URL, method, header and payload necessary to do this are displayed below.

URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "HALL", "job": "ANALYST", "mgr": 7782, "hiredate": "01-JAN-2016", "sal": 1000, "comm": null, "deptno": 10 }

If the payload is placed in a file called "/tmp/insert-payload.json", the following "curl" command will insert a row into the EMP table.

$ curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Location: http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/9999
ETag: "LrUFzrlvUWKDl8yIQWriVCbyGqK7Phzh5S/H/out3bWUcMbQTGmtqFD2TvNGioU/zaYFwgiqE79yj9ygs5U2UQ=="
Location: http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/9999
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 25 Jun 2016 17:04:49 GMT

{"empno":9999,"ename":"HALL","job":"ANALYST","mgr":7782,"hiredate":"2016-01-01T00:00:00Z","sal":1000,"comm":null,"deptno":10,"links":[{"rel":"self","href":"http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/9999"},{"rel":"edit","href":"http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/9999"},{"rel":"describedby","href":"http://ol7-121.localdomain:8080/ords/pdb1/testuser2/metadata-catalog/employees/item"},{"rel":"collection","href":"http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/"}]}
$

In addition to the web service output, we can see the row has been created by querying the table.

SELECT * FROM emp WHERE empno = 9999;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 HALL       ANALYST	      7782 01-JAN-16	   1000 		   10

SQL>

PUT Web Services (UPDATE)

Records are updated, or inserted if they are missing, using the PUT method. The URL, method, header and payload necessary to do this are displayed below.

URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/9999
Method     : PUT
Header     : Content-Type: application/json
Raw Payload: { "ename": "WOOD", "deptno": 20 }

Notice the row to be updated is determined by the URL, in a similar way to a GET call using the primary key. Excluding the PK columns, any columns not specified in the payload are set to null.

If the payload is placed in a file called "/tmp/update-payload.json", the following "curl" command will update a row into the EMP table.

$ curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/9999
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Location: http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/9999
ETag: "+/HytdM4atnPSuuDDaUrG5ZQNF9DtlWhM3NAalo3vqQ7a0ICHNrscmma+1ktAQVOnD66H+Pz88FahM96Ch9dDw=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 25 Jun 2016 17:10:55 GMT

{"empno":9999,"ename":"WOOD","job":null,"mgr":null,"hiredate":null,"sal":null,"comm":null,"deptno":20,"links":[{"rel":"self","href":"http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/9999"},{"rel":"edit","href":"http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/9999"},{"rel":"describedby","href":"http://ol7-121.localdomain:8080/ords/pdb1/testuser2/metadata-catalog/employees/item"},{"rel":"collection","href":"http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/"}]}
$

In addition to the web service output, we can see the row has been updated by querying the table.

SELECT * FROM emp WHERE empno = 9999;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 WOOD 								   20

SQL>

DELETE Web Services (DELETE)

Records are deleted using the DELETE method. The URL and method necessary to do this are displayed below. If you specify additional header or payload information the web service call may fail.

URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/?q={"empno":9999}
Method     : DELETE

The following "curl" command will delete a row from the EMP table. The URL is an encoded version of the one shown above.

$ curl -i -X DELETE  http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/?q=%7B%22empno%22%3A9999%7D
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 25 Jun 2016 17:15:26 GMT

{"rowsDeleted":1}
$

In addition to the web service output, we can see the row has been deleted by querying the table.

SELECT * FROM emp WHERE empno = 9999;

no rows selected

SQL>

Batch Load

In addition to basic DML and queries, it's possible to upload batches of data using AutoREST. The URL, method, header and payload necessary to do this are displayed below. Notice the payload is CSV data.

URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/batchload?dateFormat="DD-MON-YYYY"
Method     : POST
Header     : Content-Type text/csv
Raw Payload:
empno,ename,job,mgr,hiredate,sal,comm,deptno
9990,JONES,CLERK,,24-JUN-2016,1000,,20
9991,SMITH,CLERK,,24-JUN-2016,1000,,20
9992,DAVIS,CLERK,,24-JUN-2016,1000,,20
9993,BROWN,CLERK,,24-JUN-2016,1000,,20
9994,CLARK,CLERK,,24-JUN-2016,1000,,20

If the payload is placed in a file called "/tmp/data.csv", the following "curl" command will perform a batch load into the EMP table.

$ curl -i -X POST --data-binary @/tmp/data.csv -H "Content-Type: text/csv" http://ol7-121.localdomain:8080/ords/pdb1/testuser2/employees/batchload?dateFormat="DD-MON-YYYY"
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: text/plain
Transfer-Encoding: chunked
Date: Sat, 25 Jun 2016 17:21:27 GMT

#INFO Number of rows processed: 5
#INFO Number of rows in error: 0
0 - SUCCESS: Load processed without errors
$

In addition to the web service output, we can see the rows have been loaded by querying the table.

SELECT * FROM emp WHERE empno > 9000;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9990 JONES      CLERK		   24-JUN-16	   1000 		   20
      9991 SMITH      CLERK		   24-JUN-16	   1000 		   20
      9992 DAVIS      CLERK		   24-JUN-16	   1000 		   20
      9993 BROWN      CLERK		   24-JUN-16	   1000 		   20
      9994 CLARK      CLERK		   24-JUN-16	   1000 		   20

SQL>

The parameters that can be used to influence the batch load are documented here.

Display Enabled Objects

The USER_ORDS_ENABLED_OBJECTS view displays enabled objects.

SET LINESIZE 200
COLUMN parsing_schema FORMAT A20
COLUMN parsing_object FORMAT A20
COLUMN object_alias FORMAT A20
COLUMN type FORMAT A20
COLUMN status FORMAT A10

SELECT parsing_schema,
       parsing_object,
       object_alias,
       type,
       status
FROM   user_ords_enabled_objects
ORDER BY 1, 2;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.