Consuming ORDS Rest APIs using PL/SQL

1API End point   : https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp
2
3Parameters      : deptno, empno
4
5Method          :  GET
6

 1Declare
 2  l_clob    CLOB;
 3BEGIN
 4  l_clob := APEX_WEB_SERVICE.make_rest_request(
 5    p_url         => 'https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employees/',
 6    p_http_method => 'GET'
 7  );
 8  -- Display the whole document returned.
 9  DBMS_OUTPUT.put_line('l_clob=' || l_clob);
10 END;
11/

  1{
  2 "items": [{
  3  "empno": 7839,
  4  "ename": "KING",
  5  "job": "PRESIDENT",
  6  "mgr": null,
  7  "hiredate": "1981-11-17T00:00:00Z",
  8  "sal": 5000,
  9  "comm": null,
 10  "deptno": 10
 11 }, {
 12  "empno": 7698,
 13  "ename": "BLAKE",
 14  "job": "MANAGER",
 15  "mgr": 7839,
 16  "hiredate": "1981-05-01T00:00:00Z",
 17  "sal": 2850,
 18  "comm": null,
 19  "deptno": 30
 20 }, {
 21  "empno": 7782,
 22  "ename": "CLARK",
 23  "job": "MANAGER",
 24  "mgr": 7839,
 25  "hiredate": "1981-06-09T00:00:00Z",
 26  "sal": 2450,
 27  "comm": null,
 28  "deptno": 10
 29 }, {
 30  "empno": 7566,
 31  "ename": "JONES",
 32  "job": "MANAGER",
 33  "mgr": 7839,
 34  "hiredate": "1981-04-02T00:00:00Z",
 35  "sal": 2975,
 36  "comm": null,
 37  "deptno": 20
 38 }, {
 39  "empno": 7788,
 40  "ename": "SCOTT",
 41  "job": "ANALYST",
 42  "mgr": 7566,
 43  "hiredate": "1982-12-09T00:00:00Z",
 44  "sal": 3000,
 45  "comm": null,
 46  "deptno": 20
 47 }, {
 48  "empno": 7902,
 49  "ename": "FORD",
 50  "job": "ANALYST",
 51  "mgr": 7566,
 52  "hiredate": "1981-12-03T00:00:00Z",
 53  "sal": 3000,
 54  "comm": null,
 55  "deptno": 20
 56 }, {
 57  "empno": 7369,
 58  "ename": "SMITH",
 59  "job": "CLERK",
 60  "mgr": 7902,
 61  "hiredate": "1980-12-17T00:00:00Z",
 62  "sal": 800,
 63  "comm": null,
 64  "deptno": 20
 65 }, {
 66  "empno": 7499,
 67  "ename": "ALLEN",
 68  "job": "SALESMAN",
 69  "mgr": 7698,
 70  "hiredate": "1981-02-20T00:00:00Z",
 71  "sal": 1600,
 72  "comm": 300,
 73  "deptno": 30
 74 }, {
 75  "empno": 7521,
 76  "ename": "WARD",
 77  "job": "SALESMAN",
 78  "mgr": 7698,
 79  "hiredate": "1981-02-22T00:00:00Z",
 80  "sal": 1250,
 81  "comm": 500,
 82  "deptno": 30
 83 }, {
 84  "empno": 7654,
 85  "ename": "MARTIN",
 86  "job": "SALESMAN",
 87  "mgr": 7698,
 88  "hiredate": "1981-09-28T00:00:00Z",
 89  "sal": 1250,
 90  "comm": 1400,
 91  "deptno": 30
 92 }, {
 93  "empno": 7844,
 94  "ename": "TURNER",
 95  "job": "SALESMAN",
 96  "mgr": 7698,
 97  "hiredate": "1981-09-08T00:00:00Z",
 98  "sal": 1500,
 99  "comm": 0,
100  "deptno": 30
101 }, {
102  "empno": 7876,
103  "ename": "ADAMS",
104  "job": "CLERK",
105  "mgr": 7788,
106  "hiredate": "1983-01-12T00:00:00Z",
107  "sal": 1100,
108  "comm": null,
109  "deptno": 20
110 }, {
111  "empno": 7900,
112  "ename": "JAMES",
113  "job": "CLERK",
114  "mgr": 7698,
115  "hiredate": "1981-12-03T00:00:00Z",
116  "sal": 950,
117  "comm": null,
118  "deptno": 30
119 }, {
120  "empno": 7934,
121  "ename": "MILLER",
122  "job": "CLERK",
123  "mgr": 7782,
124  "hiredate": "1982-01-23T00:00:00Z",
125  "sal": 1300,
126  "comm": null,
127  "deptno": 10
128 }],
129 "hasMore": false,
130 "limit": 25,
131 "offset": 0,
132 "count": 14,
133 "links": [{
134  "rel": "self",
135  "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp"
136 }, {
137  "rel": "describedby",
138  "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/metadata-catalog/hr/item"
139 }, {
140  "rel": "first",
141  "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp"
142 }]
143}

 1Declare
 2  l_clob    CLOB;
 3  l_result  VARCHAR2(32767);
 4BEGIN
 5  -- Get the XML response from the web service.
 6  l_clob := APEX_WEB_SERVICE.make_rest_request(
 7    p_url         => 'https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp',
 8    p_http_method => 'GET',
 9    p_parm_name   => APEX_UTIL.string_to_table('empno:deptno'),
10    p_parm_value  => APEX_UTIL.string_to_table(7788 || ':' || 20)
11  );
12  -- Display the whole document returned.
13  DBMS_OUTPUT.put_line('l_clob=' || l_clob);
14   End;
15 /

 1{
 2"items": [{
 3 "empno": 7788,
 4 "ename": "SCOTT",
 5 "job": "ANALYST",
 6 "mgr": 7566,
 7 "hiredate": "1982-12-09T00:00:00Z",
 8 "sal": 3000,
 9 "comm": null,
10 "deptno": 20
11}],
12"hasMore": false,
13"limit": 25,
14"offset": 0,
15"count": 1,
16"links": [{
17 "rel": "self",
18 "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp?empno=7788&deptno=20"
19}, {
20 "rel": "describedby",
21 "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/metadata-catalog/hr/item"
22}, {
23 "rel": "first",
24 "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp?empno=7788&deptno=20"
25}]
26}