This article gives an overview of the AutoREST functionality of Oracle REST Data Services (ORDS) against JSON-relational duality views in Oracle 23c.
◉ Create a Test Database User
We need a new database user for our testing.
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;
Create and populate a copy of the EMP and DEPT tables.
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;
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.
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;
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.
◉ 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.
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;
/
Web services from the schema can now be referenced using the following base URL.
http://localhost:8080/ords/hr/
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.
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;
/
Notice the object is called DEPARTMENT_DV, but we want the web service to refer to it as "departments", hence the object alias. To disable AutoREST repeat the call with the P_ENABLED parameter set to FALSE.
We are now ready to start.
◉ 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.
Available Objects : http://localhost:8080/ords/hr/metadata-catalog/
Object Description: http://localhost:8080/ords/hr/metadata-catalog/departments/
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 DEPARTMENT_DV view. Remember, the object alias was set to "departments".
http://localhost:8080/ords/hr/departments/
The data from an individual row is returned using the primary key value. A comma-separated list is used for concatenated keys.
http://localhost:8080/ords/hr/departments/10
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 DEPARTMENT_DV view, starting at row 3.
http://localhost:8080/ords/hr/departments/?offset=2&limit=2
There are a variety of operators that can be used to filter the data returned from the object. Depending on you client, you may need to encode parts of the URI.
# 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
◉ 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.
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
}
]
}
If the payload is placed in a file called "/tmp/insert-payload.json", the following "curl" command will add a department via the DEPARTMENT_DV view.
$ 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/"}]}$
In addition to the web service output, we can see rows have been created in the base tables.
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>
◉ 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.
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
}
]
}
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 DEPARTMENT_DV view.
$ 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/"}]}$
In addition to the web service output, we can see the row has been updated by querying the table.
select * from emp where deptno = 40;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
9998 WOOD CLERK 500 40
SQL>
◉ DELETE Web Services (DELETE)
Records are deleted using the DELETE method. The URL and method necessary to do this are displayed below.
URL : http://localhost:8080/ords/hr/departments/50
Method : DELETE
The following "curl" command will delete a row from the EMP table. The URL is an encoded version of the one shown above.
$ 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}
$
In addition to the web service output, we can see the row has been deleted by querying the table.
select * from dept where deptno = 50;
no rows selected
SQL>
select * from emp where deptno = 50;
no rows selected
SQL>
◉ 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.
delete from emp where deptno = 50;
delete from dept where deptno = 50;
commit;
We create a new department using a REST call as we did previously.
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
}
]
}
If the payload is placed in a file called "/tmp/insert-payload.json", the following "curl" command will add a department via the DEPARTMENT_DV view.
$ 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/"}]}
$
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.
insert into emp values (9997,'WOOD','CLERK',null,null,1300,null,50);
commit;
Now we attempt to update the department, passing the original "etag" value in the "_metadata" tag.
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
}
]
}
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 DEPARTMENT_DV view.
$ 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"
}
$
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.
◉ 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.
delete from emp where deptno > 40;
delete from dept where deptno > 40;
commit;
The URL, method, header and payload necessary to do this are displayed below. Notice the payload is a JSON array of documents to load.
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
}
]
}
]
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.
$ 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
$
In addition to the web service output, we can see the rows have been loaded by querying the table.
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>
The parameters that can be used to influence the batch load are documented here.
◉ Display Enabled Objects
The USER_ORDS_ENABLED_OBJECTS view displays enabled objects.
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;
◉ Thoughts
Posts
