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
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;
/
{
"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
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;
/
{
"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"
}]
}