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

Home » Articles » Misc » Here

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.

Related 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 => 'testuser1',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser1/

Single Column "self" 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.

$ curl -s "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/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://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule1/employees/",
            "rel": "self"
        },
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/metadata-catalog/testmodule1/employees/",
            "rel": "describedby"
        }
    ],
    "offset": 0
}
$

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.

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,
                                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://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule1/employees/" | python -mjson.tool
{
    "count": 3,
    "hasMore": false,
    "items": [
        {
            "employee_name": "CLARK",
            "employee_number": 7782,
            "job": "MANAGER",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule1/employees/7782",
                    "rel": "self"
                }
            ]
        },
        {
            "employee_name": "KING",
            "employee_number": 7839,
            "job": "PRESIDENT",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule1/employees/7839",
                    "rel": "self"
                }
            ]
        },
        {
            "employee_name": "MILLER",
            "employee_number": 7934,
            "job": "CLERK",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule1/employees/7934",
                    "rel": "self"
                }
            ]
        }
    ],
    "limit": 0,
    "links": [
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule1/employees/",
            "rel": "self"
        },
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/metadata-catalog/testmodule1/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://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule1/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    => 'testmodule1',
    p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'testmodule1',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule1/employees/7934" | python -mjson.tool
{
    "count": 1,
    "hasMore": false,
    "items": [
        {
            "employee_name": "MILLER",
            "employee_number": 7934,
            "job": "CLERK"
        }
    ],
    "limit": 0,
    "links": [
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule1/employees/7934",
            "rel": "self"
        },
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/metadata-catalog/testmodule1/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    => '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 "$.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://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule2/employees/" | python -mjson.tool
{
    "count": 3,
    "hasMore": false,
    "items": [
        {
            "employee_name": "CLARK",
            "employee_number": 7782,
            "job": "MANAGER",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule2/employees/7782,CLARK",
                    "rel": "self"
                }
            ]
        },
        {
            "employee_name": "KING",
            "employee_number": 7839,
            "job": "PRESIDENT",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule2/employees/7839,KING",
                    "rel": "self"
                }
            ]
        },
        {
            "employee_name": "MILLER",
            "employee_number": 7934,
            "job": "CLERK",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule2/employees/7934,MILLER",
                    "rel": "self"
                }
            ]
        }
    ],
    "limit": 0,
    "links": [
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule2/employees/",
            "rel": "self"
        },
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/metadata-catalog/testmodule2/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. 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    => 'testmodule3',
    p_base_path      => 'testmodule3/',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/employees/" | python -mjson.tool
{
    "count": 3,
    "hasMore": false,
    "items": [
        {
            "employee_name": "CLARK",
            "employee_number": 7782,
            "job": "MANAGER",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/employees/7782",
                    "rel": "self"
                },
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/departments/10",
                    "rel": "department"
                }
            ]
        },
        {
            "employee_name": "KING",
            "employee_number": 7839,
            "job": "PRESIDENT",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/employees/7839",
                    "rel": "self"
                },
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/departments/10",
                    "rel": "department"
                }
            ]
        },
        {
            "employee_name": "MILLER",
            "employee_number": 7934,
            "job": "CLERK",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/employees/7934",
                    "rel": "self"
                },
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/departments/10",
                    "rel": "department"
                }
            ]
        }
    ],
    "limit": 0,
    "links": [
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/employees/",
            "rel": "self"
        },
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/metadata-catalog/testmodule3/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    => 'testmodule3',
    p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'testmodule3',
    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    => 'testmodule3',
    p_pattern        => 'departments/:deptno');

  ORDS.define_handler(
    p_module_name    => 'testmodule3',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/departments/10" | python -mjson.tool
{
    "count": 1,
    "hasMore": false,
    "items": [
        {
            "department_name": "ACCOUNTING",
            "department_number": 10,
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/departments/10",
                    "rel": "self"
                }
            ]
        }
    ],
    "limit": 0,
    "links": [
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/departments/10",
            "rel": "self"
        },
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/metadata-catalog/testmodule3/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    => '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",
                                  '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://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule4/employees/" | python -mjson.tool
{
    "count": 3,
    "hasMore": false,
    "items": [
        {
            "employee_name": "CLARK",
            "employee_number": 7782,
            "job": "MANAGER",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule4/employees/7782",
                    "rel": "self"
                },
                {
                    "href": "https://example.com?employee=7782",
                    "rel": "external"
                }
            ]
        },
        {
            "employee_name": "KING",
            "employee_number": 7839,
            "job": "PRESIDENT",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule4/employees/7839",
                    "rel": "self"
                },
                {
                    "href": "https://example.com?employee=7839",
                    "rel": "external"
                }
            ]
        },
        {
            "employee_name": "MILLER",
            "employee_number": 7934,
            "job": "CLERK",
            "links": [
                {
                    "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule4/employees/7934",
                    "rel": "self"
                },
                {
                    "href": "https://example.com?employee=7934",
                    "rel": "external"
                }
            ]
        }
    ],
    "limit": 0,
    "links": [
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule4/employees/",
            "rel": "self"
        },
        {
            "href": "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/metadata-catalog/testmodule4/employees/",
            "rel": "describedby"
        }
    ],
    "offset": 0
}
$

For more information see:

Hope this helps. Regards Tim...

Back to the Top.