Weblog for the AMIS Technology corner
Databases
Implementing Web Services backed by a Database PL/SQL API using the Oracle Service Bus
Dec 1st
This article accompanies an article on the Architecture section of Oracle Technology Network (OTN): Implementing the Enterprise Service Bus Pattern to Expose Database Backed Services. It provides a detailed description of the implementation of the ESB architecture design pattern – the same that is introduced in the article on OTN – using Oracle Service Bus. This OSB based implementation is in terms of structure, architecture design and functionality the replica of the pure Java based implementation described in the OTN-article. OSB adds productivity, agility and many run time administrative benefits over the pure Java solution.
The challenge
The challenge the organization – let us call them Stuff Inc. – is facing is simple and common: external parties have requested access to information through Web Services. This information is held in the enterprise (Oracle) database at Stuff Inc.
The architecture team decides that the implementation of the service should be done according to the ESB architecture pattern.
Their initial implementation is based on custom Java Classes, XSLT stylesheets and JDBC calls.
After some time having worked with this initial set up, they decide to introduce the Oracle Service Bus to replace much of their generic classes and ESB plumbing. This article tells the story of how the OSB is used to implement a simple Web Service based on a PL/SQL API in an Oracle Database, using two transformations and a JCA Database Adapter service. It demonstrates the essential steps in implementing any OSB service.
2 dagen seminar door Steven Feuerstein: Best of Oracle PL/SQL (8 en 9 december)
Oct 13th
Steven Feuerstein
In dit tweedaagse seminar neemt Steven Feuerstein je mee ver voorbij de basismogelijkheden van PL/SQL. Steven zal tijdens dit seminar de best practices behandelen die hij op tientallen plekken in de wereld heeft verzameld en die hij ook mede door zijn nauwe samenwerking met het PL/SQL product team van Oracle kan verifiëren en aanscherpen. Hij laat via code-voorbeelden en interactieve sessies zien hoe je als ontwikkelaar in staat bent om snel, hoogwaardige, goed onderhoudbare en uitbreidbare applicaties kunt maken.
Er is tijdens dit seminar voldoende ruimte om vragen te stellen en ideeën met Steven Feurestein uit te wisselen.
Bij dit seminar zijn de volgende zaken inbegrepen:
- Een exemplaar van het boek Oracle PL/SQL Programming (5th edition) van Steven Feuerstein (twv €71,-).
- Een PL/SQL quiz onder leiding van Steven Feuerstein met leuke prijzen.
- Ook ontvang je een gratis jaarabonnement op het PL/SQL Channel. Hiermee heb je toegang tot 27 uur videotraining (normaal $395).
Dit is de kans om in Nederland in een select gezelschap met één van de experts op PL/SQL gebied kennis te maken en je expertise in Oracle PL/SQL verder te vergroten. Meer informatie of inschrijven kan via deze link.
(NB: Deelname voor deze twee dagen kost 1050 euro)
Masterclass Advanced Oracle SQL – vrijdag 14 oktober 2011
Oct 11th
Aanstaande vrijdag geef ik voor de derde keer de Masterclass Advanced Oracle SQL. Deze MC gaat over SQL – dat zal duidelijk zijn. We gaan de mogelijkheden van SQL in de Oracle Database verkennen. Van voor naar achter en tot in de diepte. Want SQL is veelzijdig – en veelzijdiger dan de meeste zelfs ervaren database ontwikkelaars en DBAs zich realiseren. Mijn doel met deze MC is om deelnemers te laten zien wat er met SQL mogelijk is en vooral waar SQL een krachtiger en eleganter alternatief is voor PL/SQL of andere applicatie code (Java, .Net, PHP etc.). Als het kan in SQL, kan het zelden beter op een andere manier.
Een paar van de onderwerpen die we bespreken:
-
inline views and query refactoring
-
scalar subquery
-
recursive querying (betere aanduiding van hierarchical queries)
-
rollup, pivot and unpivot
-
(new) outer join syntax and partition outer join
-
analytical functions (of course), primarily lead and lag
-
Flasback Versions (in addition to Flashback Query)
-
Regular Expressions
Daarnaast is er ook nog wel wat gelegenheid om een paar van de highlights en toekomstige ontwikkelingen rondom de database, SQL en NoSQL van de vorige week gehouden Oracle Open World conferentie te bespreken.
Er zijn nog enkele plaatsen beschikbaar voor deze Masterclass. Ben je een ervaren Oracle ontwikkelaar of DBA en wil je wel eens zien wat er dan nog meer allemaal kan met SQL dan je al weet? Meld je dan aan via http://www.amis.nl/amis-masterclasses/advanced-oracle-sql-masterclass
(NB: deelname kost 595 euro; er zijn verschillende kortingsregelingen)
Zie voor een korte overzicht van de vorige uitvoering van deze MC – en de bijbehorende sources – dit artikeltje: http://technology.amis.nl/blog/11847/masterclass-advanced-sql-entertaining-and-intensive.
Implementation of a moderately complex data constraint business rule using RuleGen 3.0
Aug 14th
In a previous article, I have introduced RuleGen 3.0 – a 2nd generation business rule implementation framework for the Oracle Database: http://technology.amis.nl/blog/?p=12807. RuleGen provides a solid, structured, scalable framework for database developers to implement data constraints in. This article demonstrates how a moderately complex rule is analyzed and designed and implemented with RuleGen 3.0. If you want to try out RuleGen yourself, you can download the framework under trial conditions from http://www.rulegen.com/free-download .
This article illustrates the essential steps in the business rule design and build process:
-
describe the business in human readable format – preferably using names of entities and attributes or even better tables and columns
-
analyze the events that potentially violate the business rule and should therefore trigger evaluation of the rule
-
define the filter condition (a SQL expression) that identifies the records that should be checked for each triggering event (typically only a very small subset of the records in a table need to be explicitly validated upon an event)
-
define the rule validation logic in terms of SQL – write the SQL query that should not return any records in order for the rule to be valid
-
record the events, the filter conditions and the validation SQL in the RuleGen design time UI
-
configure additional characteristics of the rule – such as validation time (statement or transaction), enabled status and log-setting
-
generate the rule implementation code and deploy the rule to the run time environment
note that these steps only involve a minimum of programming: typically two or three relatively simple SQL queries. No PL/SQL programming is involved. No additional tools (besides the APEX UI of RuleGen or its APIs) are required.
The rule that I will implement in this article is specified in the context of a familiar data model – see the figure below:
The rule that needs to be enforced is: “Employees who are a SALESREP should not work for a department that is located in Amsterdam”. We will not be discussing the business meaning of this rule in this article – we take this rule as the outcome of careful analysis of our business. Our responsibility is to ensure that no data will enter our enterprise database that does not comply with this rule.
RuleGen 3.0 – the latest, leanest and most robust solution for complex data constraints in an Oracle Database
Jul 6th
No matter how complex the enterprise and application architectures become, no matter the number of tiers, services, devices and user interfaces – at the heart of most enterprises will be a relational database.
And no matter how hard we try to implement a fully service based architecture or a multi-purpose business tier (for example using EJBs) – we will have multiple routes to the database and the data in it. Data will be manipulated through web applications, web services, client/server applications, batch database jobs, application managers working directly against the database from the command line or TOAD-like tools. If for no other reason – that by itself is an overridding motivation for enforcing every data constraint at the lowest possible level – the one level that none of these channels can avoid: the database itself. In addition to the fact that only enforcement inside the database can provide real integrity (and what the hack would be semi-real integrity?), for most data rules (especially complex ones) implementation inside the database is the most performant, scalable, maintainable, managable, agile, elegant and easy to implement as well.
Note: I am not advocating that data rules should not be implemented in other tiers as well. For the purpose of user friendliness through quick feedback and preventing incorrect data from ever getting near the database, it is of course perfectly fine to specify required fields, fields with a certainly length or a domain with allowable values in user interfaces and/or describe and enforce data constraints in the POJO Domain Model and the XSDs for the canonical data model of the ESB. However: all of these are only supplemental. They do not provide robustness.
Implementing data rules inside the database is not trivial, despite all the clear advantages outlined above.
Tracking the moving history of averages and other aggregates – Flashback Aggregates in Oracle SQL
Jun 16th
You may have seen we write on Flashback functionality in the Oracle Database. It’s an area that I like – especially since the introduction of the Flashback Data Archive in Oracle 11g. As an application developer, I typically focus on Flashback Query (AS OF) and Flashback Versions query (VERSIONS BETWEEN …). The latter provides an overview of the entire history of records in a table (in so far the UNDO area or the Flashback Data Archive has the information available). The former allows us to query an entire table at a certain moment in the past.
This article looks at a combination of the two. Take the following situation – using table EMP once more as our sample set: once upon a time, all employees in Department 10 see their salary increased by 15%. The next month, all CLERKs are made happy with a $200 pay raise. Two months later, MANAGERs are cut back – salary decrease of 10%. And finally, in this brief history of time, everyone hired in 1981 is awarded 150 extra in their regular paycheck.
Using Flashback Versions query, we can find out for every employee what the various salary levels are that they have been at. And we could create a chart with the line of salary levels plotted against the time. However, that does not give us the evolution of the average salary (or any other old aggregate) over all employee records versus time. We cannot meaningfully calculate aggregate values against EMP VERSIONS – except for the minimum and maximum values that have occurred. Using Flashback Query (EMP AS OF TIMESTAMP) it is possible to calculate the average salary as it existed at one specific moment in time. That is helpful – but does not yet give us the values we need to track the evolution of the average salary over time.
I have not been able to find a SQL-only solution to this challenge. However, with a bit of PL/SQL – a Table Function – it is fairly easy to construct a simple query that returns all values that have existed throughout history for a certain aggregate – like the average salary. Here is how that goes.
Creating JSON document straight from SQL query – using LISTAGG and With Clause
Jun 14th
This very brief article demonstrates how a fairly complex, nested JSON document can be created using a single SQL query. The main features used in the SQL statement are the WITH clause to define the inline views per object (Department, Manager, Employee), Scalar Subquery to retrieve the result from an inline view as string into the overall JSON string and LISTAGG to collect multiple elements into a JSON list.
The article shows a very simple application of this JSON creation through a simple HTML page – served by a PL/SQL package. This page does not contain any data. Right after loading, the onLoad event triggers a listener that invokes – through AJAX – the PL/SQL package’s other procedure. This procedure returns the JSON object – courtesy of that fabulous SQL query – and uses it to write the company details on the fly into the page.
Producing simple Pie Chart straight out of the Oracle Database – leveraging dbms_epg, CANVAS, LISTAGG and some JavaScript
Jun 13th
Once upon a time, I had something of a reputation for creating Charts in SQL. Bar Charts, Pie Charts, Gantt Charts and several other unlikely chart types, all created with SQL. And all presented on the one stage SQL has: SQL*Plus. See for example such riveting articles like http://technology.amis.nl/blog/1890/creating-a-gantt-chart-in-sql, http://technology.amis.nl/blog/1937/gauge-charts-in-sql-or-how-to-produce-the-worlds-ugliest-speedometers and http://technology.amis.nl/blog/398/pie-charts-in-sql-how-pathetic-can-you-get. Now I am back, with another look at charts produced straight out of the Oracle Database – this time using a different stage to present the chart on. With the Embedded PL/SQL Gateway – introduced in release 10g – the Oracle Database has a different stage to present information on or a different user interface to interact through: the web browser.
This article demonstrates how we can present charts – in the web browser using the CANVAS tag – based directly on a PL/SQL package in the database. No middle tier is required in between, no additional libraries are frameworks need to be configured. It’s only between the package and your browser.
Granted, you have seen prettier charts in your life time. But this of course is only the beginning. What you see here is a screenshot of a browser showing only the rendition of HTML with some embedded JavaScript, all produced by a single PL/SQL package. Making things look pretty is not my specialty I’m afraid, so I will leave that as an exercise to the reader.
Note that the code has an interesting use of the LISTAGG aggregation operator introduced in Oracle 11gR1 Database – for creating JSON-esque data structures. I’m sure more could be achieved in this fashion.
Browser-based log-monitor for database applications (alternative to dbms_output,leveraging pipe and Embedded PL/SQL Gateway with a touch of AJAX)
Jun 10th
One of the topics I discussed in today’s Masterclass Advanced Database Development was inter-session communication – or how database sessions can exchange information. One of my favorite vehicles to accomplish that is the database pipe (leveraging the supplied package dbms_pipe) – a light weight, fast, in memory facility. Pipes can be used in several ways to exchange messages of type number, date, raw or varchar2 – up to 4000 bytes – between sessions.
A typical application of the pipe is to provide a real time logging facility that – unlike dbms_output.put_line – makes the logging from within a database session available while the PL/SQL code is running. So the requirement I will implement is: show in a browser window the logging messages that are written to the database pipe set up to collect the logging data so the progress of PL/SQL programs can be monitored in real-time. Also, have the browser monitor update/refresh automatically – without the user having to refresh all the time.
The idea would be something like the next figure:
Masterclass voor Oracle ontwikkelaars – Lucas Jellema – vrijdag 10 juni – AMIS, Nieuwegein – Advanced Database Application Development
May 24th
De Oracle Database is in zekere zin ook een Applicatie Server. Of in elk geval is de database een zeer krachtige infrastructuur die door allerlei soorten applicaties kan worden benut. Dat geldt zowel voor .Net, Java en SOA applicaties als -zo mogelijke nog meer – voor ADF, Forms, APEX en PL/SQL applicaties. In deze Masterclass gaan we aan de hand van een groot aantal praktijkvoorbeelden en demonstraties (waar deelnemers uiteraard alle code van krijgen) de voornaamste faciliteiten op het gebied van applicatie-ontwikkeling in de database verkennen.
Het gaat hierbij niet specifiek om SQL (hoewel dat natuurlijk aan bod komt) of om de nieuwste features in de 11g database (hoewel ook die natuurlijk niet onbesproken blijven). Voor deze onderwerpen biedt AMIS specifieke Masterclasses (zie http://www.amis.nl/amis-masterclasses/advanced-oracle-sql-masterclass en http://www.amis.nl/amis-masterclasses/masterclass-oracle-11g-voor-database-developers). We kijken in de breedte, voor verschillende thema’s en aspecten van applicaties, naar de functionaliteit die ons als database applicatie ontwikkelaars ter beschikking staat.
Thema’s die tijdens deze Masterclass ondermeer aan bod komen:
- Events en Server Push (in en vanuit de database)
- Parallel en Asynchroon
- Agile Development, Release Management & Availability
- Alternatieve ingangen en uitgangen (van en naar de database)
- Heden en verleden (audit en flashback)
- Business Logic & Data Constraints
- Security in database applicaties
- Xenogenetics – toepassen van best practices en design patterns uit andere (technologie-)werelden op PL/SQL applicaties
- Fancy PL/SQL Features
En een paar populaire tags: REST, EBR, SOA, Cloud (daar doen we niks mee, maar een populaire tag is het wel), XML, Types & Collections, Virtual Column, JSON, Jobs, Email, Push, AQ, Pipes, SQL, OO, Java in de Database.
Verwacht een flink aantal aansprekende demo’s om de besproken concepten ook in actie te zien. Featuring ondermeer: ‘Database als Chat-buddy‘, fine grained flashback, no-trigger-Journalling , ‘the Talking Database’, publiceren van RSS feeds en RESTful/JSON WebServices, query de database via email, ‘creating parallel universes in the database’, partial Foreign Keys, AOP in PL/SQL, global joke of the day, dependency injection in the database, server push, not-so-watertight data integrity.
Kijk voor meer informatie – en om je aan te melden – op http://www.amis.nl/amis-masterclasses/masterclass-advanced-plsql-based-database-application-development.
