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:

14Jul/112

D: Avaloq Script Sourcecode – Avaloq Parametrierer Beispiel

Avaloq Script? Im IT Bereich der Banken welche das Avaloq Softwarepaket verwenden, werden viele Wirtschaftsinformatiker oder Parametrierer gesucht – da werden Avaloq Script Kenntnisse gewünscht. Aber was ist Avaloq Script? Wie muss ich mir das vorstellen? Gibt es Sourcecode Beispiele?

Mir erging es genau so, nur konnte ich leider nirgends im Internet ein Beispiel finden.
Deshalb mein kleiner Bericht hierzu.

Aber was ist nun dieses Avaloq Script genau? In den Stelleninseraten oder dann auch beim Vorstellungsgespräch erfährt man dazu relativ wenig. Im Internet gibt’s dazu kaum Avaloq Script Codebeispiele. Der Grund liegt auf der Hand: Avaloq Script ist keine “öffentliche” Programmiersprache. Sie wird explizit nur auf dem Avaloq System verwendet. Das Avaloq System kann man sich nicht einfach so runterladen, man muss da schon eine Bank sein und viel Geld investieren um das Softwarepaket lizenziert kaufen zu können.

Allen Stellensuchenden Informatikern die sich nun relativ wenig unter Avaloq Script vorstellen können, möchte ich hier ein kleines Code Beispiel mitsamt Beschreibung zeigen. Dies ist ein Fantasiebeispiel und hat keinen Bezug zu einer reellen Implementierung einer spezifischen Bank.

Related Posts:

22May/100

D: Dokumentation TOAD for Oracle (Datenbank Werkzeug)

Quest Software bietet das Datenbank Werkzeug "TOAD" an. Vor allem bekannt- und grossgeworden im Oracle Datenbankbereich. Mittlerweile gibt's neben TOAD for Oracle auch TOAD for mySQL, TOAD for dies und jenes ...

Referenzen
[1] Oracle9i Database Concepts (10g Release 2 (10.2) und höhere Releases
[2] SQL*Plus User's Guide and Reference (Release 10.2) und höhere Releases

Beschreibung:
Bei Toad for Oracle handelt es sich um ein DB Tool (interaktiv und batch), welches erlaubt SQL, PL/SQL und SQL*Plus-Statements gegen eine Oracle Datenbank abzusetzen.
Toad Editionen
----------
Toad for Oracle ist in vier Editionen verfügbar:

Edition Description
Toad™ for Oracle
Provides the foundation and basic tools for Oracle professionals to create and execute queries, as well as build and manage database objects. You’ll benefit from the formatting feature, enhanced user interface and also have support for SQL Plus. This edition enables you to plug into Toad for Oracle's added utilities.

Toad™ for Oracle Professional
(includes all features of Toad™ for Oracle with integrated debugging, PL/SQL knowledge base and code profiling)
Developing PL/SQL code is a lengthy process, requiring a great deal of accuracy. Toad™ for Oracle Professional features an expert code review which effortlessly integrates into Toad for Oracle's Procedure Editor - analyzing stored procedures against a set of rules which means you’ll receive detailed, corrective recommendations.

Für Entwickler ist die Version "Toad™ for Oracle Professional" zu empfehlen

Toad™ for Oracle Xpert
(includes all features of Toad™ for Oracle Professional with integrated SQL Tuning and SQL & PL/SQL Optimization scanning via CodeXpert)
Are you looking for a way to make the chore of tuning SQL code easier and less time-consuming? Toad™ for Oracle Xpert can simplify this task by identifying potential performance limiting factors. We’ve also equipped you with the power of an enhanced SQL Tuning component that is sure to shorten tuning time for developers, DBAs and analysts.

Toad™ for Oracle Suite
The Toad™ for Oracle Suite equips you with all the features and functionality found in Toad™ for Oracle Xpert plus a built-in array of tools to radically improve the way Oracle professionals develop code and build and maintain database objects.
- DBA Module
- Knowledge Xpert™ for Oracle Administration
- Knowledge Xpert™ for PL/SQL
- DataFactory® Developer Edition
- Benchmark Factory® for Databases
- Toad Data Modeler

Für Datenbankadministration (DBA) ist die Version "Toad for Oracle Suite" zu empfehlen
Auf dieser Seite wird die Standardversion "Toad for Oracle" besprochen.

Login TOAD
----------
Für einen Login müssen die folgenden Felder eingegeben werden:
• User / Schema
• Password
• Database (TNS)

Anschliessend kann mit Connect eine Verbindung zur Datenbank hergestellt werden.
Alle bereits verwendeten Connections werden in der Liste links geführt und können dort auch ausgewählt werden.
Nicht mehr benötigte Connections können mit [Del] wieder aus der Liste gelöscht werden.

Related Posts: