DBA Hub

📋Steps in this guide1/9

Oracle REST Data Services (ORDS) : RESTful Web Services Handling Complex JSON Payloads

This article describes how to create RESTful web services that can process complex JSON payloads representing complete transactions.

oracle miscconfigurationintermediate
by OracleDba
17 views
1

Assumptions and Comments

This article assumes the following.
2

Create a Test Database User

We need a new database user for our testing. Create and populate a copy of the EMP and DEPT tables.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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;

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;
3

Enable ORDS

We are going to assume ORDS is installed and is available from the following base URL. 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". Web services from the schema can now be referenced using the following base URL. We are now ready to start.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
http://localhost:8080/ords/

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;
/

http://localhost:8080/ords/hr/
4

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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;
/
5

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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;
/
6

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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;
/
7

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".

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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;
/
8

Test It

Create a file called "/tmp/create-payload.json" with the following contents. This represents two new departments with their employees. The web service can be called using the following URL, method, header and payload. Checking the tables, we can see the new data has been inserted. You will need to delete the new records if you want to run the test again.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
{
  "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
          }
        ]
      }
    }
  ]
}

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

$

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>

DELETE FROM emp
WHERE  empno >= 9000;

DELETE FROM dept
WHERE  deptno IN (70, 80);

COMMIT;
9

Complex Output

There are several options for generating complete JSON from a web service. - Build the JSON manually using the 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 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 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 expression. This is accessible from a browser URL or curl using the following URL. This will result in the following output. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
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;
/

http://localhost:8080/ords/hr/rest-v1/departments/

{
    "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
}

Comments (0)

Please to add comments

No comments yet. Be the first to comment!