8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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 often 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. Of course, it all depends on your use case.
- Assumptions and Comments
- Create a Test Database User
- Enable ORDS and AutoREST
- GET Web Services (READ)
- POST Web Services (INSERT)
- PUT Web Services (UPDATE)
- DELETE Web Services (DELETE)
- Batch Load
- Display Enabled Objects
Related articles.
- Oracle REST Data Services (ORDS) : AutoREST
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Installation on Tomcat
- Oracle REST Data Services (ORDS) : Configure Multiple Databases
- Oracle REST Data Services (ORDS) : Auto PL/SQL
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- Oracle REST Data Services (ORDS) : Authentication
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 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
Enable REST web services for the test schema. We use any unique and legal URL mapping pattern for the schema, so we don't expose the schema name. In this case we use "hr" as the schema alias.
CONN testuser2/testuser2@pdb1 BEGIN ORDS.enable_schema( p_enabled => TRUE, p_schema => 'TESTUSER2', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE ); COMMIT; END; /
Web services from the schema can now be referenced using the following base URL.
http://localhost:8080/ords/hr/
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://localhost:8080/ords/hr/metadata-catalog/ Object Description: http://localhost:8080/ords/hr/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://localhost:8080/ords/hr/employees/
The data from an individual row is returned using the primary key value. A comma-separated list is used for concatenated keys.
http://localhost:8080/ords/hr/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://localhost:8080/ords/hr/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://localhost:8080/ords/hr/employees/?q={"job":"MANAGER"} # salary >= 3000 http://localhost:8080/ords/hr/employees/?q={"sal":{"$gte":3000}} # job = 'MANAGER' AND salary >= 2000 http://localhost:8080/ords/hr/employees/?q={"job":"MANAGER","sal":{"$gte":2000}} # Top paid manager. http://localhost:8080/ords/hr/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://localhost:8080/ords/hr/employees/ Method : POST Header : Content-Type: application/json Raw Payload: { "empno": 9999, "ename": "HALL", "job": "ANALYST", "mgr": 7782, "hiredate": "2016-01-01T00:00:00Z", "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://localhost:8080/ords/hr/employees/ HTTP/1.1 200 OK Server: Apache-Coyote/1.1 Content-Location: http://localhost:8080/ords/hr/employees/9999 ETag: "LrUFzrlvUWKDl8yIQWriVCbyGqK7Phzh5S/H/out3bWUcMbQTGmtqFD2TvNGioU/zaYFwgiqE79yj9ygs5U2UQ==" Location: http://localhost:8080/ords/hr/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://localhost:8080/ords/hr/employees/9999"},{"rel":"edit","href":"http://localhost:8080/ords/hr/employees/9999"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/employees/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/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://localhost:8080/ords/hr/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://localhost:8080/ords/hr/employees/9999 HTTP/1.1 200 OK Server: Apache-Coyote/1.1 Content-Location: http://localhost:8080/ords/hr/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://localhost:8080/ords/hr/employees/9999"},{"rel":"edit","href":"http://localhost:8080/ords/hr/employees/9999"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/employees/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/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. There are two variations shown. The first is similar to the PUT method, adding the primary key value into the URL. The second uses a query string to target the row(s) to delete. If you specify additional header or payload information the web service call may fail.
URL : http://localhost:8080/ords/hr/employees/9999 Method : DELETE URL : http://localhost:8080/ords/hr/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://localhost:8080/ords/hr/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://localhost:8080/ords/hr/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://localhost:8080/ords/hr/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:
- Automatic Enabling of Schema Objects for REST Access (AutoREST)
- Oracle REST Data Services (ORDS) : AutoREST
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Installation on Tomcat
- Oracle REST Data Services (ORDS) : Configure Multiple Databases
- Oracle REST Data Services (ORDS) : Auto PL/SQL
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- Oracle REST Data Services (ORDS) : Authentication
Hope this helps. Regards Tim...