declare
--
  function get_dept_as_json( p_dept_id number )
  return clob
  is
    cursor c_dept
    is
      select d.department_id
           , d.department_name
           , l.location_id
           , l.street_address
           , l.postal_code
           , c.country_id
           , c.country_name
           , c.region_id
           , m.employee_id
           , m.first_name || ' ' || m.last_name manager_name
           , m.salary
           , j.job_id
           , j.job_title
           , j.min_salary
           , j.max_salary
      from departments d
         , locations l
         , countries c
         , employees m
         , jobs j
      where d.department_id = p_dept_id
      and   l.location_id = d.location_id
      and   c.country_id = l.country_id
      and   m.employee_id (+) = d.manager_id
      and   j.job_id (+) = m.job_id;
    r_dept c_dept%rowtype;
    l_jv as_json.tp_json_value;
    l_emps as_json.tp_json_value;
    l_hist as_json.tp_json_value;
    l_date_format varchar2(20) := 'DD-MON-YYYY';
    l_rv clob;
  begin
    open c_dept;
    fetch c_dept into r_dept;
    close c_dept;
--
    dbms_lob.createtemporary( l_rv, true, dbms_lob.call );
    l_jv := as_json.json( 'id', as_json.jv( r_dept.department_id )
              , 'name', as_json.jv( r_dept.department_name )
              , 'location', as_json.json( 'id', as_json.jv( r_dept.location_id )
                              , 'streetAddress', as_json.jv( r_dept.street_address )
                              , 'postalCode', as_json.jv( r_dept.postal_code )
                              , 'country', as_json.json( 'id', as_json.jv( r_dept.country_id )
                                             , 'name', as_json.jv( r_dept.country_name )
                                             , 'regionId', as_json.jv( r_dept.region_id )
                                             )
                              )
              , 'manager', as_json.json( 'id', as_json.jv( r_dept.employee_id )
                             , 'name', as_json.jv( r_dept.manager_name )
                             , 'salary', as_json.jv( r_dept.salary )
                             , 'job', as_json.json( 'id', as_json.jv( r_dept.job_id )
                                        , 'title', as_json.jv( r_dept.job_title )
                                        , 'minSalary', as_json.jv( r_dept.min_salary )
                                        , 'maxSalary', as_json.jv( r_dept.max_salary )
                                        )
                             )
              );
--
    for r_emp in ( select e.employee_id
                        , e.first_name || ' ' || e.last_name name
                        , e.hire_date
                        , e.commission_pct
                   from employees e
                   where e.department_id = r_dept.department_id
                 )
    loop
      l_hist := null;
      for r_hist in ( select h.job_id
                           , h.department_id
                           , h.start_date
                           , h.end_date
                      from job_history h
                      where h.employee_id = r_emp.employee_id
                    )
      loop
        l_hist := as_json.add_item( l_hist
                          , as_json.json( 'id', as_json.jv( r_hist.job_id )
                                , 'departmentId', as_json.jv( r_hist.department_id )
                                , 'startDate', as_json.jv( r_hist.start_date, l_date_format )
                                , 'endDate', as_json.jv( r_hist.end_date, l_date_format )
                                )
                          );
      end loop;
      l_emps := as_json.add_item( l_emps
                        , as_json.json( 'id', as_json.jv( r_emp.employee_id )
                              , 'name', as_json.jv( r_emp.name )
                              , 'isSenior', as_json.jv( r_emp.hire_date < to_date( '01-jan-2005', 'dd-mon-yyyy' ) )
                              , 'commissionPct', as_json.jv( r_emp.commission_pct )
                              , 'jobHistory', l_hist
                              )
                        );
    end loop;
    as_json.add_member( l_jv, 'employees', l_emps );
--
    l_rv := as_json.stringify( l_jv );
    as_json.free;
    return l_rv;
  end;
begin
  dbms_output.put_line( get_dept_as_json( 10 ) );
end;