Weblog for the AMIS Technology corner
Database
Masterclass Oracle 11g voor Database Ontwikkelaars – haal meer uit SQL en PL/SQL in 11g
Jan 23rd
Aanstaande vrijdag 27 januari verzorgt Oracle ACE Director Alex Nuijten bij AMIS Services in Nieuwegein een Masterclass voor Oracle Database Developers: iedereen die met enige regelmaat SQL en/of PL/SQL toepast in zijn of haar werk. In deze Masterclass leidt Alex de deelnemers rond in Oracle Database 11g Release 1 en 2 met als voornaamste doelstelling de bezoekers in staat te stellen nuttig gebruik te gaan maken van de nieuwe mogelijkheden in deze 11g releases ten einde code simpeler, beter, sneller en functioneel rijker te maken.
Alex staat wereldwijd bekend als enthousiaste verteller en duidelijke explicateur (hij heeft met die eigenschappen al meerdere speaker-awards gewonnen op internationale conferenties). Hij combineert een duidelijk overzicht van de theoretische achtergrond met sprekende demonstraties en praktijkvoorbeelden. Deelnemers kunnen idealiter zijn some wat droge gevoel voor humor waarderen – en zijn in staat om in een tamelijk intensieve dag veel nieuwe kennis te verwerken.
Voor deze Masterclass is nog een beperkt aantal plaatsen beschikbaar. Zie http://www.amis.nl/amis-masterclasses/masterclass-oracle-11g-voor-database-developers voor meer informatie en inschrijving.
Publishing to CometD Bayeux Channel from inside the Oracle Database – PL/SQL based push to CometD Web Client
Jan 19th
In recent articles, I have introduced CometD as framework for Server to Client and Client to Client Push: Running CometD 2 examples – locally on Tomcat using Maven and NetBeans http://technology.amis.nl/blog/14709/running-cometd-2-examples-locally-on-tomcat-using-maven-and-netbeans, CometD 2 Java Client Sample – open project in NetBeans based on Maven pom file, modify sources and run Java Based Comet Client (http://technology.amis.nl/blog/14720/cometd-2-java-client-sample-open-project-in-netbeans-based-on-maven-pom-file-modify-sources-and-run-java-based-comet-client) and Push based synchronized Slideshow demo application implemented using CometD and jQuery running on Tomcat (http://technology.amis.nl/blog/14870/push-based-synchronized-slideshow-demo-application-implemented-using-cometd-and-jquery-running-on-tomcat).
CometD allows web clients (or stand alone Java clients) to subscribe to Bayeux channels maintained by the CometD Server (Servlet). These clients can publish messages to the channels and CometD will deliver the messages to all or to selected clients – in a push-fashion. This makes it possible to push messages from the Java server side of a web application to web clients and indirectly to do the same between web clients. One example I have described of applying this technology is the synchronized slideshow: a web page is opened in two or more browsers. When a slide is selected in one of the browsers, the slide is selected in all browsers (because they all subscribed to the channel that a slide selection event was published to).
This article adds another push channel to the overall picture: it describes how the Oracle Database can publish directly to the Bayeux Channels (by posting to the CometD servlet over HTTP) and thereby perform push to the subscribed web clients. Database to browser push – how does that sound?
Using the Oracle XMLDB Repository to Automatically Shred Windows Office Documents (Part 1)
Dec 20th
People who have attended the UKOUG presentation this year where Mark Drake, Sr. Product Manager XML Technologies / XMLDB, Oracle HQ, and I demonstrated the first principles of the XDB Repository, might have been impressed with its (GEO/KML Spatial, Image EXIF info) capabilities combined with Google Earth. This post will zoom in on how to consume automatically content of Windows Office document (docx).
UKOUG 2011 – Drag, Drop and other Stuff. Using your Database as a File Server
View more presentations from Marco Gralike
Most (APEX) people know the PL/SQL Gateway functionality of the XDB Protocol Listener, but this is only one very small part of the XDB Repository functionality. To be precise only one “servlet” part of it. Those “servlets” can be based on Java, C or PL/SQL. The PL/SQL Gateway, as it’s name suggests, is based on the PL/SQL part. Another “servlet”, the Native Database Web Service (NDWS), which enables you to create a database SOA endpoint service and more, is based on C code. Beside demonstrating the WebDAV ACL driven security features and database extensibility/interfacing facilities based on the database (no cost option) XMLDB functionality, it also explain one of the coolest features, IMHO, introduced in Oracle 11gR1 called: XDB Repository Events.
There is one big problem in all of this. It is very, very sparsely documented and although there is some, or was some, code out there on the worldwide web, it took me a while to get a feeling of all the specifics at hand. This post will extend on some of the posts I already created to give to a head start of what is possible. This post will demonstrate what you can do with Windows Office documents, nowadays embedded (zipped) XML content, with extentions called: docx, xlsx or pptx…
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.
Absolutely Typical – The whole story on Types and how they power PL/SQL Interoperability (UKOUG, 2011)
Dec 12th
This presentation will hopefully convince database developers that types in the Oracle Database are worth their salt – and more. With the recent improvements in 11gR2, the pieces are available to complete the puzzle of structured and modern programming with a touch of OO and more importantly to create a decoupled, reusable API that exposes services based on tables and views to clients that speak SQL, AQ, PL/SQL, Types, XML or RESTful, through SQL*Net, JDBC or HTTP.
This session shows through many demonstrations how types and collections are defined, how they are used between SQL and PL/SQL and how they can be converted to and from XML and JSON and how they drive Native WebServices as well as RESTful services based on the Embedded PL/SQL Gateway. Everyone doing PL/SQL programming will benefit immediately from this session. Every Database Developer should be aware of Types and Collections. For structured programming, for optimal SQL to PL/SQL integration and for interoperability to client application. This session introduces Types and Collections, their OO capabilities, the conversion to XML and JSON, their use in Native and RESTful WebServices and the pivotal role they can play in encapsulation and decoupling.
The slides can be reviewed here:
View more presentations from Lucas Jellema.
Resources
Download Slides plus Demoscripts here: AbsolutelyTypical_UKOUG2011_jellema.zip.
Looking back at UKOUG 2011
Dec 11th
Now that the UKOUG annual conference in Birmingham is over, it’s time to write my thoughts down. As this was the second time that I attended the UKOUG conference, I already knew that it is a big conference. Lots of great speakers and a very good agenda. On the agenda were very interesting session, sometimes making it very hard to choose which session to go to. Guess you can’t complain about that.
On the Sunday before the actual conference starts, the OakTable organized a special day. Originally I didn’t plan to attending this day, as I was flying in in the afternoon. Turned out, I was on time to attend Connor McDonald’s session. Lucky me, Connor is an awesome presenter, so I decided to stay for the rest of the day. Very enjoyable, they even had OakTable beer before the final OakTable panel session.
In the evening there was the Ace dinner, in a nearby Thai restaurant. Good company, good food. A little nerdy perhaps, but as it turned out there were 42 Ace’s and Ace Directors attending.
On Monday I attended some really good sessions, like Kyle Hailey on SQL Tuning, Bryn Llewellyn on Using the PL/SQL Hierarchical Performance Profiler, and Connor McDonald on Partitioning 101.
Late in the evening Cary Millsap did a keynote session: Learning about Life through Business and Software. Only one word for this session: Wow! The things he talked about are still going around in my mind.
Tuesday morning, right after Roel Hartman’s session Done in 60 seconds, was my own session Who’s afraid of Analytic Functions? – for which I was quite nervous. Got some nice feedback on the session, so was quite pleased with it. Of course I had to go and see Carl Dudley’s session on Analysing Your Data with Analytic Functions – check out the competition, so to speak. Michael Salt’s session on Indexing: It’s All In The Index was also very interesting.
Wednesday was time to head back home, so packed up my bags and went to see Jonathan Lewis’ session on Redo. Can you keep an audience captivated for an hour talking about Redo? Jonathan Lewis can. Sadly this was my last session.
Because I arrived early at the Birmingham International Airport, I could get an earlier -originally delayed- flight back to The Netherlands. Now I can look back at a wonderful conference….
Oh,… did I mention I received the Inspiring Presentation Award for my session I did last year?
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.
APEX: Make a report row clickable
Nov 29th
When you create a “Report with Form”, there will be an icon in the report which allows you to navigate to the form page. Only when the user clicks the icon this navigation will take place. For the current project, this was not what they wanted. They wanted to click on the row instead of just the icon. This can be simply implemented using jQuery.
UKOUG 2011: Using your Database as a Fileserver
Nov 21st
UKOUG 2011 is nearby and one of the coolest things in Oracle 11g and onwards is, IMHO, a functionality called XDB Repository Events. Most of you probably know that based on XMLDB functionality in the database, the database also can be used in a File server kind of way by enabling the XDB Repository HTTP/FTP or WebDav functionality via DBMS_XDB. XDB Repository Events are a kind of “triggers” that enable you to automatically trigger/do something based on the events triggered in this file/folder environment. For example, it is possible to automatically create duplicate files in the XDB Repository or secure them. Other possibilities are to read the content of such a file and insert that content, on the fly during the copy/paste action, into a relational table.
Most APEX enthousiast know of the PL/SQL Gateway, which is a small part of the functionality that is called the XDB Protocol Listener. Besides PL/SQL support, it also enables you to secure your data, as mentioned, trigger actions based, for example on MIME type, mount your database as a Logical Volume (currently only via WebDAV, eg. DAVFS) of your operating system. The XDB Protocol Listener can support your own solutions based on PL/SQL (like REST WebServices), but also C or Java based methods, and, out-of-the-box, Native Database Web Services (SOAP support) or direct XML content access via other “servlets” like the “ORADB/DBUriServlet” servlet method.
