March 30: OGh APEX dag

Today was OGh's APEX day. I saw 5 good presentations, in which I was not bored by Powerpoint once. I probably missed a few other good ones, since in the afternoon we all had to make some hard decisions about which presentation to visit and which ones to skip.

The first presentation was by Kitty Spaas and Mark Rooijakkers called "Centraal Boekhuis goes APEX". An informative and very entertaining story about why Centraal Boekhuis has chosen to migrate their J2EE applications to APEX ones. And about why they are still happy with that choice. This presentation was followed by a great presentation by Patrick Wolf from Oracle Austria, who told us about APEX 4.0. This new version will have lots of new features that will make developing APEX applications even easier than it already was. He demonstrated how dynamic actions and plugins work in a way that was easy to follow. So this was a good start of the day.

After the lunch everyone had to choose one out of three possible presentations. The first presentation I chose was the presentation by Jan Huyzentruyt and Olivier Dupont from iAdvise about Flightcare Belgium. They showed a very strong business case for using APEX: with only 6 people doing IT for a company of 1700 people, they managed to develop 51 small applications, with a total of 750 pages. And the screenshots of the applications looked good. The second choice of the afternoon was made in favour of Dimitri Gielis' presentation about APEX 4.0. He showed the new features that Patrick Wolf skipped. He did this in his usual enthusiastic and interactive style. Unfortunately the session ran quite a bit over time, which was unnecessary, but the content made up for it.

One of the best presentations this day was by my colleague Art Melssen about APEX templates. The APEX templates are now table-based, which doesn't leave much room for interface designers. The new version should contain a few CSS-based templates already. And according to Art, this is much better. Nice examples of how powerful this can be, can be found at csszengarden.com. He recommends to build your own template from scratch and to use CSS for the layout. The presentation inspired me to start doing that myself as well (in the second half of this year). His presentation was full of useful links with webexamples. So that should make a nice self-study for a sunday afternoon or two.

So I returned home with lots of new ideas and with the wish that APEX 4.0 will come out soon... Thanks to Natalie, OGh and the speakers for this great day.

Shredding XML into multiple tables in one shot

To insert the data from an XML document into multiple relational tables, you can use PL/SQL, loop statements and the extract function. But I wanted to know whether it is possible to do it using only SQL. With the XMLTable function you can transform an XML document to a relational format. This post will show how you can use the XMLTable function to store the data into relational tables with a master-detail-detail relationship, when the connecting columns are not part of the XML; they are surrogate key columns.

For this example, I'll use this XML:

SQL> select xml
  2    from xml_documents_to_process
  3  /

XML
----------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<msg:message xmlns:msg="namespace1">
  <msg:metadata>
    <msg:CreationTime>2010-03-16T15:23:14.56</msg:CreationTime>
  </msg:metadata>
  <msg:content xmlns:tcim="namespace2">
    <tcim:ReportingDate>2010-03-19</tcim:ReportingDate>
    <tcim:Persons>
      <tcim:Person>
        <tcim:Name>Jeffrey Lebowski</tcim:Name>
        <tcim:Items>
          <tcim:Item>
            <tcim:Name>Rug</tcim:Name>
            <tcim:Remarks>Really ties the room together</tcim:Remarks>
          </tcim:Item>
          <tcim:Item>
            <tcim:Name>Creedence Tapes</tcim:Name>
            <tcim:Remarks>Stolen</tcim:Remarks>
          </tcim:Item>
          <tcim:Item>
            <tcim:Name>Toe</tcim:Name>
            <tcim:Remarks>With nail polish</tcim:Remarks>
          </tcim:Item>
        </tcim:Items>
      </tcim:Person>
      <tcim:Person>
        <tcim:Name>Jesus Quintana</tcim:Name>
        <tcim:Items>
          <tcim:Item>
            <tcim:Name>Bowling ball</tcim:Name>
            <tcim:Remarks>Warmed up</tcim:Remarks>
          </tcim:Item>
        </tcim:Items>
      </tcim:Person>
      <tcim:Person>
        <tcim:Name>Walter Sobchak</tcim:Name>
      </tcim:Person>
    </tcim:Persons>
  </msg:content>
</msg:message>


1 row selected.

The contents have to be stored in these three tables:

SQL> create table messages
  2  ( guid           raw(16)      primary key
  3  , creation_time  timestamp(2) not null
  4  , reporting_date date         not null
  5  )
  6  /

Table created.

SQL> create table persons
  2  ( guid         raw(16)      primary key
  3  , message_guid raw(16)      not null references messages(guid)
  4  , name         varchar2(30) not null
  5  )
  6  /

Table created.

SQL> create table items
  2  ( guid        raw(16)      primary key
  3  , person_guid raw(16)      not null references persons(guid)
  4  , name        varchar2(20) not null
  5  , remarks     varchar2(40) not null
  6  )
  7  /

Table created.

Note that I use globally unique identifiers (guid's) here, because I could not make the example below work when using sequences. I challenge you to show me a reasonable way to do the same as I do below, using sequences :-). I really like to know. But for now, I know one more advantage of SYS_GUID() over sequences.

Back to the example. Let me first show how to shred this XML into these three tables in one shot. The explanation comes afterwards.

SQL> create procedure from_xml_to_relational (p_xml in xmltype)
  2  is
  3  begin
  4    insert all
  5      when add_messages_indicator = 'Y'
  6      then
  7           into messages values
  8           ( m_guid
  9           , creation_time
 10           , reporting_date
 11           )
 12      when add_persons_indicator = 'Y'
 13      then
 14           into persons values
 15           ( p_guid
 16           , m_guid
 17           , p_name
 18           )
 19      when add_items_indicator = 'Y'
 20      then
 21           into items values
 22           ( i_guid
 23           , p_guid
 24           , i_name
 25           , remarks
 26           )
 27    select case when p.id = 1 and nvl(i.id,1) = 1 then 'Y' end add_messages_indicator
 28         , case when nvl(i.id,1) = 1 then 'Y' end              add_persons_indicator
 29         , nvl2(i.id,'Y',null)                                 add_items_indicator
 30         , first_value(sys_guid()) over (partition by m.id order by p.id,i.id) m_guid
 31         , first_value(sys_guid()) over (partition by m.id,p.id order by i.id) p_guid
 32         , sys_guid()                                                          i_guid
 33         , to_timestamp(m.creation_time,'yyyy-mm-dd"T"hh24:mi:ss.ff') creation_time
 34         , to_date(m.reporting_date,'yyyy-mm-dd')                     reporting_date
 35         , p.name                                                     p_name
 36         , i.name                                                     i_name
 37         , i.remarks                                                  remarks
 38      from xmltable
 39           ( xmlnamespaces ('namespace1' as "msg", default 'namespace2')
 40           , 'msg:message'
 41             passing p_xml
 42             columns id                          for ordinality
 43                   , creation_time  varchar2(50) path 'msg:metadata/msg:CreationTime'
 44                   , reporting_date varchar2(50) path 'msg:content/ReportingDate'
 45                   , persons        xmltype      path 'msg:content/Persons'
 46           ) m
 47         , xmltable
 48           ( xmlnamespaces (default 'namespace2')
 49           , 'Persons/Person'
 50             passing m.persons
 51             columns id                 for ordinality
 52                   , name  varchar2(30) path 'Name'
 53                   , items xmltype      path 'Items'
 54           ) p
 55         , xmltable
 56           ( xmlnamespaces(default 'namespace2')
 57           , 'Items/Item'
 58             passing p.items
 59             columns id                   for ordinality
 60                   , name    varchar2(20) path 'Name'
 61                   , remarks varchar2(40) path 'Remarks'
 62           ) (+) i
 63    ;
 64  end;
 65  /

Procedure created.

The first challenge is to join the three levels in this XML with each other. This is done by passing a part of the XML to the lower level. For example, in line 45 the Persons-part of the Message XML is caught and passed on to subquery p in line 50. This is repeated from Persons to Items in line 53 and 58.

A second challenge is to do an outerjoin with XMLTable. This wasn't a challenge after all, because - as you can see - all you have to do is to add a (+) sign after the XMLTable function.

The resulting query produces 5 rows. The third challenge is to make the INSERT ALL statement do only 1 insert into the messages table, 3 inserts into persons and 4 inserts into items. To support this challenge, I introduced an "id for ordinality" numeric column. This id column helps to construct expressions add_messages_indicator, add_persons_indicator and add_items_indicator. The id's also help for constructing the right guid's for each row to link the related records to each other. To see how these expressions work, let's show the result of the select:

SQL> select case when p.id = 1 and nvl(i.id,1) = 1 then 'Y' end add_messages_indicator
  2       , case when nvl(i.id,1) = 1 then 'Y' end              add_persons_indicator
  3       , nvl2(i.id,'Y',null)                                 add_items_indicator
  4       , first_value(sys_guid()) over (partition by m.id order by p.id,i.id) m_guid
  5       , first_value(sys_guid()) over (partition by m.id,p.id order by i.id) p_guid
  6       , sys_guid()                                                          i_guid
  7       , to_timestamp(m.creation_time,'yyyy-mm-dd"T"hh24:mi:ss.ff') creation_time
  8       , to_date(m.reporting_date,'yyyy-mm-dd')                     reporting_date
  9       , p.name                                                     p_name
 10       , i.name                                                     i_name
 11       , i.remarks                                                  remarks
 12    from xml_documents_to_process x
 13       , xmltable
 14         ( xmlnamespaces ('namespace1' as "msg", default 'namespace2')
 15         , 'msg:message'
 16           passing x.xml
 17           columns id                          for ordinality
 18                 , creation_time  varchar2(50) path 'msg:metadata/msg:CreationTime'
 19                 , reporting_date varchar2(50) path 'msg:content/ReportingDate'
 20                 , persons        xmltype      path 'msg:content/Persons'
 21         ) m
 22       , xmltable
 23         ( xmlnamespaces (default 'namespace2')
 24         , 'Persons/Person'
 25           passing m.persons
 26           columns id                 for ordinality
 27                 , name  varchar2(30) path 'Name'
 28                 , items xmltype      path 'Items'
 29         ) p
 30       , xmltable
 31         ( xmlnamespaces(default 'namespace2')
 32         , 'Items/Item'
 33           passing p.items
 34           columns id                   for ordinality
 35                 , name    varchar2(20) path 'Name'
 36                 , remarks varchar2(40) path 'Remarks'
 37         ) (+) i
 38  /

ADD ADD ADD M_GUID                           P_GUID
--- --- --- -------------------------------- --------------------------------
I_GUID                           CREATION_TIME        REPORTING_DATE
-------------------------------- -------------------- -------------------
P_NAME
--------------------------------------------------------------------------------
I_NAME
------------------------------------------------------------
REMARKS
------------------------------
Y   Y   Y   82F643037E04E60FE0400000000001C0 82F643037E04E60FE0400000000001C0
82F643037E09E60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00
                                 0000000
Jeffrey Lebowski
Rug
Really ties the room together

        Y   82F643037E04E60FE0400000000001C0 82F643037E04E60FE0400000000001C0
82F643037E0AE60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00
                                 0000000
Jeffrey Lebowski
Creedence Tapes
Stolen

        Y   82F643037E04E60FE0400000000001C0 82F643037E04E60FE0400000000001C0
82F643037E0BE60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00
                                 0000000
Jeffrey Lebowski
Toe
With nail polish

    Y   Y   82F643037E04E60FE0400000000001C0 82F643037E07E60FE0400000000001C0
82F643037E0CE60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00
                                 0000000
Jesus Quintana
Bowling ball
Warmed up

    Y       82F643037E04E60FE0400000000001C0 82F643037E08E60FE0400000000001C0
82F643037E0DE60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00
                                 0000000
Walter Sobchak




5 rows selected.

The guid's all look alike, but don't be fooled (as I was initially): they differ where they need to differ. Just look at byte 6 (position 12). If you look closely, you'll see that the column m_guid all contain the same guid's. The first three p_guid's are the same, but row 4 and 5 differ. This is what the partition clause in the first_value analytic function achieves. The i_guid's all differ. With the guid's defined like this, the foreign key columns refer to the right parent records.

Finally, let's show that the INSERT ALL statement has done its job:

SQL> declare
  2    l_xml xmltype;
  3  begin
  4    select xml
  5      into l_xml
  6      from xml_documents_to_process
  7    ;
  8    from_xml_to_relational(l_xml);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select * from messages
  2  /

GUID                             CREATION_TIME        REPORTING_DATE
-------------------------------- -------------------- -------------------
82F643037DDCE60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00

1 row selected.

SQL> select * from persons
  2  /

GUID                             MESSAGE_GUID                     NAME
-------------------------------- -------------------------------- --------------------
82F643037DDCE60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Jeffrey Lebowski
82F643037DDFE60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Jesus Quintana
82F643037DE0E60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Walter Sobchak

3 rows selected.

SQL> select * from items
  2  /

GUID                             PERSON_GUID                      NAME                 REMARKS
-------------------------------- -------------------------------- -------------------- ------------------------------
82F643037DE1E60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Rug                  Really ties the room together
82F643037DE2E60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Creedence Tapes      Stolen
82F643037DE3E60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Toe                  With nail polish
82F643037DE4E60FE0400000000001C0 82F643037DDFE60FE0400000000001C0 Bowling ball         Warmed up

4 rows selected.

SQL> select m.creation_time
  2       , m.reporting_date
  3       , p.name
  4       , i.name
  5       , i.remarks
  6    from messages m
  7       , persons p
  8       , items i
  9   where m.guid = p.message_guid
 10     and p.guid = i.person_guid (+)
 11  /

CREATION_TIME        REPORTING_DATE      NAME                 NAME                 REMARKS
-------------------- ------------------- -------------------- -------------------- ------------------------------
16-03-10 15:23:14,56 19-03-2010 00:00:00 Jeffrey Lebowski     Rug                  Really ties the room together
16-03-10 15:23:14,56 19-03-2010 00:00:00 Jeffrey Lebowski     Creedence Tapes      Stolen
16-03-10 15:23:14,56 19-03-2010 00:00:00 Jeffrey Lebowski     Toe                  With nail polish
16-03-10 15:23:14,56 19-03-2010 00:00:00 Jesus Quintana       Bowling ball         Warmed up
16-03-10 15:23:14,56 19-03-2010 00:00:00 Walter Sobchak

5 rows selected.

Weird exception handling

PL/SQL got me fooled today. My assignment was to build a new procedure that gets invoked together with an existing procedure. After I had build and unit tested my new procedure, the tester wanted to conduct a system integration test. He had trouble coming up with a situation where the old and new procedure were called. So I helped him by having a look at some of the surrounding code and my conclusion was: the existing procedure and my new one will not be called. Ever. To support my claim, I had built a small script to simulate the situation. But I missed a tiny detail that seemed irrelevant at first, but which appeared to be crucial.

Here's my first simulation of the situation: two packages, one with a global public exception and a second with a local exception with the same name.

rwijk@ORA11GR1> create package pkg1
  2  as
  3    e_wrong_contents exception;
  4    procedure start1;
  5  end pkg1;
  6  /

Package created.

rwijk@ORA11GR1> create package pkg2
  2  as
  3    procedure start2;
  4  end pkg2;
  5  /

Package created.

rwijk@ORA11GR1> create package body pkg1
  2  as
  3    procedure start1
  4    is
  5    begin
  6      pkg2.start2;
  7    end start1;
  8  end pkg1;
  9  /

Package body created.

rwijk@ORA11GR1> create package body pkg2
  2  as
  3    procedure local_procedure
  4    is
  5      e_wrong_contents exception;
  6    begin
  7      raise e_wrong_contents;
  8    exception
  9    when e_wrong_contents then
 10      raise e_wrong_contents;
 11    when others then
 12      -- do something else
 13      null;
 14    end local_procedure
 15    ;
 16    procedure start2
 17    is
 18    begin
 19      local_procedure;
 20    exception
 21    when pkg1.e_wrong_contents then
 22      -- existing_procedure;
 23      -- my_new_procedure;
 24      dbms_output.put_line('pkg1.e_wrong_contents was raised');
 25    when others then
 26      dbms_output.put_line('others');
 27      dbms_output.put_line(sqlerrm || chr(10) || dbms_utility.format_error_backtrace);
 28    end start2
 29    ;
 30  end pkg2;
 31  /

Package body created.

My claim was that the existing procedure and my new one were never invoked, because pkg1.e_wrong_contents is never raised. Only the local one, but that one loses scope in procedure start2 and becomes a user defined exception there, as can be seen by running pkg1.start1:

rwijk@ORA11GR1> exec pkg1.start1
others
User-Defined Exception
ORA-06512: at "RWIJK.PKG2", line 10
ORA-06512: at "RWIJK.PKG2", line 16


PL/SQL procedure successfully completed.

The point is that pkg1.e_wrong_contents and e_wrong_contents may look related, but they are not.

After having witnessed that the existing procedure does get invoked in the production database, we had a second look at my script and a colleague pointed out that I missed one detail: a pragma exception_init to a user defined error number (-20660). I had left it out on purpose, since there is no point in assigning a user defined exception to user error number that is not used. I had checked there wasn't any RAISE_APPLICATION_ERROR(-20660,...) in the entire schema. With the pragma exception_init added, the script looks like this:

rwijk@ORA11GR1> create package pkg1
  2  as
  3    e_wrong_contents exception;
  4    pragma exception_init(e_wrong_contents,-20660)
  5    ;
  6    procedure start1;
  7  end pkg1;
  8  /

Package created.

rwijk@ORA11GR1> create package pkg2
  2  as
  3    procedure start2;
  4  end pkg2;
  5  /

Package created.

rwijk@ORA11GR1> create package body pkg1
  2  as
  3    procedure start1
  4    is
  5    begin
  6      pkg2.start2;
  7    end start1;
  8  end pkg1;
  9  /

Package body created.

rwijk@ORA11GR1> create package body pkg2
  2  as
  3    procedure local_procedure
  4    is
  5      e_wrong_contents exception;
  6      pragma exception_init(e_wrong_contents,-20660);
  7    begin
  8      raise e_wrong_contents;
  9    exception
 10    when e_wrong_contents then
 11      raise e_wrong_contents;
 12    when others then
 13      -- do something else
 14      null;
 15    end local_procedure
 16    ;
 17    procedure start2
 18    is
 19    begin
 20      local_procedure;
 21    exception
 22    when pkg1.e_wrong_contents then
 23      -- existing_procedure;
 24      -- my_new_procedure;
 25      dbms_output.put_line('pkg1.e_wrong_contents was raised');
 26    when others then
 27      dbms_output.put_line('others');
 28      dbms_output.put_line(sqlerrm || chr(10) || dbms_utility.format_error_backtrace);
 29    end start2
 30    ;
 31  end pkg2;
 32  /

Package body created.

And executing pkg1.start1:

rwijk@ORA11GR1> exec pkg1.start1
pkg1.e_wrong_contents was raised

PL/SQL procedure successfully completed.

Now the code is invoked and that's how it works in production. The pragma exception_init assigns both the global and the local one to ORA-20660, which makes them equal.

Why you would ever want to code it like this, I don't know. It's probably best categorized as "historically grown like that".

Defining your own exception, assigning it to a user error number and then raise your exception, is equivalent to just doing a raise_application_error:

rwijk@ORA11GR1> declare
  2    e_wrong_contents exception;
  3    pragma exception_init(e_wrong_contents,-20660);
  4  begin
  5    raise e_wrong_contents;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-20660:
ORA-06512: at line 5


rwijk@ORA11GR1> begin
  2    raise_application_error(-20660,null);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-20660:
ORA-06512: at line 2

And if I get to refactor the code, I would skip the pragma exception_init's and skip the local exceptions. The code would then look like this:

rwijk@ORA11GR1> create package pkg1
  2  as
  3    e_wrong_contents exception;
  4    procedure start1;
  5  end pkg1;
  6  /

Package created.

rwijk@ORA11GR1> create package pkg2
  2  as
  3    procedure start2;
  4  end pkg2;
  5  /

Package created.

rwijk@ORA11GR1> create package body pkg1
  2  as
  3    procedure start1
  4    is
  5    begin
  6      pkg2.start2;
  7    end start1;
  8  end pkg1;
  9  /

Package body created.

rwijk@ORA11GR1> create package body pkg2
  2  as
  3    procedure local_procedure
  4    is
  5    begin
  6      raise pkg1.e_wrong_contents;
  7    end local_procedure
  8    ;
  9    procedure start2
 10    is
 11    begin
 12      local_procedure;
 13    exception
 14    when pkg1.e_wrong_contents then
 15      -- existing_procedure;
 16      -- my_new_procedure;
 17      dbms_output.put_line('pkg1.e_wrong_contents was raised');
 18    when others then
 19      dbms_output.put_line('others');
 20      dbms_output.put_line(sqlerrm || chr(10) || dbms_utility.format_error_backtrace);
 21    end start2
 22    ;
 23  end pkg2;
 24  /

Package body created.

rwijk@ORA11GR1> exec pkg1.start1
pkg1.e_wrong_contents was raised

PL/SQL procedure successfully completed.

Simulating production situations using your own little hand written script is a valuable technique, but you have to be careful to include all the necessary details ...