Weblog for the AMIS Technology corner
Posts tagged sql
Jouw toekomst als Oracle professional – AMIS !!EXTRA!! informatieavond over jouw ontwikkelingspad
Jan 24th
Als je dit leest en je bent Oracle professional – ontwikkelaar of administrator – is de kans groot dat je jouw carriere bent gestart in de jaren ‘90, in het tijdperk van Oracle7, Oracle Forms en Client/Server applicaties – net voor de opkomst van Java, internet, mobiel en SOA. Misschien ligt je start daar nog wel voor en misschien ben je van een tikje later. Maar in elk geval: je hebt een solide fundament en al vele jaren ervaring in Oracle technologie.
Nu is het 2012. En de afgelopen jaren is de technologie in hoog tempo ontwikkeld. Web 2.0 applicaties en multi-tier architecturen dienden zich aan en SOA, Service Bus en BPM zijn opgekomen. Java is overal – en HTML 5 staat op het punt om Flash en Silverlight te verdringen, in desktop browsers en in mobiele devices. Eindgebruikers worden mondiger en veel-eisender en real-time, push-enabled, BI-gedreven dashboards en multi-channel takenlijsten worden in snel itererende agile-projecten gerealiseerd.
Oracle 11g Database doet alle simpele DBA taken zelf – en de uitdaging voor de Database Administrator is verschoven naar groter en interessanter, zeker als 24/7 beschikbaarheid, virtualisatie en de cloud als infrastructuur opduiken. Middleware administratie en integrale security zijn zomaar twee nieuwe uitdagingen op de weg van de administrator, naast een verdergaande consolidatie en professionalisering van deployment en monitoring.
De uitdaging voor de Oracle professional lijkt duidelijk: hoe kies je je weg naar de toekomst? Om interessante klussen te blijven (gaan?) doen is het nodig om aansluiting te vinden bij de ontwikkelingen in de markt. Eerst moet je natuurlijk goed inzicht hebben in wat die ontwikkelingen zijn – in de markt in het algemeen en binnen de Oracle technology stack in het bijzonder. Dan moet je uitvinden hoe je vanuit je huidige kennis en ervaring de aansluiting vindt en jezelf prepareert voor het werken met de huidige en komende technologie. En tenslotte moet je het gaan doen – kennis vergaren, ervaring opdoen en je profileren om de passende klussen op te kunnen pakken.
Factorial in Oracle SQL – using both new Recursive Subquery and classic Connect By approach
Dec 13th
I regularly teach a masterclass on Oracle SQL. One of the topics I explore in depth is the use of the CONNECT BY query style to perform not just hierarchical queries but also networking or even generic recursive SQL. In Oracle Database 11g, the recursive subquery was formally introduced, the SQL Standard’s approach to this style of querying. The Recursive Subquery even stronger suggest recursive operations to be performed of course, but classic connect by can do that job as well.
One archetypical example of a recursive operation is the calculation of a factorial: n! = 1* 2 * 3 *…. * (n-1) * n.
In this short post I will show both the new, straightforward 11g based solution as well as the classic approach with CONNECT BY – that may not looks as recursive, but still very much is.
Weird ADF 11g requirement addressed with left outer join and modern SQL join syntax
Dec 13th
The functional requirement was a little unusual. The page should either show all master-records or – depending on the value of a parameter – it should show exactly one master-record joined with exactly one detail-record. The use case was valid – that was exactly the functionality that was required.
In terms of EMP and DEPT -I like to always simplify things to express them in EMP and DEPT terms – , we should either see all Departments (and no Employee data) or we should see the data for a specific Employee joined with the data for its corresponding Department. And of course we just one to create a single page, and make it as simple as possible to create that page.
A simplistic page that supports this functionality could like this:
when a specific Employee is requested and
when all Departments should be shown, because no single Employee is asked for.
ADF: The best way to indicate for records in a table the existence of details – or: the importance of up-to-date SQL knowledge
Oct 27th
An interesting discussion arose in one of the projects I am involved in. The functional requirement at stake: show records in a table. In one of the columns, show an indicator (checkbox for example) that signals whether or not that particular record has a specific type of details associated with it. You could say that it was the equivalent of a table of Departments with a checkmark for each Department that has employees (or employees of a certain type).
There are several ways of doing this – and several tiers in the application at which it can be done. One fairly straightforward way in ADF BC would be to create a ViewObject for the detail records, link them through a ViewLink to the master-VO and create a Transient Attribute in the master VO that uses a Groovy expression to count the number details.
However, when discussing the best approach, we came across other options and then took the discussion to a more generic level. One of my favorite guidelines, useful in situations like this, is to not bring data to where it is not really needed. In this, on the middle tier we do not really need the detail records. All we need is an indication of the existence of the detail records, which is not the same at all. When you consider the approach in an extreme situation – say records can have 1 milion details – it quickly becomes clear that there is a distinction between deriving the indication of detail-existence on the database or in the middle tier.
An easy approach that makes it unneccessary for the ADF BC framework to query the database for detail-records and make a server round trip for every row presented in the web page in the table is the following:
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)
OOW 2011 – What’s New, Improved and Coming in Oracle Application Development
Oct 3rd
Currently at Tom Kyte’s session regarding topics new, improved or coming in Oracle Application Development. Tom told about the history APEX has gone thru and the current setup with the APEX Listener and even the “PL/SQL Gateway” was mentioned. I always have to laugh a bit because this last one touches the XDB Protocol Server which can do way more then only this PL/SQL extension for APEX which has been embedded in this XDB Protocol Server framework. There is a APEX book out there that touches a bit the possibilities of the framework, one of the reasons I will promote and explain it a bit more during conferences like UKOUG, because I think its a shame that people don’t know its full potential. Anyway. Tom stressed once again that APEX is a serious environment regarding the huge websites out there based on APEX supporting 1000th of user sessions. Also it nowadays had a better debug support (4.1) and use / support for ROWID, improved data upload and calendar wizard support and redesigned websheets in APEX 4.1.
Data Integriteit anno 2011 – Hands-on met Toon Koppelaars en RuleGen 3.0 bij AMIS op dinsdag 27 september
Sep 22nd
datum: dinsdag 27 september; 16.30 uur
locatie: AMIS, Edisonbaan 15, Nieuwegein
sprekers: Toon Koppelaars en Lucas Jellema
Het belang van data integriteit behoeft geen toelichting – als het goed is. Vrijwel iedere enterprise applicatie is gebouwd op het fundament van een database. Robuustheid van die database en absolute betrouwbaarheid van de gegevens daarin zijn. essentieel. Het garanderen van die integriteit en robuustheid is lang niet altijd goed geregeld – en is ook geen triviale uitdaging.
De veelheid van toegangskanalen tot de database – batch jobs, web services, web applicaties in Java en .NET, PL/SQL modules en natuurlijk TOAD voor de applicatie beheerder – maakt wel duidelijk dat de integriteitsregels voor de data ofwel de business rules niet in de applicaties of in een service bus (alleen) kunnen worden geïmplementeerd. Integriteit in moderne applicatie-landschappen kan alleen worden gegarandeerd met implementatie van de business rules in de database zelf.
De Oracle database biedt naast de integrity constraints voor primary, unique en foreign keys weliswaar een veelheid aan mechanismen en constructies maar geen concrete, eenduidige, robuuste en efficiënte manier om business rules te implementeren. RuleGen is een framework dat optimaal gebruik maakt van wat Oracle 10g en 11g aan mechanismes biedt om te komen tot consistente en waterdichte afdwinging van de business rules. De uitvoering van de rules is efficiënt. Op run time kunnen rules gemonitord worden, in ‘log only mode’ draaien en zowel op statement- als transactie-level worden afgedwongen. De analyse en ontwikkeling van business rules wordt door RuleGen met een to-the-point user interface ondersteund die ontwikkelaars laat concentreren op de essentie ipv op het loodgieterswerk.
“RuleGen zou je kunnen beschouwen als de natuurlijke opvolger van CDM RuleFrame dat eind vorige eeuw door Oracle Nederland werd gelanceerd – een verbeterde uitvoering die de database slimmer benut” – aldus Lucas Jellema, CTO van AMIS en ooit de grondlegger van CDM RuleFrame.
In deze workshop wordt RuleGen geïntroduceerd. Read the rest of this entry »
Update day to second interval column in selection from time gaps table by parsing strings containing time differences using REGEXP_SUBSTR
Jul 25th
An apparently trivial challenge that still took me bit longer than expected. What is the situation: I have a table with two columns (of interest). One is a column called gap, with values like these:
Each value represents a time difference. These values are strings – not much use to calculate with. Therefore a second column in the table – called timegap – is defined, of type INTERVAL DAY TO SECOND. The challenge I am facing is to set the value of this column timegap based on the time difference described by the string in column gap.
With a little bit of SQL agility, using for example some regular expressions and an interesting update on [the result of] an inline view, it turns out to be doable in a fairly simple SQL statement.
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.
Using SQL UNPIVOT to prepare data for dynamic ADF Faces User Interfaces
Jul 3rd
The UNPIVOT operator was introduced in Oracle Database 11g – see for example: http://technology.amis.nl/blog/2421/the-oracle-11g-unpivot-operator-turning-columns-into-rows. In Oracle, the UNPIVOT operation is the process of turning Columns to Rows. Put simply, by applying the UNPIVOT operator to a number of columns, every row is split into that same number of rows. Each of these rows has two new columns: one for the column that this row stems from – one of the columns the data set was UNPIVOTed by – and one with the value from the column. The original UNPIVOT-columns are no longer part of the newly created records.
We can use the UNPIVOT operator in ADF BC ViewObjects to derive multiple rows from individual records. Subsequently, these rows can be filtered by the property they represent. This sounds vague – I know. I will use this approach to achieve the following functionality: a page that displays a bar chart with values per department. Depending on the setting of a radio group, the chart will display the number of employees, the maximum salary, the average experience of the average salary per department. The chart is based on a single ViewObject that contains a single query that uses UNPIVOT.g
Click on AvgSal and the graph is instantly refreshed to display:
