-- 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 dashboard
as
procedure departments;
end;
create or replace
package body dashboard
as
procedure write_javascript
( p_data in varchar2
, p_labels in varchar2
) is
begin
htp.p('');
end;
procedure departments
as
l_data varchar2(4000);
l_labels varchar2(4000);
begin
htp.p('Departments Dashboard');
select '['||listagg( '"'||dname||'"', ',')
within group (order by sumsal desc)
||']' as data
, '['||listagg( nvl(sumsal,0), ',')
within group (order by sumsal desc)
||']' as labels
into l_labels, l_data
from ( select dname
, sum(sal) sumsal
from emp
right outer join dept
using (deptno)
group by dname
)
;
write_javascript(l_data, l_labels);
htp.p('');
htp.p('Salary distribution across departments
');
htp.p('');
htp.p('');
end departments;
end dashboard;
-- url on local XE:
http://127.0.0.1:8080/hello/dashboard/departments
-- other ports can be configured with
EXECUTE dbms_xdb.SETHTTPPORT(2100);