Creating an XMLType based on Object Types

Sometimes it is necessary to create a Stored Procedure which returns an XMLType, like when you want to expose the Stored Procedure to the "outside world", like via a Mediator. There are several options to create an XMLType. In this blogpost I will show you two ways of doing this. First the "regular" way using XMLElement, XMLForest and XMLAgg. Second using Object Types which are being converted to XMLType.

Using DBMS_UTILITY to aggregate strings

In my presentation "Analytic Functions Revisited" there is a section on how to string together some columns, string aggregation. Why? There is a new function in Oracle 11g Release 2, called LISTAGG which allows you to do this. This LISTAGG function also has an Analytic counterpart and that's the reason it is in the presentation.
At the UKOUG TEBS conference I did this presentation and someone approached me after wards to talk about these techniques. It boiled down to "Why do it the hard way when there is a built in package that allows you to do this for you?"
The built in package he was referring to is, note the title of this blog entry, DBMS_UTILITY. There are some downsides of using DBMS_UTILITY, more than I initially thought.

My First UKOUG - day two and three

Why can I never find the time to write my blogs?... Anyway, long overdue, here is my report from my first UKOUG conference. If you want to know what I did the first day, check out this link.
As I mentioned before the program is awesome, lots of very good speakers and very interesting subjects. It's quite hard to pick and choose the sessions. So what did I do the second and third day there?

My First UKOUG - day one

Last week the UKOUG-TEBS conference was in Birmingham. For whatever reason I had the idea that this conference was fairly small - no idea why I thought that.
The conference was held in the ICC in Birmingham, a wonderful location.

Change SYSDATE for testing

This morning I had some free time, so I was playing around with a little APEX 4 plugin. Probably the most simple plugin that you can imagine, but that is not what this post is about, or at least not mainly.
The plugin shows the current date (or I should say: sysdate) as a region on an APEX page.

Edition Based Redefinition and USER_OBJECTS_AE

Last week the Oracle PL/SQL Programming (OPP) and APEXposed conference took place in Brussels, Belgium.

My session was on Edition Based Redefinition, the killer feature of the Oracle 11g Release 2 database. One of my demo's showed what a procedure looks like when you override it in a newer edition from the datadictionary standpoint.
In earlier releases of the Oracle database it was not possible to have two procedures (or any other object as a matter of fact) with the same name in the same database schema.
With Edition Based Redefinition you can have two procedures with the same name, as long as they are in different Edtions.
For this demo I will use the new datadictionary view USER_OBJECTS_AE.

Inline View Check Option

Something I didn't know was possible, the "With Check Option" with an inline view. I knew this was possible with a "regular" view , but with an inline view...
The Check Option prohibits changes to the table (through the view) which would produce row that are not included in the view.
Let's start with a sample of how the Check Option works.