8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle REST Data Services (ORDS) : RESTful Web Services Handling Complex JSON Payloads
When you are learning Oracle REST Data Services (ORDS) most of the examples are relatively simple, involving interactions with a single table. In reality a RESTful web service should represent a whole unit of work, like a complete transaction. This article presents a more complex example, which uses a JSON payload to create multiple departments along with their employees in a single call to the web service.
- Assumptions and Comments
- Create a Test Database User
- Enable ORDS
- Create API using APEX_JSON
- Create API using JSON_TABLE in 12c
- Create API using PL/SQL JSON Objects in 12cR2
- Create POST Web Service
- Test It
- Complex Output
Related articles.
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- Oracle REST Data Services (ORDS) : Installation on Tomcat
- Oracle REST Data Services (ORDS) : Standalone Mode
- Oracle REST Data Services (ORDS) : Configure Multiple Databases
- APEX_JSON Package: Generate and Parse JSON Documents in Oracle
- PL/SQL Object Types for JSON in Oracle Database 12c Release 2 (12.2)
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, All Articles
Assumptions and Comments
This article assumes the following.
- You already have a functioning installation of ORDS, using an application server or standalone mode.
- 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 unless otherwise stated.
- You have a way to call the web services. I used "curl" and the "Advanced REST client" extension for Chrome.
- You are already familiar with creating basic RESTful web services using PL/SQL.
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, CREATE PROCEDURE TO testuser1;
Create and populate a copy of the EMP and DEPT tables.
CONN testuser1/testuser1@pdb1 CREATE TABLE DEPT ( DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13) ) ; CREATE TABLE EMP ( EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT ); INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;
Enable ORDS
We are going to assume ORDS is installed and is available from the following base URL.
http://localhost:8080/ords/
We have to enable Oracle REST data 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. In this example we used "hr".
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; /
Web services from the schema can now be referenced using the following base URL.
http://localhost:8080/ords/hr/
We are now ready to start.
Create API using APEX_JSON
The source of the POST handler can be a regular PL/SQL block containing an insert statement, but it makes sense to enclose this in a stored procedure or packaged procedure. Create a stored procedure to process the payload using the APEX_JSON package. This is available in any database that has APEX 5.x installed.
CONN testuser1/testuser1@pdb1 CREATE OR REPLACE PROCEDURE create_departments (p_data IN BLOB) AS TYPE t_dept_tab IS TABLE OF dept%ROWTYPE; TYPE t_emp_tab IS TABLE OF emp%ROWTYPE; l_dept_tab t_dept_tab := t_dept_tab(); l_emp_tab t_emp_tab := t_emp_tab(); l_clob CLOB; l_dest_offset PLS_INTEGER := 1; l_src_offset PLS_INTEGER := 1; l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx; l_warning PLS_INTEGER; l_dept_count PLS_INTEGER; l_emp_count PLS_INTEGER; BEGIN -- Convert the BLOB to a CLOB. DBMS_LOB.createtemporary( lob_loc => l_clob, cache => FALSE, dur => DBMS_LOB.call); DBMS_LOB.converttoclob( dest_lob => l_clob, src_blob => p_data, amount => DBMS_LOB.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset, blob_csid => DBMS_LOB.default_csid, lang_context => l_lang_context, warning => l_warning); APEX_JSON.parse(l_clob); -- Loop through all the departments. l_dept_count := APEX_JSON.get_count(p_path => 'departments'); FOR i IN 1 .. l_dept_count LOOP l_dept_tab.extend; l_dept_tab(l_dept_tab.last).deptno := APEX_JSON.get_number(p_path => 'departments[%d].department.department_no', p0 => i); l_dept_tab(l_dept_tab.last).dname := APEX_JSON.get_varchar2(p_path => 'departments[%d].department.department_name', p0 => i); l_emp_count := APEX_JSON.get_count(p_path => 'departments[%d].department.employees', p0 => i); -- Loop through all the employees for the current department. FOR j IN 1 .. l_emp_count LOOP l_emp_tab.extend; l_emp_tab(l_emp_tab.last).deptno := l_dept_tab(l_dept_tab.last).deptno; l_emp_tab(l_emp_tab.last).empno := APEX_JSON.get_number(p_path => 'departments[%d].department.employees[%d].employee_number', p0 => i, p1 => j); l_emp_tab(l_emp_tab.last).ename := APEX_JSON.get_varchar2(p_path => 'departments[%d].department.employees[%d].employee_name', p0 => i, p1 => j); l_emp_tab(l_emp_tab.last).sal := APEX_JSON.get_number(p_path => 'departments[%d].department.employees[%d].salary', p0 => i, p1 => j); l_emp_tab(l_emp_tab.last).hiredate := SYSDATE; END LOOP; END LOOP; -- Populate the tables. FORALL i IN l_dept_tab.first .. l_dept_tab.last INSERT INTO dept VALUES l_dept_tab(i); FORALL i IN l_emp_tab.first .. l_emp_tab.last INSERT INTO emp VALUES l_emp_tab(i); COMMIT; DBMS_LOB.freetemporary(lob_loc => l_clob); EXCEPTION WHEN OTHERS THEN HTP.print(SQLERRM); END; /
Create API using JSON_TABLE in 12c
If you are using Oracle database 12c you could use the JSON_TABLE function to parse the JSON payload.
CREATE OR REPLACE PROCEDURE create_departments (p_data IN BLOB) AS BEGIN INSERT INTO dept (deptno, dname) SELECT * FROM json_table(p_data FORMAT JSON, '$.departments[*].department' COLUMNS ( deptno NUMBER PATH '$.department_no', dname VARCHAR2 PATH '$.department_name')); INSERT INTO emp (deptno, empno, ename, sal, hiredate) SELECT j.*, SYSDATE AS hiredate FROM json_table(p_data FORMAT JSON, '$.departments[*].department' COLUMNS ( deptno NUMBER PATH '$.department_no', NESTED PATH '$.employees[*]' COLUMNS ( empno NUMBER PATH '$.employee_number', ename VARCHAR2 PATH '$.employee_name', sal NUMBER PATH '$.salary'))) j; COMMIT; EXCEPTION WHEN OTHERS THEN HTP.print(SQLERRM); END; /
Create API using PL/SQL JSON Objects in 12cR2
If you are using Oracle database 12c release 2 you could use the PL/SQL JSON objects to parse the JSON payload.
CREATE OR REPLACE PROCEDURE create_departments (p_data IN BLOB) AS TYPE t_dept_tab IS TABLE OF dept%ROWTYPE; TYPE t_emp_tab IS TABLE OF emp%ROWTYPE; l_dept_tab t_dept_tab := t_dept_tab(); l_emp_tab t_emp_tab := t_emp_tab(); l_top_obj JSON_OBJECT_T; l_dept_arr JSON_ARRAY_T; l_dept_obj JSON_OBJECT_T; l_emp_arr JSON_ARRAY_T; l_emp_obj JSON_OBJECT_T; BEGIN l_top_obj := JSON_OBJECT_T(p_data); l_dept_arr := l_top_obj.get_array('departments'); FOR i IN 0 .. l_dept_arr.get_size - 1 LOOP l_dept_tab.extend; l_dept_obj := TREAT(l_dept_arr.get(i) AS JSON_OBJECT_T).get_object('department'); l_dept_tab(l_dept_tab.last).deptno := l_dept_obj.get_number('department_no'); l_dept_tab(l_dept_tab.last).dname := l_dept_obj.get_string('department_name'); l_emp_arr := l_dept_obj.get_array('employees'); FOR j IN 0 .. l_emp_arr.get_size - 1 LOOP l_emp_obj := TREAT(l_emp_arr.get(j) AS JSON_OBJECT_T); l_emp_tab.extend; l_emp_tab(l_emp_tab.last).deptno := l_dept_tab(l_dept_tab.last).deptno; l_emp_tab(l_emp_tab.last).empno := l_emp_obj.get_number('employee_number'); l_emp_tab(l_emp_tab.last).ename := l_emp_obj.get_string('employee_name'); l_emp_tab(l_emp_tab.last).sal := l_emp_obj.get_number('salary'); l_emp_tab(l_emp_tab.last).hiredate := SYSDATE; END LOOP; END LOOP; -- Populate the tables. FORALL i IN l_dept_tab.first .. l_dept_tab.last INSERT INTO dept VALUES l_dept_tab(i); FORALL i IN l_emp_tab.first .. l_emp_tab.last INSERT INTO emp VALUES l_emp_tab(i); COMMIT; EXCEPTION WHEN OTHERS THEN HTP.print(SQLERRM); END; /
Create POST Web Service
The following code creates a web service with a POST handler that calls the stored procedure, passing the whole payload as a single parameter. The bind variable used for the payload must be called "body".
CONN testuser1/testuser1@pdb1 BEGIN ORDS.define_module( p_module_name => 'rest-v1', p_base_path => 'rest-v1/', p_items_per_page => 0); ORDS.define_template( p_module_name => 'rest-v1', p_pattern => 'departments/'); ORDS.define_handler( p_module_name => 'rest-v1', p_pattern => 'departments/', p_method => 'POST', p_source_type => ORDS.source_type_plsql, p_source => 'BEGIN create_departments(p_data => :body); END;', p_items_per_page => 0); COMMIT; END; /
Test It
Create a file called "/tmp/create-payload.json" with the following contents. This represents two new departments with their employees.
{ "departments":[ { "department":{ "department_name":"DEV", "department_no":70, "employees":[ { "employee_number":9000, "employee_name":"JONES", "salary":1000 }, { "employee_number":9001, "employee_name":"SMITH", "salary":2000 } ] } }, { "department":{ "department_name":"DBA", "department_no":80, "employees":[ { "employee_number":9002, "employee_name":"HALL", "salary":3000 } ] } } ] }
The web service can be called using the following URL, method, header and payload.
URL : http://localhost:8080/ords/hr/rest-v1/departments/ Method : POST Header : Content-Type: application/json Raw Payload: /tmp/create-payload.json $ curl -i -X POST --data-binary @/tmp/create-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v1/departments/ HTTP/1.1 200 OK Transfer-Encoding: chunked $
Checking the tables, we can see the new data has been inserted.
SELECT * FROM dept WHERE deptno > 40; DEPTNO DNAME LOC ---------- -------------- ------------- 70 DEV 80 DBA 2 rows selected. SQL> SELECT * FROM emp WHERE empno >= 9000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------------- ---------- ---------- ---------- 9000 JONES 17-DEC-2016 22:09:58 1000 70 9001 SMITH 17-DEC-2016 22:09:58 2000 70 9002 HALL 17-DEC-2016 22:09:58 3000 80 3 rows selected. SQL>
You will need to delete the new records if you want to run the test again.
DELETE FROM emp WHERE empno >= 9000; DELETE FROM dept WHERE deptno IN (70, 80); COMMIT;
Complex Output
There are several options for generating complete JSON from a web service.
- Build the JSON manually using the
APEX_JSON
package and push it out using ORDS as a gateway (here). - Build the JSON using SQL/JSON and push it out using ORDS as a gateway (here).
- Build the JSON using the PL/SQL Object Types for JSON and push it out using ORDS as a gateway (here)
- Use the
CURSOR
function in SQL to produce nested JSON documents via ORDS, explained below.
If you are manually building JSON you are bypassing some of the functionality of ORDS. It is up to use to push the JSON text out through the gateway, as you would for a regular Web Toolkit application. You can see examples of this here.
The CURSOR
expression can be used in a query to generate nested JSON. The example below creates a list of departments,
with each department containing a list of department employees. The employees are represented by a ref cursor defined using the CURSOR
expression.
CONN testuser1/testuser1@pdb1 BEGIN ORDS.define_module( p_module_name => 'rest-v1', p_base_path => 'rest-v1/', p_items_per_page => 0); ORDS.define_template( p_module_name => 'rest-v1', p_pattern => 'departments/'); ORDS.define_handler( p_module_name => 'rest-v1', p_pattern => 'departments/', p_method => 'GET', p_source_type => ORDS.source_type_collection_feed, p_source => 'SELECT d.dname AS "department_name", d.deptno AS "department_number", d.deptno AS "$self", CURSOR(SELECT e.empno AS "employee_number", e.ename AS "employee_name", e.empno AS "$self" FROM emp e WHERE e.deptno = d.deptno ORDER BY e.empno) AS "employees" FROM dept d ORDER BY d.dname', p_items_per_page => 0); COMMIT; END; /
This is accessible from a browser URL or curl using the following URL.
http://localhost:8080/ords/hr/rest-v1/departments/
This will result in the following output.
{ "count": 4, "hasMore": false, "items": [ { "department_name": "ACCOUNTING", "department_number": 10, "employees": [ { "employee_name": "CLARK", "employee_number": 7782, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7782", "rel": "self" } ] }, { "employee_name": "KING", "employee_number": 7839, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7839", "rel": "self" } ] }, { "employee_name": "MILLER", "employee_number": 7934, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7934", "rel": "self" } ] } ], "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/10", "rel": "self" } ] }, { "department_name": "OPERATIONS", "department_number": 40, "employees": [], "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/40", "rel": "self" } ] }, { "department_name": "RESEARCH", "department_number": 20, "employees": [ { "employee_name": "SMITH", "employee_number": 7369, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7369", "rel": "self" } ] }, { "employee_name": "JONES", "employee_number": 7566, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7566", "rel": "self" } ] }, { "employee_name": "SCOTT", "employee_number": 7788, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7788", "rel": "self" } ] }, { "employee_name": "ADAMS", "employee_number": 7876, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7876", "rel": "self" } ] }, { "employee_name": "FORD", "employee_number": 7902, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7902", "rel": "self" } ] } ], "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/20", "rel": "self" } ] }, { "department_name": "SALES", "department_number": 30, "employees": [ { "employee_name": "ALLEN", "employee_number": 7499, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7499", "rel": "self" } ] }, { "employee_name": "WARD", "employee_number": 7521, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7521", "rel": "self" } ] }, { "employee_name": "MARTIN", "employee_number": 7654, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7654", "rel": "self" } ] }, { "employee_name": "BLAKE", "employee_number": 7698, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7698", "rel": "self" } ] }, { "employee_name": "TURNER", "employee_number": 7844, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7844", "rel": "self" } ] }, { "employee_name": "JAMES", "employee_number": 7900, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/7900", "rel": "self" } ] } ], "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/30", "rel": "self" } ] } ], "limit": 0, "links": [ { "href": "http://localhost:8080/ords/hr/rest-v1/departments/", "rel": "self" }, { "href": "http://localhost:8080/ords/hr/metadata-catalog/rest-v1/departments/", "rel": "describedby" } ], "offset": 0 }
For more information see:
- ORDS PL/SQL Package Reference
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- Oracle REST Data Services (ORDS) : Installation on Tomcat
- Oracle REST Data Services (ORDS) : Standalone Mode
- Oracle REST Data Services (ORDS) : Configure Multiple Databases
- APEX_JSON Package: Generate and Parse JSON Documents in Oracle
- PL/SQL Object Types for JSON in Oracle Database 12c Release 2 (12.2)
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, All Articles
Hope this helps. Regards Tim...