-- simple DBMS_EPG
rem create and authorize dad as SCOTT
-- as SYS:
BEGIN
DBMS_EPG.create_dad
( dad_name => 'hello'
, path => '/hello/*'
);
END;
/
-- as SCOTT:
begin
DBMS_EPG.AUTHORIZE_DAD('hello');
end;
/
create or replace
package hrm_json
as
procedure company;
procedure hrmpage;
end;
create or replace
package body hrm_json
as
procedure company
as
l_data varchar2(4000);
begin
with manager as
( select '{ '
||' "name":"'||ename||'"'
||',"salary":'||sal
||',"hiredate":"'||to_char(hiredate, 'DD-MM-YYYY')||'"'
||'} ' json
, emp.*
from emp
)
, employee as
( select '{ '
||' "name":"'||ename||'"'
||',"job":"'||job||'"'
||',"salary":'||sal
||',"manager":'||case when mgr is null then '""' else (select json from manager mgr where mgr.empno = emp.mgr) end ||',"hiredate":"'||to_char(hiredate, 'DD-MM-YYYY')||'"'
||'} ' json
, emp.*
from emp
)
, department as
( select '{ '
||' "name":"'||dname||'"'
||',"identifier":"'||deptno||'"'
||',"location":"'||loc||'"'
||',"employees":'||( select '['||listagg( json, ',')
within group (order by 1)
||']' as data
from employee emp
where emp.deptno = dept.deptno
)
||'} ' json
from dept
)
select '{"company" : ['
||( select listagg( json, ',')
within group (order by 1)
from department
)
||']}'
into l_data
from dual
;
htp.p(l_data);
end company;
procedure write_javascript
is
begin
htp.p('');
end;
procedure hrmpage
as
begin
htp.p('HRM page');
write_javascript;
htp.p('');
htp.p('HRM details for company
');
htp.p('');
htp.p('');
end;
end hrm_json;
-- url on local XE:
http://127.0.0.1:8080/hello/hrm_json.company
-- other ports can be configured with
EXECUTE dbms_xdb.SETHTTPPORT(2100);