Parsing json using webservice url

firstly you should install json parsing package. you will find it at
http://sourceforge.net/projects/pljson/
or
https://github.com/pljson/pljson


declare
l_http_request   UTL_HTTP.req;
l_http_response  UTL_HTTP.resp;
l_response_text  CLOB;
acd VARCHAR2(32767);
buf VARCHAR2(32767);
l_list json_list;
empno           number;
ename         VARCHAR2(100);
job        VARCHAR2(100);
mgr         number;
hiredate         VARCHAR2(100);
sal         number;
comm         varchar2(200);
deptno     number;
obj json;

--get json from url

begin
l_http_request := UTL_HTTP.begin_request('http://127.0.0.1:9000/ords/anuj/anuj/emp/', 'GET'
              , 'HTTP/1.1'
              );

l_http_response := UTL_HTTP.get_response(l_http_request);

Begin
 LOOP
    UTL_HTTP.read_text(l_http_response, buf);
    l_response_text := l_response_text || buf;
  END LOOP;
  EXCEPTION
  WHEN UTL_HTTP.end_of_body THEN
    NULL;
 
 End;

 --extract to start point

obj := json(l_response_text );
acd := json_ext.get_json_value(obj,'items').to_char;
l_list := json_list(acd);

--parsing json

FOR i IN 1..l_list.count
LOOP


empno     := json_ext.get_number(json(l_list.get(i)),'empno');
ename     := json_ext.get_string(json(l_list.get(i)),'ename');
job       := json_ext.get_string(json(l_list.get(i)),'job');
mgr       := json_ext.get_number(json(l_list.get(i)),'mgr');
hiredate  := json_ext.get_string(json(l_list.get(i)),'hiredate');
sal       := json_ext.get_number(json(l_list.get(i)),'sal');
comm      := json_ext.get_number(json(l_list.get(i)),'comm');
deptno    := json_ext.get_number(json(l_list.get(i)),'deptno');
if comm is null then
comm := 'NULL';
end if;

dbms_output.put_line(empno||', '||ename||', '||job||', '||mgr||', '||hiredate||', '||sal||', '||comm||', '||deptno);

end loop;

 
  UTL_HTTP.end_response(l_http_response);

EXCEPTION
  WHEN UTL_HTTP.end_of_body
    THEN UTL_HTTP.end_response(l_http_response);

end;