8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle REST Data Services (ORDS) : Including Hyperlinks in JSON Output
This article demonstrates how column aliases are used to instruct Oracle REST Data Services (ORDS) to display values as hyperlinks in the JSON output. This can help make services self-describing and improve navigation between services.
Thanks to Colm Divilly for giving me the heads-up on how this functionality works.
- Setup
- Default Links
- Single Column "self" Links
- Concatenated Key "self" Links
- Relative Links
- External Links
Related articles.
- Oracle REST Data Services (ORDS) : Including Hyperlinks in JSON Output
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Setup
Create the following test user.
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 TYPE TO testuser1;
The examples in this article use the following 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 REST web 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 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 => 'hr', p_auto_rest_auth => FALSE ); COMMIT; END; /
Web services from the schema can now be referenced using the following types of base URL.
http://<host>:<port>/ords/<schema-alias>/ http://<host>:<port>/ords/<optional-connection-mapping>/<schema-alias>/ # Example used here. http://localhost:8080/ords/hr/
Default Links
The following web service displays some information about employees. It's been limited to department 10 in this example to reduce the output.
CONN testuser1/testuser1@pdb1 BEGIN ORDS.define_service( p_module_name => 'testmodule1', p_base_path => 'testmodule1/', p_pattern => 'employees/', p_method => 'GET', p_source_type => ORDS.source_type_collection_feed, p_source => 'SELECT empno AS "employee_number", ename AS "employee_name", job FROM emp WHERE deptno = 10', p_items_per_page => 0); COMMIT; END; /
We use the curl
command to display the output from the web service, using json.tool
to pretty-print it.
Notice there are no URIs describing how to reference a single employee directly, only the default links to the service itself.
$ curl -s "http://localhost:8080/ords/hr/testmodule1/employees/" | python -mjson.tool { "count": 3, "hasMore": false, "items": [ { "employee_name": "CLARK", "employee_number": 7782, "job": "MANAGER" }, { "employee_name": "KING", "employee_number": 7839, "job": "PRESIDENT" }, { "employee_name": "MILLER", "employee_number": 7934, "job": "CLERK" } ], "limit": 0, "links": [ { "href": "http://localhost:8080/ords/hr/testmodule1/employees/", "rel": "self" }, { "href": "http://localhost:8080/ords/hr/metadata-catalog/testmodule1/employees/", "rel": "describedby" } ], "offset": 0 } $
Single Column "self" Links
We know the empno
column is the primary key, so we can create a direct URI to each employee by adding it to the select list with an alias beginning with a "$" symbol. This will generate a link by appending the empno
value to the service URI to create the href
, with the rel
value matching the alias name. The following example uses the "$self" alias, but "$.id" would also produce a self link.
BEGIN ORDS.define_service( p_module_name => 'testmodule2', p_base_path => 'testmodule2/', p_pattern => 'employees/', p_method => 'GET', p_source_type => ORDS.source_type_collection_feed, p_source => 'SELECT empno AS "employee_number", ename AS "employee_name", job, empno AS "$self" FROM emp WHERE deptno = 10', p_items_per_page => 0); COMMIT; END; /
If we call the service again we can see the "self" links have been added to each employee.
$ curl -s "http://localhost:8080/ords/hr/testmodule2/employees/" | python -mjson.tool { "count": 3, "hasMore": false, "items": [ { "employee_name": "CLARK", "employee_number": 7782, "job": "MANAGER", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule2/employees/7782", "rel": "self" } ] }, { "employee_name": "KING", "employee_number": 7839, "job": "PRESIDENT", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule2/employees/7839", "rel": "self" } ] }, { "employee_name": "MILLER", "employee_number": 7934, "job": "CLERK", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule2/employees/7934", "rel": "self" } ] } ], "limit": 0, "links": [ { "href": "http://localhost:8080/ords/hr/testmodule2/employees/", "rel": "self" }, { "href": "http://localhost:8080/ords/hr/metadata-catalog/testmodule2/employees/", "rel": "describedby" } ], "offset": 0 } $
We now have the "self" URI for each employee, but the service doesn't support the individual employee URIs yet, as shown below. The "-I" option limits the output to the headers, so we don't display all the HTML from the 404 error page.
$ curl -s -I "http://localhost:8080/ords/hr/testmodule2/employees/7934" HTTP/1.1 404 Content-Length: 0 Date: Sat, 15 Oct 2017 13:39:48 GMT $
We can add a template and handler to the service for the individual employee "self" URI.
BEGIN ORDS.define_template( p_module_name => 'testmodule2', p_pattern => 'employees/:empno'); ORDS.define_handler( p_module_name => 'testmodule2', p_pattern => 'employees/:empno', p_method => 'GET', p_source_type => ORDS.source_type_collection_feed, p_source => 'SELECT empno AS "employee_number", ename AS "employee_name", job FROM emp WHERE empno = :empno', p_items_per_page => 0); COMMIT; END; /
The service now supports the individual employee "self" URIs, as seen by re-running the previous call.
$ curl -s "http://localhost:8080/ords/hr/testmodule2/employees/7934" | python -mjson.tool { "count": 1, "hasMore": false, "items": [ { "employee_name": "MILLER", "employee_number": 7934, "job": "CLERK" } ], "limit": 0, "links": [ { "href": "http://localhost:8080/ords/hr/testmodule2/employees/7934", "rel": "self" }, { "href": "http://localhost:8080/ords/hr/metadata-catalog/testmodule2/employees/item", "rel": "describedby" } ], "offset": 0 } $
Concatenated Key "self" Links
When the "$.id" column alias is used for a single column it results in the same type of URI we saw when using the "$self" alias in the previous example. If multiple columns use the "$.id" column alias, their values are concatenated together into a comma-separated list, to indicate a concatenated key. In the following example we will pretend the empno
and ename
columns make up a concatenated key, so we will alias them both with "$.id".
BEGIN ORDS.define_service( p_module_name => 'testmodule3', p_base_path => 'testmodule3/', p_pattern => 'employees/', p_method => 'GET', p_source_type => ORDS.source_type_collection_feed, p_source => 'SELECT empno AS "employee_number", ename AS "employee_name", job, empno AS "$.id", ename AS "$.id" FROM emp WHERE deptno = 10', p_items_per_page => 0); COMMIT; END; /
We can see the employee-specific "self" URIs include the concatenated key.
$ curl -s "http://localhost:8080/ords/hr/testmodule3/employees/" | python -mjson.tool { "count": 3, "hasMore": false, "items": [ { "employee_name": "CLARK", "employee_number": 7782, "job": "MANAGER", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule3/employees/7782,CLARK", "rel": "self" } ] }, { "employee_name": "KING", "employee_number": 7839, "job": "PRESIDENT", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule3/employees/7839,KING", "rel": "self" } ] }, { "employee_name": "MILLER", "employee_number": 7934, "job": "CLERK", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule3/employees/7934,MILLER", "rel": "self" } ] } ], "limit": 0, "links": [ { "href": "http://localhost:8080/ords/hr/testmodule3/employees/", "rel": "self" }, { "href": "http://localhost:8080/ords/hr/metadata-catalog/testmodule3/employees/", "rel": "describedby" } ], "offset": 0 } $
Relative Links
Relative links can be used to link through to other services in the same module or different modules. ORDS will happily accept "../" and "./" starts to paths, but you can also use "^/" to build a path relative to the schema alias. The following service includes a direct URI for the current employee, and a URI for their department. The query is quoted using the q'[ ... ]'
syntax so we don't have to worry about internal single quotes.
BEGIN ORDS.define_service( p_module_name => 'testmodule4', p_base_path => 'testmodule4/', p_pattern => 'employees/', p_method => 'GET', p_source_type => ORDS.source_type_collection_feed, p_source => q'[SELECT empno AS "employee_number", ename AS "employee_name", job, empno AS "$self", '../departments/' || deptno AS "$department" FROM emp WHERE deptno = 10]', p_items_per_page => 0); COMMIT; END; /
We can now see the "department" link in addition to the "self" link for each employee.
$ curl -s "http://localhost:8080/ords/hr/testmodule4/employees/" | python -mjson.tool { "count": 3, "hasMore": false, "items": [ { "employee_name": "CLARK", "employee_number": 7782, "job": "MANAGER", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule4/employees/7782", "rel": "self" }, { "href": "http://localhost:8080/ords/hr/testmodule4/departments/10", "rel": "department" } ] }, { "employee_name": "KING", "employee_number": 7839, "job": "PRESIDENT", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule4/employees/7839", "rel": "self" }, { "href": "http://localhost:8080/ords/hr/testmodule4/departments/10", "rel": "department" } ] }, { "employee_name": "MILLER", "employee_number": 7934, "job": "CLERK", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule4/employees/7934", "rel": "self" }, { "href": "http://localhost:8080/ords/hr/testmodule4/departments/10", "rel": "department" } ] } ], "limit": 0, "links": [ { "href": "http://localhost:8080/ords/hr/testmodule4/employees/", "rel": "self" }, { "href": "http://localhost:8080/ords/hr/metadata-catalog/testmodule4/employees/", "rel": "describedby" } ], "offset": 0 } $
We will have to add the services to support the employee "self" URI, as we did previously, and the new "department" URI.
BEGIN -- Individual employee. ORDS.define_template( p_module_name => 'testmodule4', p_pattern => 'employees/:empno'); ORDS.define_handler( p_module_name => 'testmodule4', p_pattern => 'employees/:empno', p_method => 'GET', p_source_type => ORDS.source_type_collection_feed, p_source => 'SELECT empno AS "employee_number", ename AS "employee_name", job FROM emp WHERE empno = :empno', p_items_per_page => 0); -- Individual department. ORDS.define_template( p_module_name => 'testmodule4', p_pattern => 'departments/:deptno'); ORDS.define_handler( p_module_name => 'testmodule4', p_pattern => 'departments/:deptno', p_method => 'GET', p_source_type => ORDS.source_type_collection_feed, p_source => 'SELECT deptno AS "department_number", dname AS "department_name", deptno AS "$self" FROM dept WHERE deptno = :deptno', p_items_per_page => 0); COMMIT; END; /
The "department" link works as expected.
$ curl -s "http://localhost:8080/ords/hr/testmodule4/departments/10" | python -mjson.tool { "count": 1, "hasMore": false, "items": [ { "department_name": "ACCOUNTING", "department_number": 10, "links": [ { "href": "http://localhost:8080/ords/hr/testmodule4/departments/10", "rel": "self" } ] } ], "limit": 0, "links": [ { "href": "http://localhost:8080/ords/hr/testmodule4/departments/10", "rel": "self" }, { "href": "http://localhost:8080/ords/hr/metadata-catalog/testmodule4/departments/item", "rel": "describedby" } ], "offset": 0 } $
External Links
External links are added in a similar way to relative links, but they must include the whole link address. They can be hard-coded or built up using the contents of the table columns. The following service includes a direct URI for the current employee, and an external link. The query is quoted using the q'[ ... ]'
syntax so we don't have to worry about internal single quotes.
BEGIN ORDS.define_service( p_module_name => 'testmodule5', p_base_path => 'testmodule5/', p_pattern => 'employees/', p_method => 'GET', p_source_type => ORDS.source_type_collection_feed, p_source => q'[SELECT empno AS "employee_number", ename AS "employee_name", job, empno AS "$self", 'https://example.com?employee=' || empno AS "$external" FROM emp WHERE deptno = 10]', p_items_per_page => 0); COMMIT; END; /
We can see the external link in the output.
$ curl -s "http://localhost:8080/ords/hr/testmodule5/employees/" | python -mjson.tool { "count": 3, "hasMore": false, "items": [ { "employee_name": "CLARK", "employee_number": 7782, "job": "MANAGER", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule5/employees/7782", "rel": "self" }, { "href": "https://example.com?employee=7782", "rel": "external" } ] }, { "employee_name": "KING", "employee_number": 7839, "job": "PRESIDENT", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule5/employees/7839", "rel": "self" }, { "href": "https://example.com?employee=7839", "rel": "external" } ] }, { "employee_name": "MILLER", "employee_number": 7934, "job": "CLERK", "links": [ { "href": "http://localhost:8080/ords/hr/testmodule5/employees/7934", "rel": "self" }, { "href": "https://example.com?employee=7934", "rel": "external" } ] } ], "limit": 0, "links": [ { "href": "http://localhost:8080/ords/hr/testmodule5/employees/", "rel": "self" }, { "href": "http://localhost:8080/ords/hr/metadata-catalog/testmodule5/employees/", "rel": "describedby" } ], "offset": 0 } $
For more information see:
- Oracle REST Data Services (ORDS) : Including Hyperlinks in JSON Output
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...