SERPland Flugverkehr/Airtraffic Zürich – Auto Direktimport Dänemark – Disneyland Paris – Legoland Deutschland

15Jul/110

Oracle Service Bus Tutorial: basing dbAdapter on Nested Object Type (Master-Detail)

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.

oracle-service-bus-tutorial-dept-emp-datamodel-oracle

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 ;

Related Posts:

Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.