DBA Hub

📋Steps in this guide1/6

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.

oracle miscconfigurationintermediate
by OracleDba
15 views
1

Setup

Create the following test user. The examples in this article use the following tables. 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. Web services from the schema can now be referenced using the following types of base URL.

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

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;

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://<host>:<port>/ords/<schema-alias>/
http://<host>:<port>/ords/<optional-connection-mapping>/<schema-alias>/

# Example used here.
http://localhost:8080/ords/hr/
2

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. We use the command to display the output from the web service, using 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.

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

$
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
}
$
3

Single Column "self" Links

We know the 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 value to the service URI to create the , with the value matching the alias name. The following example uses the "$self" alias, but "$.id" would also produce a self link. If we call the service again we can see the "self" links have been added to each employee. 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. We can add a template and handler to the service for the individual employee "self" URI. The service now supports the individual employee "self" URIs, as seen by re-running the previous call.

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

$
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
}
$

$
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

$

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

$
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
}
$
4

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 and columns make up a concatenated key, so we will alias them both with "$.id". We can see the employee-specific "self" URIs include the concatenated key.

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

$
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
}
$
5

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 syntax so we don't have to worry about internal single quotes. We can now see the "department" link in addition to the "self" link for each employee. We will have to add the services to support the employee "self" URI, as we did previously, and the new "department" URI. The "department" link works as expected.

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

$
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
}
$

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

$
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
}
$
6

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 syntax so we don't have to worry about internal single quotes. We can see the external link in the 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
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;
/

$
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
}
$

Comments (0)

Please to add comments

No comments yet. Be the first to comment!