Should you expose a Stored Procedure via XMLType?

When you want to expose your Stored Procedure to the "outside world", should you do this using XMLType? As always, the correct answer is "it depends".
For this example I will use a Package containing two functions, the signature of the package is as follows:

create or replace package emps_pkg
is
   function get_department_xml (p_department_no in number)
      return xmltype;

   function get_departement_ot (p_department_no in number)
      return all_departments_ot;
  
end emps_pkg;

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.

Going to OOW2010 - Long Wednesday

Wednesday morning at 5 o'clock as the day begins, the alarm clock wakes me. Time to get out, take a shower and get ready to go to Oracle Open World. Not only my first Oracle Open World, but also the first time that I will be invited to the Oracle Ace Directors Briefing. Looking forward to an exciting week and a half in and around San Francisco.

Oracle OpenWorld, the countdown has begun

One week from now I am flying to San Francisco. Very excited to go as this will be my first Oracle OpenWorld.

Birthday Gift from Oracle: Ace Director

Yesterday was my birthday which usually starts off by being woken up by my children showering me with drawings they made. Yesterday was no exception.
I also got an email from Oracle, a nice one I might add.

Alex-
I'm happy to inform you that your Oracle ACE Director nomination submitted by ------- has been accepted. The next step is confirming your understanding of Oracle's expectation as well as confirming the support of your company/management to participate.

As requested by my nominator in this matter, the name has been left out. Thank you for nominating me - you know who you are. :)

APEX 4.0: Removing Plugins

Just a quick note so I won't forget, and maybe help you along the way.
A little while ago I created my first APEX 4.0 plugin, which was a nice learning experience. Removing a plugin is (or at least to me) not as obvious as you might think.
This blog entry is about removing an installed plugin.

APEX 4.0 Plugin: Watermark

This simple plugin for APEX 4.0 shows some text inside an item. Usually a watermark is used to give the user extra information to assist with filling out a Form. Creating this plugin was more a learning experience than anything else. If you just want the plugin than follow this link to download it. If you want to go through the same learning experience I went through while creating this plugin, than keep reading.

Connecting Without TNSNames With EZConnect

On my laptop I run two databases, Oracle 10g Release 2 and Oracle 11g Release 2. Next to my regular consulting work, I also give an Oracle Advanced SQL and PL/SQL training called "7Up". This training covers "all" new features since Oracle7.
I tried using VM for having multiple versions of the database, but my laptop would grind to a halt.
Anyway, usually I use SQL*Plus for all my demo's - SQL*Plus Windows that is. As you may know, SQL*Plus Windows vanished from the Oracle 11g database.
Until now I was using SQL*Plus commandline instead, and that was fine.
Last week I received the latest Oracle Magazine (July/August 2010) at home and in it was the AskTom column on connecting without TNSNames.

Looking back at ODTUG Kaleidoscope 2010

In case you missed it (how is that possible?) last week was the annual ODTUG conference, Kaleidoscope in Washington DC. In this post I will share some of, what I consider, the highlights of this event.
I always enjoy going to this conference, it's nice to see old friends and make some new ones.

Analytic Function bug and National Championship

Yesterday there were the "NL Masters" in my hometown Oosterhout, The Netherlands. This two day track and field event was the National Championship for Athletes in the Master Class.
While volunteering (I was running around all day as a courier) I noticed a familiar name on the list. Toine van Beckhoven, currently ranked first in the PL/SQL Challenge. Small world. Toine finished first on the 400m hurdles in his category and is now the official Dutch National Champion. Congratulations, Toine.
Being involved in this event triggered a question regarding ranking. And we're back to analytic functions... ;)

Analytic Function bug?

Last Tuesday we had an ODTUG Preview mini-conference at our office in Nieuwegein, The Netherlands. Nine presentations with nine fine speakers. Too bad the session were in parallel, and thus you had to pick and choose which session to attend. Oh well, that's always the case with conferences, even tiny ones like this one.
Because I'm doing a presentation during the ODTUG Kaleidoscope 2010, I also did my presentation last Tuesday.
The feedback I got was good, and I also got some pointers to make the presentation better.

Check your Datatype, also in Check Constraints

A question came up at the Oracle discussion forum on SQL and PL/SQL regarding not being able to see any data even though the table did contain records. My lesson from this question was to pay close attention to the datatypes and implicit conversions taking place. What still puzzles me is a way to detect the mistake made apart from looking closely.
Anyway these queries didn't return any records:

SELECT * FROM problem WHERE solved = '0';
SELECT * FROM problem WHERE solved = 0;

Oracle 11gR2: Editions and SQL Developer

I know the correct term is Edition Based Redefinition, but that would make the title of this blog post a bit too long.
Over the last weekend Oracle 11gR2 was released on the Windows platform, good news for me. On my laptop I run Windows so I could finally upgrade my demo database from Release 1 to Release 2. I had been doing demo's with a virtual machine, which was ok. Like the one I did last year during the Planboard Symposium
While I was preparing for that presentation, the support for Edition Based Redefinition was added to SQL Developer 2.1, at least that's what the New Features stated. But since March 1st there is SQL Developer 2.1.1 which does have support for Edition Based Redefinition.
This blogpost is not going to explain Edition Based Redefinition in detail, but will show you the support that SQL Developer has for Edition Based Redefinition.

Change the XML Prolog using XMLROOT

About a month ago I wrote about using XMLType and RefCursor to rename the ROW and ROWSET tags. You can find that article by clicking here.
For the same project described in that article the XML prolog needed to include the encoding as well. Just in case you are unfamiliar with the XML Prolog, this is it:

<?xml version="1.0"?>

In Oracle 10g there is an XMLROOT function which creates the XML Prolog for you, but it doesn't allow you to add encoding to it. At least not by specifying parameters, but there is a way to do this, read on..

Identifying the "last" record using the LEAD function

Of course there is no such thing as the "last" record in a relational database. Unless you have an ORDER BY in your query.
For the current project we needed to determine the last record in a set, because this record needs special treatment. One of my colleagues came up with a CASE expression combined with a LEAD function to determine this record.

XMLType, RefCursor and renaming ROW and ROWSET tags

The project that I'm currently involved in uses XML to send data to a reporting engine which parses the XML and creates a pretty PDF report.
In order to do this some "generic" packages were created to create the XML based on the content of a RefCursor. Works like a charm. Just call the function with a RefCursor and get an XML back.
The problem they initially had was that the XML returned uses the ROWSET and ROW tags, while they wanted different tags. No problem, some REPLACE function would do the job, and that worked fine. Until last week that was...

The Case for the Case Statement

Oracle 9i introduced the CASE Statement and CASE Expressions.
Some say that the CASE statement is a drop-in replacement of the IF statement, but there is a subtle difference between these two.
In this post I will explain the difference between the Case statement and the IF statement. Sometimes it's "better" to change the IF statement to a CASE statement.