retrieve return parameters from rest web service in plsql
declare l_resp_clob clob := ' { "next": { "$ref": "http://server_addr/apex/testing/emprest/getAllDepts?page=1" }, "items": [{ "deptno": 10, "dname": "ACCOUNTING", "loc": "NEW YORK" }, { "deptno": 20, "dname": "RESEARCH", "loc": "DALLAS" }, { "deptno": 30, "dname": "SALES", "loc": "CHICAGO" }, { "deptno": 40, "dname": "OPERATIONS", "loc": "BOSTON" } ]}'; l_values apex_json.t_values; l_js_deptno dept.deptno % type; l_js_dname dept.dname % type; l_js_loc dept.loc % type; l_item_count number;begin --parse the returned JSON apex_json.parse( p_values => l_values, p_source => l_resp_clob); l_item_count := apex_json.get_count(p_path => 'items', p_values => l_values); dbms_output.put_line('Item count: ' || l_item_count); dbms_output.put_line('-----------looping over all items------------------'); for i in 1 .. l_item_count loop dbms_output.put_line('Item no: ' || i); l_js_deptno := apex_json.get_number( p_values => l_values, p_path => 'items[' || i || '].deptno'); l_js_dname := apex_json.get_varchar2( p_values => l_values, p_path => 'items[' || i || '].dname'); l_js_loc := apex_json.get_varchar2( p_values => l_values, p_path => 'items[' || i || '].loc'); dbms_output.put_line('Dept no: ' || l_js_deptno || ', Dname: ' || l_js_dname || ', Loc: ' || l_js_loc); end loop;end;