Home » Articles » Misc » Here

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, 23c, 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.

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, 23c, All Articles

Hope this helps. Regards Tim...

Back to the Top.

Created: 2016-12-17  Updated: 2019-12-14

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer