Consuming ORDS Rest APIs using PL/SQL

February 29, 2020Posted inAPEX, ORDS

Consuming ORDS Rest APIs using PL/SQL

API End point   : https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp

Parameters      : deptno, empno

Method          :  GET

Without parameters

Copy Code
Declare
  l_clob    CLOB;
BEGIN
  l_clob := APEX_WEB_SERVICE.make_rest_request(
    p_url         => 'https://apex.oracle.com/pls/apex/ashish_portfolio/hr/employees/',
    p_http_method => 'GET'
  );
  -- Display the whole document returned.
  DBMS_OUTPUT.put_line('l_clob=' || l_clob);
 END;
/

Copy Code
{
 "items": [{
  "empno": 7839,
  "ename": "KING",
  "job": "PRESIDENT",
  "mgr": null,
  "hiredate": "1981-11-17T00:00:00Z",
  "sal": 5000,
  "comm": null,
  "deptno": 10
 }, {
  "empno": 7698,
  "ename": "BLAKE",
  "job": "MANAGER",
  "mgr": 7839,
  "hiredate": "1981-05-01T00:00:00Z",
  "sal": 2850,
  "comm": null,
  "deptno": 30
 }, {
  "empno": 7782,
  "ename": "CLARK",
  "job": "MANAGER",
  "mgr": 7839,
  "hiredate": "1981-06-09T00:00:00Z",
  "sal": 2450,
  "comm": null,
  "deptno": 10
 }, {
  "empno": 7566,
  "ename": "JONES",
  "job": "MANAGER",
  "mgr": 7839,
  "hiredate": "1981-04-02T00:00:00Z",
  "sal": 2975,
  "comm": null,
  "deptno": 20
 }, {
  "empno": 7788,
  "ename": "SCOTT",
  "job": "ANALYST",
  "mgr": 7566,
  "hiredate": "1982-12-09T00:00:00Z",
  "sal": 3000,
  "comm": null,
  "deptno": 20
 }, {
  "empno": 7902,
  "ename": "FORD",
  "job": "ANALYST",
  "mgr": 7566,
  "hiredate": "1981-12-03T00:00:00Z",
  "sal": 3000,
  "comm": null,
  "deptno": 20
 }, {
  "empno": 7369,
  "ename": "SMITH",
  "job": "CLERK",
  "mgr": 7902,
  "hiredate": "1980-12-17T00:00:00Z",
  "sal": 800,
  "comm": null,
  "deptno": 20
 }, {
  "empno": 7499,
  "ename": "ALLEN",
  "job": "SALESMAN",
  "mgr": 7698,
  "hiredate": "1981-02-20T00:00:00Z",
  "sal": 1600,
  "comm": 300,
  "deptno": 30
 }, {
  "empno": 7521,
  "ename": "WARD",
  "job": "SALESMAN",
  "mgr": 7698,
  "hiredate": "1981-02-22T00:00:00Z",
  "sal": 1250,
  "comm": 500,
  "deptno": 30
 }, {
  "empno": 7654,
  "ename": "MARTIN",
  "job": "SALESMAN",
  "mgr": 7698,
  "hiredate": "1981-09-28T00:00:00Z",
  "sal": 1250,
  "comm": 1400,
  "deptno": 30
 }, {
  "empno": 7844,
  "ename": "TURNER",
  "job": "SALESMAN",
  "mgr": 7698,
  "hiredate": "1981-09-08T00:00:00Z",
  "sal": 1500,
  "comm": 0,
  "deptno": 30
 }, {
  "empno": 7876,
  "ename": "ADAMS",
  "job": "CLERK",
  "mgr": 7788,
  "hiredate": "1983-01-12T00:00:00Z",
  "sal": 1100,
  "comm": null,
  "deptno": 20
 }, {
  "empno": 7900,
  "ename": "JAMES",
  "job": "CLERK",
  "mgr": 7698,
  "hiredate": "1981-12-03T00:00:00Z",
  "sal": 950,
  "comm": null,
  "deptno": 30
 }, {
  "empno": 7934,
  "ename": "MILLER",
  "job": "CLERK",
  "mgr": 7782,
  "hiredate": "1982-01-23T00:00:00Z",
  "sal": 1300,
  "comm": null,
  "deptno": 10
 }],
 "hasMore": false,
 "limit": 25,
 "offset": 0,
 "count": 14,
 "links": [{
  "rel": "self",
  "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp"
 }, {
  "rel": "describedby",
  "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/metadata-catalog/hr/item"
 }, {
  "rel": "first",
  "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp"
 }]
}


With Parameters

Copy Code
Declare
  l_clob    CLOB;
  l_result  VARCHAR2(32767);
BEGIN
  -- Get the XML response from the web service.
  l_clob := APEX_WEB_SERVICE.make_rest_request(
    p_url         => 'https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp',
    p_http_method => 'GET',
    p_parm_name   => APEX_UTIL.string_to_table('empno:deptno'),
    p_parm_value  => APEX_UTIL.string_to_table(7788 || ':' || 20)
  );
  -- Display the whole document returned.
  DBMS_OUTPUT.put_line('l_clob=' || l_clob);
   End;
 /

Output
Copy Code
{
"items": [{
 "empno": 7788,
 "ename": "SCOTT",
 "job": "ANALYST",
 "mgr": 7566,
 "hiredate": "1982-12-09T00:00:00Z",
 "sal": 3000,
 "comm": null,
 "deptno": 20
}],
"hasMore": false,
"limit": 25,
"offset": 0,
"count": 1,
"links": [{
 "rel": "self",
 "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp?empno=7788&deptno=20"
}, {
 "rel": "describedby",
 "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/metadata-catalog/hr/item"
}, {
 "rel": "first",
 "href": "https://apex.oracle.com/pls/apex/ashish_portfolio/hr/test_emp?empno=7788&deptno=20"
}]
}

Tags: