DBA Hub

📋Steps in this guide1/11

Oracle REST Data Services (ORDS) : AutoREST of JSON-Relational Duality Views

This article gives an overview of the AutoREST functionality of Oracle REST Data Services (ORDS) against JSON-relational duality views in Oracle 23ai.

oracle miscconfigurationintermediate
by OracleDba
24 views
1

Assumptions and Comments

This article assumes the following. - You have an Oracle 23ai database available.. - You already have a functioning installation of ORDS . This will need to be version 23.1 or higher. - The paths for the ORDS configuration match those from the ORDS installation article listed above. - 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. We used "curl" in this article. - You have a basic understanding of JSON-relational duality views and AutoREST from the linked articles.
2

Create a Test Database User

We need a new database user for our testing. Create and populate a copy of the and tables. Create a JSON-relational duality view against the base tables. You can get more information about JSON-relational duality views here . We've purposely excluded some of the optional columns to make things a little simpler. Notice the view references the departments table, but includes a list of all employees in the department. So this maps to a real-world object, not just a single table.

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
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop user if exists testuser2 cascade;
create user testuser2 identified by testuser2
  default tablespace users quota unlimited on users;
  
grant connect, resource to testuser2;

conn testuser2/testuser2@//localhost:1521/freepdb1

drop table if exists emp purge;
drop table if exists dept purge;

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

create index emp_dept_fk_i on emp(deptno);

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;

drop view if exists department_dv;

create json relational duality view department_dv as
select json {'departmentNumber' : d.deptno,
             'departmentName'   : d.dname,
             'location'         : d.loc,
             'employees' :
               [ select json {'employeeNumber' : e.empno,
                              'employeeName'   : e.ename,
                              'job'            : e.job,
                              'salary'         : e.sal}
                 from   emp e with insert update delete
                 where  d.deptno = e.deptno ]}
from dept d with insert update delete;
3

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. Web services from the schema can now be referenced using the following base URL. The final step is to enable AutoREST for the JSON-relational duality view. This is done the same way as AutoREST enabling any other view. Notice the object is called , but we want the web service to refer to it as "departments", hence the object alias. To disable AutoREST repeat the call with the parameter set to FALSE. 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
19
20
21
22
23
24
25
26
27
28
29
conn testuser2/testuser2@//localhost:1521/freepdb1

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

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

begin
  ords.enable_object (
    p_enabled      => TRUE, -- Default  { TRUE | FALSE }
    p_schema       => 'TESTUSER2',
    p_object       => 'DEPARTMENT_DV',
    p_object_type  => 'VIEW', -- Default  { TABLE | VIEW }
    p_object_alias => 'departments'
  );
    
  commit;
end;
/
4

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. 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 view. Remember, the object alias was set to "departments". The data from an individual row is returned using the primary key value. A comma-separated list is used for concatenated keys. It's possible to page through data using the offset and limit parameters. The following URL returns a page of 2 rows of data from the view, starting at row 3. There are a variety of operators that can be used to filter the data returned from the object ( here ). Depending on you client, you may need to encode parts of the URI.

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
Available Objects : http://localhost:8080/ords/hr/metadata-catalog/
Object Description: http://localhost:8080/ords/hr/metadata-catalog/departments/

http://localhost:8080/ords/hr/departments/

http://localhost:8080/ords/hr/departments/10

http://localhost:8080/ords/hr/departments/?offset=2&limit=2

# departmentName = 'SALES'
Normal : http://localhost:8080/ords/hr/departments/?q={"items.departmentName":"SALES"}
Encoded: http://localhost:8080/ords/hr/departments/?q=%7B%22departmentName%22:%22SALES%22%7D

# departmentNumber >= 20
Normal : http://localhost:8080/ords/hr/departments/?q={"departmentNumber":{"$gte":30}}
Encoded: http://localhost:8080/ords/hr/departments/?q=%7B%22departmentNumber%22:%7B%22$gte%22:30%7D%7D

# departmentName = 'SALES' AND departmentNumber >= 30
Normal : http://localhost:8080/ords/hr/departments/?q={"departmentName":"SALES","departmentNumber":{"$gte":30}}
Encoded: http://localhost:8080/ords/hr/departments/?q=%7B%22departmentName%22:%22SALES%22,%22departmentNumber%22:%7B%22$gte%22:30%7D%7D
5

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. If the payload is placed in a file called "/tmp/insert-payload.json", the following "curl" command will add a department via the view. In addition to the web service output, we can see rows have been created in the base 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
URL        : http://localhost:8080/ords/hr/departments/
Method     : POST
Header     : Content-Type: application/json
Raw Payload:
{
  "departmentNumber" : 50,
  "departmentName" : "DBA",
  "location" : "BIRMINGHAM",
  "employees" : [
    {
      "employeeNumber" : 9999,
      "employeeName" : "HALL",
      "job" : "CLERK",
      "salary" : 500
    }
  ]
}

$
curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/departments/
HTTP/1.1 201
Content-Location: http://localhost:8080/ords/hr/departments/50
ETag: "77052B06E84B60749E410D5C2BA797DF"
Location: http://localhost:8080/ords/hr/departments/50
Cache-Control: max-age=0
Expires: Wed, 12 Apr 2023 09:55:13 GMT
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 12 Apr 2023 09:55:13 GMT

{"departmentNumber":50,"departmentName":"DBA","location":"BIRMINGHAM","employees":[{"employeeNumber":9999,"employeeName":"HALL","job":"CLERK","salary":500}],"_metadata":{"etag":"77052B06E84B60749E410D5C2BA797DF","asof":"00000000002710B9"},"links":[{"rel":"self","href":"http://localhost:8080/ords/hr/departments/50"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/departments/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/departments/"}]}$

select * from dept where deptno = 50;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 DBA            BIRMINGHAM

SQL>


select * from emp where empno = 9999;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 HALL       CLERK                                 500                    50

SQL>
6

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. 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 add a new employee to department 40 via the view. In addition to the web service output, we can see the row has been updated by querying the table.

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
URL        : http://localhost:8080/ords/hr/departments/50
Method     : PUT
Header     : Content-Type: application/json
Raw Payload: 
{
  "departmentNumber" : 40,
  "departmentName" : "OPERATIONS",
  "location" : "BOSTON",
  "employees" : [
    {
      "employeeNumber" : 9998,
      "employeeName" : "WOOD",
      "job" : "CLERK",
      "salary" : 500
    }
  ]
}

$
curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/departments/40
HTTP/1.1 200
Content-Location: http://localhost:8080/ords/hr/departments/40
ETag: "AAC7DB6EB25FAB98572C2855225DE82B"
Cache-Control: max-age=0
Expires: Wed, 12 Apr 2023 10:06:51 GMT
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 12 Apr 2023 10:06:51 GMT

{"departmentNumber":40,"departmentName":"OPERATIONS","location":"BOSTON","employees":[{"employeeNumber":9998,"employeeName":"WOOD","job":"CLERK","salary":500}],"_metadata":{"etag":"AAC7DB6EB25FAB98572C2855225DE82B","asof":"00000000002713E6"},"links":[{"rel":"self","href":"http://localhost:8080/ords/hr/departments/40"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/departments/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/departments/"}]}$

select * from emp where deptno = 40;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9998 WOOD       CLERK                                 500                    40

SQL>
7

DELETE Web Services (DELETE)

Records are deleted using the DELETE method. The URL and method necessary to do this are displayed below. The following "curl" command will delete a row from the EMP table. The URL is an encoded version of the one shown above. In addition to the web service output, we can see the row has been deleted by querying the table.

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
URL        : http://localhost:8080/ords/hr/departments/50
Method     : DELETE

$
curl -i -X DELETE  http://localhost:8080/ords/hr/departments/50
HTTP/1.1 200
X-Frame-Options: SAMEORIGIN
X-Content-Type-Options: nosniff
X-XSS-Protection: 1; mode=block
Cache-Control: max-age=0
Expires: Wed, 12 Apr 2023 10:11:09 GMT
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 12 Apr 2023 10:11:09 GMT

{"rowsDeleted":1}
$

select * from dept where deptno = 50;

no rows selected

SQL>


select * from emp where deptno = 50;

no rows selected

SQL>
8

Managing State

In all the previous operations we've ignored state, assuming the data is not changing. In reality it's possible the data has changed between our service calls. JSON-relational duality views give us a way to manage the state, providing us with an "etag" which is effectively a version we can use for optimistic locking. The following example shows this. We delete department "50" to give us a clean starting point. We create a new department using a REST call as we did previously. If the payload is placed in a file called "/tmp/insert-payload.json", the following "curl" command will add a department via the view. Notice the resulting "etag" value of "77052B06E84B60749E410D5C2BA797DF". We add another employee to department "50" using a conventional insert. This simulates the data changing between the last time we checked the document. Now we attempt to update the department, passing the original "etag" value in the "_metadata" tag. If the payload is placed in a file called "/tmp/update-payload.json", the following "curl" command will update the employee details in department 50 via the view. The data change has caused the "etag" value to change, so the update caused a "PredconditionFailed" error. In order to proceed, we would have to re-query the data to get the new "etag" value, then try 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
delete from emp where deptno = 50;
delete from dept where deptno = 50;
commit;

URL        : http://localhost:8080/ords/hr/departments/
Method     : POST
Header     : Content-Type: application/json
Raw Payload:
{
  "departmentNumber" : 50,
  "departmentName" : "DBA",
  "location" : "BIRMINGHAM",
  "employees" : [
    {
      "employeeNumber" : 9999,
      "employeeName" : "HALL",
      "job" : "CLERK",
      "salary" : 500
    }
  ]
}

$
curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/departments/
HTTP/1.1 201
Content-Location: http://localhost:8080/ords/hr/departments/50
ETag: "77052B06E84B60749E410D5C2BA797DF"
Location: http://localhost:8080/ords/hr/departments/50
Cache-Control: max-age=0
Expires: Wed, 12 Apr 2023 12:51:57 GMT
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 12 Apr 2023 12:51:57 GMT

{"departmentNumber":50,"departmentName":"DBA","location":"BIRMINGHAM","employees":[{"employeeNumber":9999,"employeeName":"HALL","job":"CLERK","salary":500}],"_metadata":{"etag":"77052B06E84B60749E410D5C2BA797DF","asof":"0000000000274448"},"links":[{"rel":"self","href":"http://localhost:8080/ords/hr/departments/50"},{"rel":"describedby","href":"http://localhost:8080/ords/hr/metadata-catalog/departments/item"},{"rel":"collection","href":"http://localhost:8080/ords/hr/departments/"}]}
$

insert into emp values (9997,'WOOD','CLERK',null,null,1300,null,50);
commit;

URL        : http://localhost:8080/ords/hr/departments/50
Method     : POST
Header     : Content-Type: application/json
Raw Payload:
{
  "_metadata" : {"etag" : "77052B06E84B60749E410D5C2BA797DF"},
  "departmentNumber" : 50,
  "departmentName" : "DBA",
  "location" : "BIRMINGHAM",
  "employees" : [
    {
      "employeeNumber" : 9999,
      "employeeName" : "HALL",
      "job" : "SALESMAN",
      "salary" : 1000
    }
  ]
}

$
curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/departments/50
HTTP/1.1 412
Cache-Control: max-age=0
Expires: Wed, 12 Apr 2023 12:58:40 GMT
Content-Type: application/problem+json
Content-Length: 204
Date: Wed, 12 Apr 2023 12:58:40 GMT

{
    "code": "PredconditionFailed",
    "message": "Predcondition Failed",
    "type": "tag:oracle.com,2020:error/PredconditionFailed",
    "instance": "tag:oracle.com,2020:ecid/qOqFfmt7AEbuGbIfns-vKg"
}
$
9

Batch Load

In addition to basic DML and queries, it's possible to upload batches of data using AutoREST. Make sure the additional departments and employees are removed. The URL, method, header and payload necessary to do this are displayed below. Notice the payload is a JSON array of documents to load. If the payload is placed in a file called "/tmp/data.json", the following "curl" command will perform a batch load into the EMP table. In addition to the web service output, we can see the rows have been loaded by querying the table. The parameters that can be used to influence the batch load are documented here .

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
delete from emp where deptno > 40;
delete from dept where deptno > 40;
commit;

URL        : http://localhost:8080/ords/hr/departments/batchload"
Method     : POST
Header     : Content-Type : application/json
Raw Payload:
[
  {
    "departmentNumber" : 60,
    "departmentName" : "DEVELOPER",
    "location" : "LONDON",
    "employees" : [
      {
        "employeeNumber" : 9997,
        "employeeName" : "SMITH",
        "job" : "MANAGER",
        "salary" : 3000
      }
    ]
  },
  {
    "departmentNumber" : 70,
    "departmentName" : "PROJECTS",
    "location" : "LONDON",
    "employees" : [
      {
        "employeeNumber" : 9996,
        "employeeName" : "JONES",
        "job" : "MANAGER",
        "salary" : 3500
      }
    ]
  },
  {
    "departmentNumber" : 80,
    "departmentName" : "MAINTENANCE",
    "location" : "LONDON",
    "employees" : [
      {
        "employeeNumber" : 9995,
        "employeeName" : "DAVIS",
        "job" : "MAMAGER",
        "salary" : 2500
      }
    ]
  }
]

$
curl -i -X POST --data-binary @/tmp/data.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/departments/batchload
HTTP/1.1 200
X-Frame-Options: SAMEORIGIN
X-Content-Type-Options: nosniff
X-XSS-Protection: 1; mode=block
Cache-Control: max-age=0
Expires: Wed, 12 Apr 2023 14:58:58 GMT
Content-Type: text/plain
Transfer-Encoding: chunked
Date: Wed, 12 Apr 2023 14:58:58 GMT

#INFO Number of rows processed: 3
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 3
SUCCESS: Processed without errors
$

select * from dept where deptno > 40;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        70 PROJECTS       LONDON
        80 MAINTENANCE    LONDON
        60 DEVELOPER      LONDON

SQL>


select * from emp where deptno > 40;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9996 JONES      MANAGER                              3500                    70
      9995 DAVIS      MAMAGER                              2500                    80
      9997 SMITH      MANAGER                              3000                    60

SQL>
10

Display Enabled Objects

The view displays enabled objects.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;
11

Thoughts

My biggest issue with AutoREST in the past was it was table/view centric. Very few real world units of work map directly to one table or view. As a result I often spurned AutoREST in favour of manually coding APIs in PL/SQL, and presenting them as REST web services. With JSON-relational duality views we can easily map real world objects to multiple database tables. The integration between ORDS and JSON-relational duality views make AutoREST a lot more appealing. For more information see: - Automatic Enabling of Schema Objects for REST Access (AutoREST) - Oracle REST Data Services (ORDS) : AutoREST - JSON-Relational Duality Views in Oracle Database 23ai - Oracle REST Data Services (ORDS) : AutoREST - Oracle REST Data Services (ORDS) : All Articles Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!