Tuesday, March 30, 2010

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.

Monday, March 29, 2010

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.

Monday, March 8, 2010

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 ...