Part of my Oracle Service Bus Tutorial: Building a dbAdapter on a Master-Detail table or view. JDeveloper offers two ways of implementation:
- Build a dbAdapter based on Views with JOIN within the dbAdapter.
- Build a dbAdapter based on a direct Query
- Build a dbAdapter based on a stored procedure / function or oracle package – the function returns a nested object type (master-detail).
Joining views or tables within the dbAdapter is not very elegant and therefore not part of my Oracle Service Bus tutorial. I’d like to describe the way of using object types for master-detail relationships.
Oracle Service Bus Tutorial: “departments–<employees” master-detail
Most of you know Oracle’s sample tables, I’m using departments and employees. To a department, many employees may exists. An employees belongs to one department. Easy. A simple master-detail relationship.
So, in Oracle Service Bus we like to use this master-detail data. But as mentioned above, we’d like to do it very elegant with clean and simple pl/sql code. No Joins within the dbAdapter. I’d like to have the complete database logic in the database itself. Not on the middleware.
So I use a nested object type. This oracle service bus tutorial now shows the specific implementation.
Oracle Service Bus Tutorial: PL/SQL code and Object Type definitions
Creation of the object types and the pl/sql program code.
-----------------------------------------------------------------------
-- Oracle Service Bus Tutorial
-- Drops
-----------------------------------------------------------------------
DROP TYPE OSBTutorial_department_tab_t force
/
DROP TYPE OSBTutorial_department_obj_t force
/
DROP TYPE OSBTutorial_employee_tab_t force
/
DROP TYPE OSBTutorial_employee_obj_t force
/
-----------------------------------------------------------------------
-- Oracle Service Bus Tutorial
-- Business Objects
-----------------------------------------------------------------------
create or replace type OSBTutorial_employee_obj_t as object
(
positionnr number(12)
,betrag number(12,2)
,wahrungcode varchar2(3)
,wahrung varchar2(60)
,positiontext varchar2(2000)
)
/
create or replace type OSBTutorial_employee_tab_t as table of OSBTutorial_employee_obj_t
/
create or replace type OSBTutorial_department_obj_t as object
(
department_id number(12)
,departmentnr number(12)
,departmentdatum date
,departmenttext varchar2(2000)
,departmentsumme number(12,2)
,falligam date
,bezahltam date
,mahnstufe number(2)
,positionen OSBTutorial_employee_tab_t
)
/
create or replace type OSBTutorial_department_tab_t as table of OSBTutorial_department_obj_t
/
-----------------------------------------------------------------------
-- Oracle Service Bus Tutorial
-- Package, will be called by dbAdapter
-----------------------------------------------------------------------
CREATE OR REPLACE package OSBTutorial_department_pa
is
function getdepartmentenObjecte( pdepartment_id in number, pStatus in varchar2 default 'OFFEN')
return OSBTutorial_department_tab_t;
end OSBTutorial_department_pa;
/
CREATE OR REPLACE package body OSBTutorial_department_pa
is
-------------------------------------------------------------------
-- User Defined Exceptions
-------------------------------------------------------------------
e_not_exists_dept exception;
pragma exception_init (e_not_exists_dept, -20999);
-------------------------------------------------------------------
-- Funciton getdepartmentenObjecte
-------------------------------------------------------------------
function getdepartmentenObjecte( pdepartment_id in number, pStatus in varchar2 default 'OFFEN')
return OSBTutorial_department_tab_t
is
cursor curdepartment(indepartment_id in number)
is
select
department_id
,departmentnr
,departmentdatum
,departmenttext
,falligam
,bezahltam
,mahnstufe
,positionnr
,betrag
,wahrungcode
,wahrung
,positiontext
,sum(betrag)
over (partition by department_id, departmentnr
order by department_id, departmentnr) as departmentsumme
,lead(departmentnr)
over (order by department_id, departmentnr) as next_departmentnr
from OSBTutorial_departmenten_vie
where 1=1
and department_id = indepartment_id
order by department_id, departmentnr, positionnr
;
rdepartment curdepartment%rowtype;
vdepartmentTabListe OSBTutorial_department_tab_t;
vdepartmentObjRecord OSBTutorial_department_obj_t;
vemployeeTabListe OSBTutorial_employee_tab_t;
vemployeeObjRecord OSBTutorial_employee_obj_t;
vdepartmentExists boolean;
cProg constant varchar2(130) := 'OSBTutorial_department_pa.getdepartmentenObjecte';
vStep varchar2(160) := 'init';
begin
-- check if departmenten exists
open curdepartment(pdepartment_id);
fetch curdepartment into rdepartment;
vdepartmentExists := curdepartment%found;
close curdepartment;
-- Object Types construct
if vdepartmentExists
then
-- New departmentslist create
vdepartmentTabListe := OSBTutorial_department_tab_t();
for rdepartment in curdepartment(pdepartment_id)
loop
-- jede Position der aktuellen department wird in die Liste erstellt.
-- solange bis wieder die Position 1 erscheint, dann gibts eine
-- neue Liste
if (rdepartment.positionnr = 1)
then
-- new employees list create
vemployeeTabListe := OSBTutorial_employee_tab_t();
end if;
-- instance, add to list
vemployeeObjRecord := OSBTutorial_employee_obj_t(
rdepartment.positionnr
, rdepartment.betrag
, rdepartment.wahrungcode
, rdepartment.wahrung
, rdepartment.positiontext
);
vemployeeTabListe.extend;
vemployeeTabListe(vemployeeTabListe.last) := vemployeeObjRecord;
-- on last employee, the department object will be created
if ( rdepartment.next_departmentnr != rdepartment.departmentnr
or rdepartment.next_departmentnr is null)
then
-- Neue department instanzieren und zur Liste add
vdepartmentObjRecord := OSBTutorial_department_obj_t(
rdepartment.department_id
,rdepartment.departmentnr
,rdepartment.departmentdatum
,rdepartment.departmenttext
,rdepartment.departmentsumme
,rdepartment.falligam
,rdepartment.bezahltam
,rdepartment.mahnstufe
,vemployeeTabListe -- all belonging employees to the department
);
vdepartmentTabListe.extend;
vdepartmentTabListe(vdepartmentTabListe.last) := vdepartmentObjRecord;
end if;
end loop;
else
--raise e_not_exists_dept;
null;
end if; -- exists (cursordaten gefunden)
return vdepartmentTabListe;
end getdepartmentenObjecte;
--
end OSBTutorial_department_pa;
/
can be easily tested with TOAD:
-----------------------------------------------------------------------
-- Oracle Service Bus Tutorial
-- Sample within SQL itself (using TOAD, SQL*PLUS, ...)
-----------------------------------------------------------------------
select OSBTutorial_department_pa.getdepartmentenObjecte(37797) as result
from dual
;