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

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.

Related articles.

Assumptions and Comments

This article assumes the following.

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

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 from the schema can now be referenced using the following base URL.

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

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    => 'testmodule1',
    p_base_path      => 'testmodule1/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule1',
   p_pattern        => 'departments/');

  ORDS.define_handler(
    p_module_name    => 'testmodule1',
    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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule1/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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule1/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.

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    => 'testmodule2',
    p_base_path      => 'testmodule2/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule2',
   p_pattern        => 'departments/');

  ORDS.define_handler(
    p_module_name    => 'testmodule2',
    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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7782",
                            "rel": "self"
                        }
                    ]
                },
                {
                    "employee_name": "KING",
                    "employee_number": 7839,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7839",
                            "rel": "self"
                        }
                    ]
                },
                {
                    "employee_name": "MILLER",
                    "employee_number": 7934,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7934",
                            "rel": "self"
                        }
                    ]
                }
            ],
            "links": [
                {
                    "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/10",
                    "rel": "self"
                }
            ]
        },
        {
            "department_name": "OPERATIONS",
            "department_number": 40,
            "employees": [],
            "links": [
                {
                    "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/40",
                    "rel": "self"
                }
            ]
        },
        {
            "department_name": "RESEARCH",
            "department_number": 20,
            "employees": [
                {
                    "employee_name": "SMITH",
                    "employee_number": 7369,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7369",
                            "rel": "self"
                        }
                    ]
                },
                {
                    "employee_name": "JONES",
                    "employee_number": 7566,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7566",
                            "rel": "self"
                        }
                    ]
                },
                {
                    "employee_name": "SCOTT",
                    "employee_number": 7788,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7788",
                            "rel": "self"
                        }
                    ]
                },
                {
                    "employee_name": "ADAMS",
                    "employee_number": 7876,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7876",
                            "rel": "self"
                        }
                    ]
                },
                {
                    "employee_name": "FORD",
                    "employee_number": 7902,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7902",
                            "rel": "self"
                        }
                    ]
                }
            ],
            "links": [
                {
                    "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/20",
                    "rel": "self"
                }
            ]
        },
        {
            "department_name": "SALES",
            "department_number": 30,
            "employees": [
                {
                    "employee_name": "ALLEN",
                    "employee_number": 7499,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7499",
                            "rel": "self"
                        }
                    ]
                },
                {
                    "employee_name": "WARD",
                    "employee_number": 7521,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7521",
                            "rel": "self"
                        }
                    ]
                },
                {
                    "employee_name": "MARTIN",
                    "employee_number": 7654,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7654",
                            "rel": "self"
                        }
                    ]
                },
                {
                    "employee_name": "BLAKE",
                    "employee_number": 7698,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7698",
                            "rel": "self"
                        }
                    ]
                },
                {
                    "employee_name": "TURNER",
                    "employee_number": 7844,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7844",
                            "rel": "self"
                        }
                    ]
                },
                {
                    "employee_name": "JAMES",
                    "employee_number": 7900,
                    "links": [
                        {
                            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/7900",
                            "rel": "self"
                        }
                    ]
                }
            ],
            "links": [
                {
                    "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/30",
                    "rel": "self"
                }
            ]
        }
    ],
    "limit": 0,
    "links": [
        {
            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/departments/",
            "rel": "self"
        },
        {
            "href": "http://ol7-121.localdomain:8080/ords/pdb1/testuser1/metadata-catalog/testmodule2/departments/",
            "rel": "describedby"
        }
    ],
    "offset": 0
}

For more information see:

Hope this helps. Regards Tim...

Back to the Top.