Translating and more with Google API's

Google has a lot of API's that you can use in SQL and PL/SQL as well. A couple of months ago I saw a very nice example on OTN here. It was an example of how to use Google's Translate API. Here is how to do it yourself.

First of all, since version 11, you need to specify fine grained access to external network services, which is described here in the documentation. The package DBMS_NETWORK_ACL_ADMIN should be used to create an access control list saying that access to the googleapis is allowed:

rwijk@ORA11202> declare
  2    cn_acl constant varchar2(100) := 'googleapis.xml';
  3  begin
  4    dbms_network_acl_admin.create_acl
  5    ( acl         => cn_acl
  6    , description => 'Google Translate API'
  7    , principal   => 'RWIJK'
  8    , is_grant    => true
  9    , privilege   => 'connect'
 10    , start_date  => null
 11    , end_date    => null
 12    );
 13    dbms_network_acl_admin.add_privilege
 14    ( acl       => cn_acl
 15    , principal => 'RWIJK'
 16    , is_grant  => true
 17    , privilege => 'resolve'
 18    );
 19    dbms_network_acl_admin.assign_acl
 20    ( acl  => cn_acl
 21    , host => 'ajax.googleapis.com'
 22    );
 23    dbms_network_acl_admin.assign_acl
 24    ( acl  => cn_acl
 25    , host => 'maps.googleapis.com'
 26    );
 27  end;
 28  /

PL/SQL procedure successfully completed.

For the translation API, I assigned ajax.googleapis.com and for another example later on I also assigned maps.googleapis.com. Now I can use the API and create my own translate function:

rwijk@ORA11202> create function my_translate
  2  ( p_text in varchar2
  3  , p_source_language_code in varchar2
  4  , p_target_language_code in varchar2
  5  ) return varchar2
  6  is
  7  begin
  8    return regexp_substr
  9    ( httpuritype
 10      ( 'http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q=' ||
 11        utl_url.escape(p_text) ||
 12        '&langpair=' || p_source_language_code || '|' || p_target_language_code
 13      ).getclob()
 14    , '[^"]+'
 15    , 1
 16    , 6
 17    );
 18  end my_translate;
 19  /

Function created.

And I can execute it to translate an English sentence to Dutch:

rwijk@ORA11202> exec dbms_output.put_line(my_translate('How do I translate English sentences?','en','nl'));
Hoe vertaal ik Engels zinnen?

PL/SQL procedure successfully completed.

Note that the translation is not perfect as the Dutch sentence should read "Hoe vertaal ik Engelse zinnen?". And to demonstrate that other languages work as well:

rwijk@ORA11202> exec dbms_output.put_line(my_translate('How do I translate English sentences?','en','fr'));
Comment puis-je traduire des phrases en anglais?

PL/SQL procedure successfully completed.

rwijk@ORA11202> exec dbms_output.put_line(my_translate('How do I translate English sentences?','en','de'));
Wie übersetze ich englische Sätze?

PL/SQL procedure successfully completed.

rwijk@ORA11202> exec dbms_output.put_line(my_translate('How do I translate English sentences?','en','es'));
¿Cómo traducir frases Inglés?

PL/SQL procedure successfully completed.

Another example is to get geographical data from Google, using the Google Geocoding API. Here is an example generating XML output with the old address of Oracle Netherlands:

rwijk@ORA11202> select httpuritype('http://maps.googleapis.com/maps/api/geocode/xml?latlng=52.0715554,5.0514789&sensor=false').getclob()
  2    from dual
  3  /

HTTPURITYPE('HTTP://MAPS.GOOGLEAPIS.COM/MAPS/API/GEOCODE/XML?LATLNG=52.0715554,5
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<GeocodeResponse>
 <status>OK</status>
 <result>
  <type>street_address</type>
  <formatted_address>Rijnzathe 6, 3454 De
Meern, The Netherlands</formatted_addres
s>
  <address_component>
   <long_name>6</long_name>
   <short_name>6</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Rijnzathe</long_name>
   <short_name>Rijnzathe</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Vleuten-De Meern</long_name
>
   <short_name>Vleuten-De Meern</short_name>
   <type>sublocality</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Oudenrijn</long_name>
   <short_name>Oudenrijn</short_name>
   <type>locality</type>
   <type>political</type>
  </address_component>
  ...
  [removed lots of lines]
  ...
  </geometry>
 </result>
</GeocodeResponse>



1 row selected. 

Google has a lot more API's available. You can find them here.

I'd like to end this year of blogging with this query:

rwijk@ORA11202> select regexp_substr
  2         ( httpuritype
  3           ( 'http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q=' ||
  4             utl_url.escape('Ik wens alle lezers van deze blog een gelukkig nieuwjaar') ||
  5             '&langpair=nl|' || column_value
  6           ).getclob()
  7         , '[^"]+'
  8         , 1 
  9         , 6 
 10         ) wish
 11    from table(sys.dbms_debug_vc2coll('en','fr','de','es','it','ru','zh','in','pl','sv','fi','ar','el'))
 12  /

WISH
---------------------------------------------------------------------------------------
I wish all readers of this blog a happy new year
Je souhaite à tous les lecteurs de ce blog une heureuse nouvelle année


Ich wünsche allen Lesern dieses Blogs ein frohes neues Jahr 
Les deseo a todos los lectores de este blog un feliz año nuevo
Auguro a tutti i lettori di questo blog un felice anno nuovo 
Я желаю всем читателям этого блога с новым годом
我希望这个博客的所有读者新年快乐 
Saya berharap semua pembaca blog ini tahun baru bahagia
Życzę wszystkim czytelnikom tego bloga, szczęśliwego nowego roku
Jag önskar alla läsare av denna blogg ett gott nytt år
Toivotan kaikille lukijoille tämän blogin onnellista uutta vuotta


أتمنى لجميع قراء هذا بلوق سنة جديدة سعيدة
Εύχομαι σε όλους τους αναγνώστες αυτού του blog ένα ευτυχισμένο νέο έτος

13 rows selected.

Aggregating over zero rows

In my presentation and paper about grouping last year, I mentioned that an aggregation query without a group by clause is the same as grouping by the empty grouping set. So this query:

SQL> select count(*)
  2    from emp
  3  /

  COUNT(*)
----------
        14

1 rij is geselecteerd.

is the same as:

SQL> select count(*)
  2    from emp
  3   group by ()
  4  /

  COUNT(*)
----------
        14

1 rij is geselecteerd.

However, this is not always true (also known as "false"). It is not true when you aggregate over zero rows. If I repeat the query above, but filter on deptno = 40 (there are no employees in that department), then there is an odd difference:

SQL> select count(*)
  2    from emp
  3   where deptno = 40
  4  /

  COUNT(*)
----------
         0

1 rij is geselecteerd.

SQL> select count(*)
  2    from emp
  3   where deptno = 40
  4   group by ()
  5  /

Er zijn geen rijen geselecteerd.

It's not that the empty grouping set is weird or anything, it's the fact that a group by clause is present. The documentation states:

"In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view."

So it seems that the internal implementation looks something like this in pseudocode:

if not "group by clause present"
then
  return exactly one row
else
  return as much rows as there are groups
end if

Even if we are grouping by a regular column instead of the empty grouping set, we get a "No rows selected":

SQL> select deptno
  2       , count(*)
  3    from emp
  4   where deptno = 40
  5   group by deptno
  6  /

Er zijn geen rijen geselecteerd.

We can also see the same behaviour when using the group by extensions, like rollup and cube. They typically show a grand total, like in:

SQL> select deptno
  2       , count(*)
  3    from emp
  4   group by rollup(deptno)
  5  /

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        20          5
        30          6
                   14

4 rijen zijn geselecteerd.

But when there are zero rows, not only the regular group rows disappear, but also the grand total:

SQL> select deptno
  2       , count(*)
  3    from emp
  4   where deptno = 40
  5   group by rollup(deptno)
  6  /

Er zijn geen rijen geselecteerd.

Which is now less of a surprise, because this query is equivalent to:

SQL> select deptno
  2       , count(*)
  3    from emp
  4   where deptno = 40
  5   group by grouping sets (deptno,())
  6  /

Er zijn geen rijen geselecteerd.

Which equals:

SQL> select deptno
  2       , count(*)
  3    from emp
  4   where deptno = 40
  5   group by deptno
  6   union all
  7  select null
  8       , count(*)
  9    from emp
 10   where deptno = 40
 11   group by ()
 12  /

Er zijn geen rijen geselecteerd.

And then we recognize the queries listed earlier.

So the behaviour is consistent, but it surprised me, even though it is clearly documented.

OOW10

Yesterday I came back from my third Oracle OpenWorld. This year I was not on a bloggers pass, so I feel less obliged to cover the event. But I know there are people out there that like to read about my experiences and I still like it very much at San Francisco, so here is a small write-up about my experiences at Oracle OpenWorld 2010.

The event started at Sunday, but later than usual: 12:30 PM I think, instead of 8:30 AM in previous years. For the first time I had to wait in a queue to register and receive the conference pass. I also received an Oracle Develop t-shirt, a conference book and the Appreciation Event wristband, but to my surprise no bag. The guy behind the counter pointed at the screen that said what he was allowed to give, and the screen said nothing about a bag. It felt strange, because except for Frits Hoogland, everybody seemed to wear that bag. Not really a problem, since I have enough of them at home. But I did not want to carry the stuff with me all day, so I decided to go back to the hotel and drop the book and t-shirt.

The sessions I saw on Sunday differed much in quality. I especially liked Cary Millsap's lesson in how to prepare for and deliver a presentation. Very inspiring. Even though I knew all content about messed-up apps already, because they are on his blog as well. I also like Richard Foote's presentation about indexes and Chris Muir's one on load testing APEX applications. In the evening, all ACEs and ACE Directors were transported to a very nice boat for a trip on the bay, with nice food, conversations and magnificent views of San Francisco by night.

On Monday I visited four sessions, which were all pretty good. First a live PL/SQL Challenge by Steven Feuerstein, with some interesting questions. No prices here, but Steven had created a secondary challenge for the people present at Oracle OpenWorld, where I won a second place and a 100 dollar Amazon gift card. Thanks Steven! Another session I enjoyed was Lucas Jellema's and Alex Nuijten's Xenogenetics session, about some use cases for using Java in PL/SQL, among others. In the evening I visited OTN Night, mainly for some food :-).

Tuesday I went to Oracle Closed World together with Frits Hoogland, where I saw a very good presentation of Kyle Hayley about Visual SQL Tuning with his Embarcadero product. The steps I intuitively take to solve performance problems with my explain plans and tkprofs were nicely illustrated with graphs. Pick as a starting point the table with the best filter predicates, then join down (to details) and then join up (to master tables). When visualizing all that, it becomes obvious. At Closed World I received a t-shirt which I wore on Thursday. This generated quite some comments ("Is that from Mogens? He is a funny guy.") and even from strangers who liked the "Meat.Beer.Explode." phrase. I returned to my hotel pretty early to do some preparation for my own session and in the evening I went to Ruby Skye for the Oracle Benelux Party.

Wednesday was my own presentation called "Oracle Database 11g's Result Cache". At Hotel Nikko in the Bay View room. This room was on the 25th floor and had a magnificent view of the bay. The session went well, and I was exactly on time. Unfortunately, I heard later that not everybody could see the SQL*Plus screen well, even though I had specifically asked the guy at the back of the room if he could read it... I received some interesting questions at the end, which I will try to answer in some future blogpost. Next up was the Bloggers Meetup, which was fun. And afterwards, Roel Hartman, Jacco Landlust and his girlfriend Margot and me went to the appreciation event, where I saw Don Henley, the Black Eyed Peas (slow start, but great ending) and two songs of the Steve Miller Band.

On Thursday I only saw two sessions, of which I really liked one. It was Jože Senegačnik's session called Query Transformations about all kinds of transformations the cost based optimizer performs when hard parsing queries. These transformations are visible in the 10053 trace file, which I rarely use. The session was great because it fitted great with what I already knew, but there were lots of interesting little details I did not know. After gathering at the OTN Lounge with some fellow ACEs and skipping the wrap-up party, I headed back to my hotel.

Upcoming conferences

Tomorrow I'm off to Oracle OpenWorld for the third time in a row. I'm looking forward to a week attending great sessions, meeting friends, visiting the appreciation event, the Oracle ACE dinner and the Blogger's Meetup and presenting my session about the result cache. If you want to attend that session, the session details are:

ID#: S319106
Title: Oracle Database 11g’s Result Cache
Date: 22-SEP-10
Time: 13:00-14:00
Venue: Hotel Nikko
Room: Bay View

I will also be giving the result cache presentation at two other conferences closer to home.

At October 5, Planboard - known for their successful Oracle DBA Symposiums - organizes their first Oracle Developer Symposium. This is a conference you should not miss if you are a Dutch speaking Oracle developer. One day, with three tracks: Database, APEX and Fusion Middleware. More details can be found here.

At October 27 and 28, ODTUG OPP 2010 will be held in Brussels. They have combined two conferences: one about PL/SQL and one about APEX, with 5 tracks in total.

Last wednesday I rehearsed the presentation at a knowledge session at CIBER. I will be making some adjustments, but the Wednesday version of my presentation can be downloaded here:

The powerpoint and the scripts.

European Summer Time

I needed a function to determine how many hours fit in a given day. In the Netherlands, a function that does "return 24;" is not good enough. In the northern hemisphere, Daylight Saving Time is quite common. And in Europe, except Iceland, we have European Summer Time which states that the clock moves one hour forward on the last Sunday in March and it moves one hour backwards on the last Sunday in October. Which means there is a day in March that consists of 23 hours and in October there is one with 25 hours.

So, for my function I needed two other functions: one that returns the date where European Summer Time starts and one that returns the date where European Summer Time ends. I found existing code which - if it had been modularized - looked like this:

create function european_summer_time_start1 (p_year in number)
  return date
is
begin
  execute immediate
    'alter session set nls_date_language = "English"'
  ;
  return
    next_day(to_date('01-04-'||p_year,'dd-mm-rrrr')-1, 'sunday') - 7
  ;
end european_summer_time_start1;

and

create function european_summer_time_end1 (p_year in number)
  return date
is
begin
  execute immediate
    'alter session set nls_date_language="English"'
  ;
  return
    next_day(to_date('01-11-'||p_year,'dd-mm-rrrr')-1, 'sunday') - 7
  ;
end european_summer_time_end1;

It uses the NEXT_DAY function to determine the next sunday after March 31 and October 31, and then subtracts a week to get the last Sunday of March and October. The second parameter ('sunday') is in the date language of the session. And to be sure the function runs correctly, the nls_date_language parameter is set. I don't like this at all, because it sets a session parameter without setting the value back after the function has finished. Code might run differently depending on whether the function has been run previously in the session or not. I want the code to be NLS independent and I came up with these two functions:

create function european_summer_time_start2 (p_year in number)
  return date
is
begin
  return
    trunc
    ( to_date(to_char(p_year) || '0401','yyyymmdd')
    , 'iw'
    ) - interval '1' day
  ;
end european_summer_time_start2;

and

create function european_summer_time_end2 (p_year in number)
  return date
is
begin
  return
    trunc
    ( to_date(to_char(p_year) || '1101','yyyymmdd')
    , 'iw'
    ) - interval '1' day
  ;
end european_summer_time_end2;

Here I use the TRUNC-function to set the date value back to the beginning of the ISO-week, which is always on Monday, regardless of NLS-settings. Then subtract one day and we have the last Sunday of March/October.

Then I saw the formula on Wikipedia in the same link I mentioned earlier:

Formula used to calculate the beginning of European Summer Time:
Sunday (31 − (5 * y ÷ 4 + 4) mod 7) March at 01:00 GMT

Formula used to calculate the end of European Summer Time:
Sunday (31 − (5 * y ÷ 4 + 1) mod 7) October at 01:00 GMT

Implementing these formulas lead to a third variant of the two functions:

create function european_summer_time_start3 (p_year in number)
  return date
is
begin
  return
    to_date
    ( to_char(p_year) ||
      '03' ||
      to_char(31 - trunc(mod(5 * p_year / 4 + 4, 7)))
    , 'yyyymmdd'
    )
  ;
end european_summer_time_start3;

and

create function european_summer_time_end3 (p_year in number)
  return date
is
begin
  return
    to_date
    ( to_char(p_year) ||
      '10' ||
      to_char(31 - trunc(mod(5 * p_year / 4 + 1, 7)))
    , 'yyyymmdd'
    )
  ;
end european_summer_time_end3;

Next, I compared the three variants. They return the right dates:

rwijk@TEST10> select european_summer_time_start1(year) ws1
  2       , european_summer_time_start2(year) ws2
  3       , european_summer_time_start3(year) ws3
  4       , european_summer_time_end1(year)   sw1
  5       , european_summer_time_end2(year)   sw2
  6       , european_summer_time_end3(year)   sw3
  7    from ( select 1999 + level year
  8             from dual
  9          connect by level <= 11
 10         )
 11  /

WS1        WS2        WS3        SW1        SW2        SW3
---------- ---------- ---------- ---------- ---------- ----------
26-03-2000 26-03-2000 26-03-2000 29-10-2000 29-10-2000 29-10-2000
25-03-2001 25-03-2001 25-03-2001 28-10-2001 28-10-2001 28-10-2001
31-03-2002 31-03-2002 31-03-2002 27-10-2002 27-10-2002 27-10-2002
30-03-2003 30-03-2003 30-03-2003 26-10-2003 26-10-2003 26-10-2003
28-03-2004 28-03-2004 28-03-2004 31-10-2004 31-10-2004 31-10-2004
27-03-2005 27-03-2005 27-03-2005 30-10-2005 30-10-2005 30-10-2005
26-03-2006 26-03-2006 26-03-2006 29-10-2006 29-10-2006 29-10-2006
25-03-2007 25-03-2007 25-03-2007 28-10-2007 28-10-2007 28-10-2007
30-03-2008 30-03-2008 30-03-2008 26-10-2008 26-10-2008 26-10-2008
29-03-2009 29-03-2009 29-03-2009 25-10-2009 25-10-2009 25-10-2009
28-03-2010 28-03-2010 28-03-2010 31-10-2010 31-10-2010 31-10-2010

11 rows selected.

But as mentioned in the Wikipedia article, the third variant produces wrong results in 2100, which is not a leap year:

rwijk@TEST10> select european_summer_time_start1(year) ws1
  2       , european_summer_time_start2(year) ws2
  3       , european_summer_time_start3(year) ws3
  4       , european_summer_time_end1(year)   sw1
  5       , european_summer_time_end2(year)   sw2
  6       , european_summer_time_end3(year)   sw3
  7    from ( select 2097 + level year
  8             from dual
  9          connect by level <= 5
 10         )
 11  /

WS1        WS2        WS3        SW1        SW2        SW3
---------- ---------- ---------- ---------- ---------- ----------
30-03-2098 30-03-2098 30-03-2098 26-10-2098 26-10-2098 26-10-2098
29-03-2099 29-03-2099 29-03-2099 25-10-2099 25-10-2099 25-10-2099
28-03-2100 28-03-2100 27-03-2100 31-10-2100 31-10-2100 30-10-2100
27-03-2101 27-03-2101 26-03-2101 30-10-2101 30-10-2101 29-10-2101
26-03-2102 26-03-2102 25-03-2102 29-10-2102 29-10-2102 28-10-2102

5 rows selected.

It probably is the faster function though, as it doesn't need date arithmetic and ISO week calculation, but I was curious in how much it differs. Here is the test that I used:

rwijk@TEST10> var N number
rwijk@TEST10> exec :N := 100000

PL/SQL procedure successfully completed.

rwijk@TEST10> set timing on
rwijk@TEST10> declare
  2    l_date date;
  3  begin
  4    for i in 1..:N
  5    loop
  6      l_date := european_summer_time_start1(2010);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.20
rwijk@TEST10> declare
  2    l_date date;
  3  begin
  4    for i in 1..:N
  5    loop
  6      l_date := european_summer_time_start2(2010);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.30
rwijk@TEST10> declare
  2    l_date date;
  3  begin
  4    for i in 1..:N
  5    loop
  6      l_date := european_summer_time_start3(2010);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.79

The test was executed several times with consistent timings. So now I have to decide whether I use the function that works fastest but breaks in 2100, or one that runs a little slower but keeps on working. Performance doesn't really matter here, so I'll stick with the second variant.

The PL/SQL Challenge effect

In Google Analytics I noticed a strange peak in my page visits. On a normal weekday when I haven't posted something new, approximately 200 people visit one or more blogposts here. But on Thursday July 8, there were 299. When zooming in on that day, I noticed that my blogpost about SAVE EXCEPTIONS was visited 101 times, where 5 or 10 per day is normal for that blogpost. When something like this happens, it is usually caused by someone posting a link to my blog, but that was not the case now. Then I looked at the searched keywords and I saw these lines (I filtered out lots of other rows):

save exceptions 8
oracle save exceptions 6
forall log errors save exceptions 2
forall save exceptions log errors 2
forall save exceptions oracle 2
log errors save exceptions 2
oracle forall save exceptions 2
save exception 2
"log errors" "save exceptions" 1
"log errors" "save exceptions" "dbms_errlog.create_error_log" 1
"log errors" "save exceptions" forall 1
"save exceptions" "log errors" 1
_http://rwijk.blogspot.com/2007/11/save-exceptions.html 1
both log errors and save exception 1
create_error_log "save exceptions" 1
forall 'log error' 'save exceptions' 1
forall log errors save exception both togheter 1
forall save exception 1
forall save exceptions 1
forall save exceptions and log errors together 1
forall save exceptions log errors sql%bulk_exceptions 1
forall statement "log errors" "save exceptions" "bulk_exceptions" 1
log eroors save exceptions forall 1
log error oracle save exceptions 1
log error save exceptions 1
log errors save exceptions forall oracle 1
log errors,save exception with for all oracle 1
oracle bulk "save exceptions" 1
oracle forall log errors save exceptions 1
oracle forall save exception 1
oracle forall save exceptions dbms_errlog bulk_exceptions 1
oracle pl sql log errors save exceptions 1
oracle save exceptions forall 1
oracle save exceptions log errors 1
oracle save exceptions log errors for all 1
oracle save exceptions vs log errors 1
pl/sql log errors save exception forall 1
pl/sql save exceptions 1
plsql using log errors and save exception 1
save exceprions 1
save exceptions example 1
save exceptions in oracle sql 1
save exceptions in pl sql 1
save exceptions log error oracle 1
save exeptions 1
use log errors and save exception in forall orcle 1
using save exceptions and log errors in the same forall statement 1
using save exceptions and log errors inside forall pl sql 1
when save exceptions are used in oracle 1

And then I remembered last week's PL/SQL Challenge. On July 8, it had a nice question about what happens when you combine the FORALL SAVE EXCEPTIONS with a LOG ERRORS clause...

And while I'm talking about the PL/SQL Challenge: if you haven't played it yet and you want to learn the language better, then you should give it a try. If you played the game and you haven't learned anything, then at least you'll have a high score :-). The questions are very diverse regarding the topics and regarding difficulty. So there will always be topics with which you are highly familiar and those with which you are not. For example, I use PL/SQL almost 15 years now, but somehow I almost never used the UTL_FILE package. It just seems counterintuitive to me to work with files when you have a database at your disposal. But by playing the quiz I learned the package inside out.

And for those of you who wished me luck and wanted to know how the playoff went: well, it went smooth. So compliments to the developers of the site. The 10 questions itself contained a lot of text and code to read and grasp in just 15 minutes. And I thought the questions were on average tougher than normal. I managed to answer all questions in time, but I had to rush. I'm not so sure about the correctness of all my answers, though. We'll see.

One Oracle forum to rule them all

Previous year I already expressed some of my thoughts on the OTN Forums and Stack Overflow here. In short my story was: I absolutely love the Q&A engine of Stack Overflow, but the best answers can be found on OTN. However, with more knowledgeable people appearing on Stack Overflow, the gap is closing. In the comments section and from conversations I came to know that there is still some resistance with Stack Overflow to some people on two other points:

- Stack Overflow is primarily aimed at the developers community, so a DBA won't feel much at home as there are only a few real DBA questions. For Oracle this is a real pity, because there is a huge amount of knowledge about Oracle databases among the DBA's. If we could only welcome them as well...

- To categorize questions, Stack Overflow uses tags. People who answer questions use tags to see all potentially interesting questions. However, you'll see lots of not relevant questions as well this way. You can't ever filter out all questions that you don't want to see, but the signal-to-noise ratio should at least be above some personal threshold. And this can be hard to achieve on Stack Overflow depending on your interests.

I ended my blog post last year with the phrase "I wish that the best of both worlds can be combined somewhere in the future.". And this wish can come true, with Stack Exchange. It's now possible to use the brilliant Q&A engine of Stack Overflow and create a forum site for only Oracle related questions. This should be very interesting for DBA's as well, and because of the Oracle-focus, it becomes much easier to express your interests. The community can tag and re-tag all questions and direct them to the world's best experts and wannabe's in Oracle land on a certain field. I have a dream, of no more hardly readable questions with lousy titles, no more inaccessible forum sites, no more trolling and no more questions without reasonable answers. I dream of one Oracle forum that unites all other Oracle forums, so we can learn from ALL experts, not just the few that happen to be present in a particular forum.

So, I support Gary Myers' idea and hope that you do as well. And if you do, please click the FOLLOW button here. The technology is ready, the proposal is there, now we only need to express our wish as a community and commit ourselves to it.

Spread the word.

Oracle 11g's Result Cache at OOW10?

Oracle Mix has a Suggest-a-Session facility for Oracle OpenWorld 2010. Some 120+ sessions are already suggested and you can vote on the sessions you'd like to see on Oracle OpenWorld. The sessions with the most votes will be selected in the program.

My proposal is a session about Oracle 11g's Result Cache. Already 24 people have voted for my session (thank you!), but it looks like I need a couple more. So, if you'd like to see a technical session about the result cache at Oracle OpenWorld or if you just want to support me :-), please vote. Note that you have to vote for at least 3 sessions for your votes to count. The voting closes this Saturday, June 20.

UPDATE 10th July:
I've just received a speaker invitation to present my session at Oracle OpenWorld. Thank you all for the extra votes that made this possible!

Simulating LAG/LEAD IGNORE NULLS

From version 11 onwards, the analytic functions LAG and LEAD have the extra option to RESPECT or IGNORE NULLS. This comes in handy when -for example- you don't want a regular next value like this:

rwijk@ORA11GR2> select empno
  2       , comm
  3       , lead(comm) over (order by empno) next_comm
  4    from emp
  5   order by empno
  6  /

     EMPNO       COMM  NEXT_COMM
---------- ---------- ----------
      7369                   300
      7499        300        500
      7521        500
      7566                  1400
      7654       1400
      7698
      7782
      7788
      7839                     0
      7844          0
      7876
      7900
      7902
      7934

14 rows selected.

but a next value which ignore nulls, like this:

rwijk@ORA11GR2> select empno
  2       , comm
  3       , lead(comm ignore nulls) over (order by empno) next_comm
  4    from emp
  5   order by empno
  6  /

     EMPNO       COMM  NEXT_COMM
---------- ---------- ----------
      7369                   300
      7499        300        500
      7521        500       1400
      7566                  1400
      7654       1400          0
      7698                     0
      7782                     0
      7788                     0
      7839                     0
      7844          0
      7876
      7900
      7902
      7934

14 rows selected.

As you can see the LEAD function is now looking for the next non null value, instead of just the next value. For example, the next_comm value for empno 7521 is not NULL (of empno 7566), but 1400 (of empno 7654).

Last week I attended the ODTUG preview sessions at AMIS, and one of the sessions was Alex Nuijten's session about analytic functions. When he mentioned the new IGNORE NULLS option, he also showed a query how you can simulate the same functionality in versions prior to 11g. That query was a rather lengthy one, so I decided I'd try to come up with a shorter one. And this is the result:

rwijk@ORA11GR2> select empno
  2       , comm
  3       , max(comm) over (order by grp range between 1 following and 1 following) next_comm
  4    from ( select empno
  5                , comm
  6                , sum(nvl2(comm,1,0)) over (order by empno) grp
  7             from emp
  8         )
  9   order by empno
 10  /

     EMPNO       COMM  NEXT_COMM
---------- ---------- ----------
      7369                   300
      7499        300        500
      7521        500       1400
      7566                  1400
      7654       1400          0
      7698                     0
      7782                     0
      7788                     0
      7839                     0
      7844          0
      7876
      7900
      7902
      7934

14 rows selected.

And posting it here on my blog will surely mean someone will show up and post an even shorter one :-).

For completeness, doing the same for a LAG ... IGNORE NULLS query:

rwijk@ORA11GR2> select empno
  2       , comm
  3       , lag(comm ignore nulls) over (order by empno) prev_comm
  4    from emp
  5   order by empno
  6  /

     EMPNO       COMM  PREV_COMM
---------- ---------- ----------
      7369
      7499        300
      7521        500        300
      7566                   500
      7654       1400        500
      7698                  1400
      7782                  1400
      7788                  1400
      7839                  1400
      7844          0       1400
      7876                     0
      7900                     0
      7902                     0
      7934                     0

14 rows selected.

This one can be simulated in versions prior to 11g with the following query:

rwijk@ORA11GR2> select empno
  2       , comm
  3       , max(comm) over (order by grp range between 1 following and 1 following) prev_comm
  4    from ( select empno
  5                , comm
  6                , sum(nvl2(comm,1,0)) over (order by empno desc) grp
  7             from emp
  8         )
  9   order by empno
 10  /

     EMPNO       COMM  PREV_COMM
---------- ---------- ----------
      7369
      7499        300
      7521        500        300
      7566                   500
      7654       1400        500
      7698                  1400
      7782                  1400
      7788                  1400
      7839                  1400
      7844          0       1400
      7876                     0
      7900                     0
      7902                     0
      7934                     0

14 rows selected.

Exponential Moving Average

There was a nice question on OTN today about whether there is a standard Oracle function to calculate the exponential moving average. The answer is that there is no such function, but with the model clause, you can calculate it very easy. And it's a great example of what I mean with "variable number of calculations based on calculated values", written in my third part of the model clause tutorial.

Before today, I didn't even know what an exponential moving average was exactly. You can read more about it here on Wikipedia or here with a nice example. From the first link:

An exponential moving average (EMA), applies weighting factors which decrease exponentially. The weighting for each older data point decreases exponentially, giving much more importance to recent observations while still not discarding older observations entirely.

From the second link:

The formula for calculating an Exponential Moving Average (EMA) is:

X = (K * (C - P)) + P

Where:

X = Current EMA (i.e. EMA to be calculated)

C = Current original data value

K = Smoothing Constant

P = Previous EMA

(The first EMA in the range to be calculated is arbitrary and can be the corresponding original data value or, often, a Simple Moving Average value.

Where:

K = Smoothing Constant = 2 / (1 + n)

And this formula is followed by an example which I extended a bit, using this table:

rwijk@TEST10> create table sales (product,month,amount)
  2  as
  3  select 'A', date '2009-01-01', 10 from dual union all
  4  select 'A', date '2009-02-01', 15 from dual union all
  5  select 'A', date '2009-03-01', 17 from dual union all
  6  select 'A', date '2009-04-01', 20 from dual union all
  7  select 'A', date '2009-05-01', 22 from dual union all
  8  select 'A', date '2009-06-01', 20 from dual union all
  9  select 'A', date '2009-07-01', 25 from dual union all
 10  select 'A', date '2009-08-01', 27 from dual union all
 11  select 'A', date '2009-09-01', 30 from dual union all
 12  select 'A', date '2009-10-01', 35 from dual union all
 13  select 'A', date '2009-11-01', 37 from dual union all
 14  select 'A', date '2009-12-01', 40 from dual union all
 15  select 'B', date '2009-01-01', 0 from dual union all
 16  select 'B', date '2009-02-01', 50 from dual union all
 17  select 'B', date '2009-03-01', 10 from dual union all
 18  select 'B', date '2009-04-01', 40 from dual union all
 19  select 'B', date '2009-05-01', 15 from dual union all
 20  select 'B', date '2009-06-01', 35 from dual union all
 21  select 'B', date '2009-07-01', 30 from dual union all
 22  select 'B', date '2009-08-01', 30 from dual union all
 23  select 'B', date '2009-09-01', 20 from dual union all
 24  select 'B', date '2009-10-01', 20 from dual union all
 25  select 'B', date '2009-11-01', 20 from dual union all
 26  select 'B', date '2009-12-01', 20 from dual
 27  /

Table created.

The records from product A match the example in the link. I made up the numbers from product B. Here is the model clause query that implements the formula. Note how the formula directly translate into the one and only rule of the model clause. The "smoothing constant" K is set to .5, based on a window of values (n) equalling 3.

rwijk@TEST10> select product
  2       , month
  3       , amount
  4       , round(ema,3) "Exponential Moving Average"
  5    from sales
  6   model
  7         partition by (product)
  8         dimension by (month)
  9         measures (amount,0 ema)
 10         ( ema[any] order by month
 11           = nvl2
 12             ( ema[add_months(cv(),-1)]
 13             , ( .5 * (amount[cv()] - ema[add_months(cv(),-1)])) + ema[add_months(cv(),-1)]
 14             , amount[cv()]
 15             )
 16         )
 17   order by product
 18       , month
 19  /

PRO MONTH                   AMOUNT Exponential Moving Average
--- ------------------- ---------- --------------------------
A   01-01-2009 00:00:00         10                         10
A   01-02-2009 00:00:00         15                       12,5
A   01-03-2009 00:00:00         17                      14,75
A   01-04-2009 00:00:00         20                     17,375
A   01-05-2009 00:00:00         22                     19,688
A   01-06-2009 00:00:00         20                     19,844
A   01-07-2009 00:00:00         25                     22,422
A   01-08-2009 00:00:00         27                     24,711
A   01-09-2009 00:00:00         30                     27,355
A   01-10-2009 00:00:00         35                     31,178
A   01-11-2009 00:00:00         37                     34,089
A   01-12-2009 00:00:00         40                     37,044
B   01-01-2009 00:00:00          0                          0
B   01-02-2009 00:00:00         50                         25
B   01-03-2009 00:00:00         10                       17,5
B   01-04-2009 00:00:00         40                      28,75
B   01-05-2009 00:00:00         15                     21,875
B   01-06-2009 00:00:00         35                     28,438
B   01-07-2009 00:00:00         30                     29,219
B   01-08-2009 00:00:00         30                     29,609
B   01-09-2009 00:00:00         20                     24,805
B   01-10-2009 00:00:00         20                     22,402
B   01-11-2009 00:00:00         20                     21,201
B   01-12-2009 00:00:00         20                     20,601

24 rows selected.

Challenge: try this without the model clause and see if you can come up with something more comprehensive ...

SQL injection to the rescue

Ok, I may be the last person seeing this, but I thought this is just too funny not to share it. I found a way to get rid of those annoying speeding tickets.

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

OGh APEX dag

Tuesday March 30, the Dutch Oracle usergroup OGh organizes one entire day dedicated to APEX. I'm a little biased, as I helped organizing the event, but I think we succeeded in putting together a great program, as you can see here (warning: Dutch). The lineup includes some very well known APEX speakers like Patrick Wolf, Dimitri Gielis and Roel Hartman.

The schedule also includes three sessions where you'll hear from companies who have chosen APEX as their development tool of choice. So if you need to convince your boss to choose the tool that matches the skill set of his team members, send him or her to this day. And be quick, because there are only 7 places left, as of this moment.

Hope to see you there.

enq: JI - contention

To implement entity rules and inter-entity rules, one of the options is to use an on commit-time fast refreshable materialized view with a check constraint on top. You can read a few examples in these posts. It's an elegant way to check for conditions at commit-time, and quite remarkable when you see it for the first time. But there is a serious caveat to this way of implementing business rules, which may or may not be relevant for your situation. In any case, it doesn't hurt to be aware of this caveat. To show what I mean I create a table called percentages_per_year:

rwijk@ORA11GR1> create table percentages_per_year (ym,percentage)
  2  as
  3   select date '2005-12-01' + numtoyminterval(level,'month')
  4        , case mod(level,3) when 0 then 5 else 10 end
  5     from dual
  6  connect by level <= 48
  7  /

Table created.

rwijk@ORA11GR1> select * from percentages_per_year
  2  /

YM                  PERCENTAGE
------------------- ----------
01-01-2006 00:00:00         10
01-02-2006 00:00:00         10
01-03-2006 00:00:00          5
01-04-2006 00:00:00         10
01-05-2006 00:00:00         10
01-06-2006 00:00:00          5
01-07-2006 00:00:00         10
01-08-2006 00:00:00         10
01-09-2006 00:00:00          5
01-10-2006 00:00:00         10
01-11-2006 00:00:00         10
01-12-2006 00:00:00          5
01-01-2007 00:00:00         10
01-02-2007 00:00:00         10
01-03-2007 00:00:00          5
01-04-2007 00:00:00         10
01-05-2007 00:00:00         10
01-06-2007 00:00:00          5
01-07-2007 00:00:00         10
01-08-2007 00:00:00         10
01-09-2007 00:00:00          5
01-10-2007 00:00:00         10
01-11-2007 00:00:00         10
01-12-2007 00:00:00          5
01-01-2008 00:00:00         10
01-02-2008 00:00:00         10
01-03-2008 00:00:00          5
01-04-2008 00:00:00         10
01-05-2008 00:00:00         10
01-06-2008 00:00:00          5
01-07-2008 00:00:00         10
01-08-2008 00:00:00         10
01-09-2008 00:00:00          5
01-10-2008 00:00:00         10
01-11-2008 00:00:00         10
01-12-2008 00:00:00          5
01-01-2009 00:00:00         10
01-02-2009 00:00:00         10
01-03-2009 00:00:00          5
01-04-2009 00:00:00         10
01-05-2009 00:00:00         10
01-06-2009 00:00:00          5
01-07-2009 00:00:00         10
01-08-2009 00:00:00         10
01-09-2009 00:00:00          5
01-10-2009 00:00:00         10
01-11-2009 00:00:00         10
01-12-2009 00:00:00          5

48 rows selected.

The data is such that all percentages in a year add up to exactly 100. To ensure this is always the case in the future, I implement it as a business rule using an on commit-time fast refreshable materialized view:

rwijk@ORA11GR1> create materialized view log on percentages_per_year
  2    with sequence, rowid (ym,percentage) including new values
  3  /

Materialized view log created.

rwijk@ORA11GR1> create materialized view mv
  2    refresh fast on commit
  3  as
  4  select to_char(ym,'yyyy') year
  5       , sum(percentage)    year_percentage
  6       , count(percentage)  count_percentage
  7       , count(*)           count_all
  8    from percentages_per_year
  9   group by to_char(ym,'yyyy')
 10  /

Materialized view created.

rwijk@ORA11GR1> alter materialized view mv
  2    add constraint year_percentage_100_ck
  3    check (year_percentage=100)
  4  /

Materialized view altered.

rwijk@ORA11GR1> select * from mv
  2  /

YEAR YEAR_PERCENTAGE COUNT_PERCENTAGE  COUNT_ALL
---- --------------- ---------------- ----------
2009             100               12         12
2008             100               12         12
2007             100               12         12
2006             100               12         12

4 rows selected.

rwijk@ORA11GR1> exec dbms_stats.gather_table_stats(user,'percentages_per_year')

PL/SQL procedure successfully completed.

Now the idea is to run 4 jobs, each updating 12 records of one year. To see what's going on inside each job, I create a logging table with logging procedures that run autonomous transactions:

rwijk@ORA11GR1> create table t_log
  2  ( year      number(4)
  3  , starttime timestamp(6)
  4  , endtime   timestamp(6)
  5  )
  6  /

Table created.

rwijk@ORA11GR1> create procedure log_start (p_year in t_log.year%type)
  2  is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into t_log (year,starttime)
  6    values (p_year,systimestamp)
  7    ;
  8    commit;
  9  end log_start;
 10  /

Procedure created.

rwijk@ORA11GR1> create procedure log_end (p_year in t_log.year%type)
  2  is
  3    pragma autonomous_transaction;
  4  begin
  5    update t_log
  6       set endtime = systimestamp
  7     where year = p_year
  8    ;
  9    commit;
 10  end log_end;
 11  /

Procedure created.

The jobs will run a procedure called update_percentages. The procedure sets its module and action column, to be able to identify the trace files that are created. It logs itself before the update and after the commit:

rwijk@ORA11GR1> create procedure update_percentages (p_year in number)
  2  is
  3  begin
  4    dbms_application_info.set_module('p',to_char(p_year))
  5    ;
  6    dbms_session.session_trace_enable
  7    ;
  8    log_start(p_year)
  9    ;
 10    update percentages_per_year
 11       set percentage = case when extract(month from ym) <= 8 then 9 else 7 end
 12     where extract(year from ym) = p_year
 13    ;
 14    commit
 15    ;
 16    log_end(p_year)
 17    ;
 18  end update_percentages;
 19  /

Procedure created.

And now let 4 jobs run:

rwijk@ORA11GR1> declare
  2    l_job_id binary_integer;
  3  begin
  4    for i in 2006 .. 2009
  5    loop
  6      dbms_job.submit(l_job_id,'rwijk.update_percentages('||to_char(i)||');');
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

rwijk@ORA11GR1> commit
  2  /

Commit complete.

rwijk@ORA11GR1> pause

After all jobs have run concurrently and have finished, the logging table looks like this:

rwijk@ORA11GR1> select year
  2       , starttime
  3       , endtime
  4       , endtime - starttime duration
  5    from t_log
  6   order by endtime
  7  /

 YEAR STARTTIME                       ENDTIME
----- ------------------------------- -------------------------------
DURATION
-------------------------------
 2007 14-JAN-10 09.59.45.625000 PM    14-JAN-10 09.59.45.843000 PM
+000000000 00:00:00.218000

 2006 14-JAN-10 09.59.45.609000 PM    14-JAN-10 09.59.45.875000 PM
+000000000 00:00:00.266000

 2008 14-JAN-10 09.59.45.656000 PM    14-JAN-10 09.59.45.906000 PM
+000000000 00:00:00.250000

 2009 14-JAN-10 09.59.45.625000 PM    14-JAN-10 09.59.45.937000 PM
+000000000 00:00:00.312000


4 rows selected.

You can see the overlap of the processes. The process for year 2007 has finished first, and doesn't experience any noticeable wait. The other three processes do. A look at the trace file shows one line like this:

WAIT #8: nam='enq: JI - contention' ela= 85497 name|mode=1246298118 view object #=106258 0=0 obj#=-1 tim=7377031494

This line is followed after the COMMIT (XCTEND rlbk=0) in the trace file. The wait event is called "enq: JI - contention", where JI stands for materialized view refresh :-). In the documention it says that JI stands for "Enqueue used during AJV snapshot refresh". So it's still unclear where those letters JI really stand for, although the J may be from the J in AJV. And google suggests that AJV stands for "Aggregate join view". Maybe there are so many two-letter enqueue names already that they just picked one?

In the WAIT line from the trace file, we see three parameters after the "ela= 85497". We can see these parameters in the v$event_name view too:

rwijk@ORA11GR1> select event#
  2       , name
  3       , parameter1
  4       , parameter2
  5       , parameter3
  6       , wait_class
  7    from v$event_name
  8   where name = 'enq: JI - contention'
  9  /

    EVENT# NAME
---------- ----------------------------------------------------------------
PARAMETER1
----------------------------------------------------------------
PARAMETER2
----------------------------------------------------------------
PARAMETER3
----------------------------------------------------------------
WAIT_CLASS
----------------------------------------------------------------
       818 enq: JI - contention
name|mode
view object #
0
Other


1 row selected.

The first parameter is "name|mode", which equals a rather strange number 1246298118. This pretty-old-but-still-very-useful article of Anjo Kolk helped me interpreting the number:

rwijk@ORA11GR1> select chr(bitand(1246298118,-16777216)/16777215) ||
  2         chr(bitand(1246298118,16711680)/65535) “Lock”
  3       , bitand(1246298118, 65535) “Mode”
  4    from dual
  5  /

Lo       Mode
-- ----------
JI          6

1 row selected.

Where lock mode 6 means an exclusive lock.

The second parameter is the object_id as you can find it in the all_objects view. The third parameter isn't useful here.

So a materialized view cannot be fast refreshed more than once in a given period. It's serialized during the commit. Normally when I update record 1 and commit and you update record 2 and commit, we don't have to wait for each other. But when having an on commit-time fast refreshable materialized view on top of the table, we do have to wait when two sessions do some totally unrelated transactions concurrently against the same table. Is this a problem? Not when the table is modified infrequently or only by a single session. But it can be a big problem when applied to a table that modifies a lot concurrently.

So be sure to use on commit-time fast refreshable materialized views for implementing business rules only on tables that are not concurrently accessed or infrequently changed. Or be prepared to be called by the users of your system ...

CAST-COLLECT versus CAST-MULTISET

At work I am building an interface using SQL object types and SQL collection types. I noticed a difference between using the COLLECT aggregate function in combination with a CAST function, versus the CAST-MULTISET method. I started out using CAST-COLLECT, but switched to CAST-MULTISET. Here is a simulation:

rwijk@ORA11GR1> create table customers
  2  ( id        number(6)    not null primary key
  3  , name      varchar2(30) not null
  4  , birthdate date         not null
  5  )
  6  /

Table created.

rwijk@ORA11GR1> create table bankaccounts
  2  ( nr              number(10)   not null primary key
  3  , customer_id     number(6)    not null
  4  , current_balance number(14,2) not null
  5  )
  6  /

Table created.

rwijk@ORA11GR1> insert into customers
  2  select 1, 'Jeffrey Lebowski', date '1964-01-01' from dual union all
  3  select 2, 'Walter Sobchak', date '1961-01-01' from dual
  4  /

2 rows created.

rwijk@ORA11GR1> create sequence customers_seq start with 3
  2  /

Sequence created.

rwijk@ORA11GR1> insert into bankaccounts
  2  select 123456789, 1, 10 from dual union all
  3  select 987654321, 1, 100 from dual union all
  4  select 234567890, 2, 2000 from dual
  5  /

3 rows created.

rwijk@ORA11GR1> select * from customers
  2  /

        ID NAME                           BIRTHDATE
---------- ------------------------------ -------------------
         1 Jeffrey Lebowski               01-01-1964 00:00:00
         2 Walter Sobchak                 01-01-1961 00:00:00

2 rows selected.

rwijk@ORA11GR1> select * from bankaccounts
  2  /

        NR CUSTOMER_ID CURRENT_BALANCE
---------- ----------- ---------------
 123456789           1              10
 987654321           1             100
 234567890           2            2000

3 rows selected.

Two simple tables with a master-detail relationship. To be able to communicate (not to store, except for simple auditing) a customer, SQL types come in handy. Three types, two object types and a collection type:

rwijk@ORA11GR1> create type to_bankaccount is object
  2  ( nr              number(10)
  3  , current_balance number(14,2)
  4  );
  5  /

Type created.

rwijk@ORA11GR1> create type ta_bankaccounts is table of to_bankaccount;
  2  /

Type created.

rwijk@ORA11GR1> create type to_customer is object
  2  ( name         varchar2(30)
  3  , birthdate    date
  4  , bankaccounts ta_bankaccounts
  5  );
  6  /

Type created.

rwijk@ORA11GR1> select type_name
  2       , typecode
  3       , attributes
  4    from user_types
  5  /

TYPE_NAME                      TYPECODE                       ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT                 OBJECT                                  2
TA_BANKACCOUNTS                COLLECTION                              0
TO_CUSTOMER                    OBJECT                                  3

3 rows selected.

The external system can call a function to get the customer information. The function below simulates that function. I used the CAST-COLLECT method:

rwijk@ORA11GR1> create function customer_with_collect
  2  ( p_customer_id in customers.id%type
  3  ) return to_customer
  4  is
  5    o_customer to_customer;
  6  begin
  7    select to_customer
  8           ( c.name
  9           , c.birthdate
 10           , ( select cast
 11                      ( collect(to_bankaccount(ba.nr,ba.current_balance))
 12                        as ta_bankaccounts
 13                      )
 14                 from bankaccounts ba
 15                where ba.customer_id = c.id
 16             )
 17           )
 18      into o_customer
 19      from customers c
 20     where c.id = p_customer_id
 21    ;
 22    return o_customer;
 23  end customer_with_collect;
 24  /

Function created.

rwijk@ORA11GR1> select type_name
  2       , typecode
  3       , attributes
  4    from user_types
  5  /

TYPE_NAME                      TYPECODE                       ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT                 OBJECT                                  2
TA_BANKACCOUNTS                COLLECTION                              0
TO_CUSTOMER                    OBJECT                                  3

3 rows selected.

rwijk@ORA11GR1> select customer_with_collect(1)
  2    from dual
  3  /

CUSTOMER_WITH_COLLECT(1)(NAME, BIRTHDATE, BANKACCOUNTS(NR, CURRENT_BALANCE))
------------------------------------------------------------------------------
TO_CUSTOMER('Jeffrey Lebowski', '01-01-1964 00:00:00', TA_BANKACCOUNTS(TO_BANK
ACCOUNT(123456789, 10), TO_BANKACCOUNT(987654321, 100)))


1 row selected.

rwijk@ORA11GR1> select type_name
  2       , typecode
  3       , attributes
  4    from user_types
  5  /

TYPE_NAME                      TYPECODE                       ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT                 OBJECT                                  2
TA_BANKACCOUNTS                COLLECTION                              0
TO_CUSTOMER                    OBJECT                                  3
SYSTPuS41SrC7Q9G6/OfQT4VHPA==  COLLECTION                              0

4 rows selected.

But as you can see, when you use the function, an internal type has been persistently created. This is the internal type that is used to store the result of the COLLECT-function in:

rwijk@ORA11GR1> select collect(to_bankaccount(nr,current_balance))
  2    from bankaccounts
  3  /

COLLECT(TO_BANKACCOUNT(NR,CURRENT_BALANCE))(NR, CURRENT_BALANCE)
------------------------------------------------------------------------------
SYSTPuS41SrC7Q9G6/OfQT4VHPA==(TO_BANKACCOUNT(123456789, 10), TO_BANKACCOUNT(98
7654321, 100), TO_BANKACCOUNT(234567890, 2000))


1 row selected.

Note that the name of the type is the same as listed earlier.

If you don't query the user_types view as I did, then you might be surprised that the following sequence doesn't work:

rwijk@ORA11GR1> drop type to_customer
  2  /

Type dropped.

rwijk@ORA11GR1> drop type ta_bankaccounts
  2  /

Type dropped.

rwijk@ORA11GR1> drop type to_bankaccount
  2  /
drop type to_bankaccount
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

So I have to drop the internal type first, before I can drop type TO_BANKACCOUNT:

rwijk@ORA11GR1> column type_name new_value systype
rwijk@ORA11GR1> select type_name
  2    from user_types
  3   where type_name like 'SYS%'
  4  /

TYPE_NAME
------------------------------
SYSTPuS41SrC7Q9G6/OfQT4VHPA==

1 row selected.

rwijk@ORA11GR1> drop type "&systype"
  2  /
old   1: drop type "&systype"
new   1: drop type "SYSTPuS41SrC7Q9G6/OfQT4VHPA=="

Type dropped.

rwijk@ORA11GR1> drop type to_bankaccount
  2  /

Type dropped.

This behaviour annoyed me slightly, so I checked out another way using CAST-MULTISET:

rwijk@ORA11GR1> create type to_bankaccount is object
  2  ( nr              number(10)
  3  , current_balance number(14,2)
  4  );
  5  /

Type created.

rwijk@ORA11GR1> create type ta_bankaccounts is table of to_bankaccount;
  2  /

Type created.

rwijk@ORA11GR1> create type to_customer is object
  2  ( name         varchar2(30)
  3  , birthdate    date
  4  , bankaccounts ta_bankaccounts
  5  );
  6  /

Type created.

rwijk@ORA11GR1> create function customer_with_multiset
  2  ( p_customer_id in customers.id%type
  3  ) return to_customer
  4  is
  5    o_customer to_customer;
  6  begin
  7    select to_customer
  8           ( c.name
  9           , c.birthdate
 10           , cast
 11             ( multiset
 12               ( select to_bankaccount(ba.nr,ba.current_balance)
 13                   from bankaccounts ba
 14                  where ba.customer_id = c.id
 15               )
 16               as ta_bankaccounts
 17             )
 18           )
 19      into o_customer
 20      from customers c
 21     where c.id = p_customer_id
 22    ;
 23    return o_customer;
 24  end customer_with_multiset;
 25  /

Function created.

And with CAST-MULTISET, there are no additional internal types created:

rwijk@ORA11GR1> select customer_with_multiset(1)
  2    from dual
  3  /

CUSTOMER_WITH_MULTISET(1)(NAME, BIRTHDATE, BANKACCOUNTS(NR, CURRENT_BALANCE))
------------------------------------------------------------------------------
TO_CUSTOMER('Jeffrey Lebowski', '01-01-1964 00:00:00', TA_BANKACCOUNTS(TO_BANK
ACCOUNT(123456789, 10), TO_BANKACCOUNT(987654321, 100)))


1 row selected.

rwijk@ORA11GR1> select type_name
  2       , typecode
  3       , attributes
  4    from user_types
  5  /

TYPE_NAME                      TYPECODE                       ATTRIBUTES
------------------------------ ------------------------------ ----------
TA_BANKACCOUNTS                COLLECTION                              0
TO_BANKACCOUNT                 OBJECT                                  2
TO_CUSTOMER                    OBJECT                                  3

3 rows selected.

So that's 0-1 in favour of CAST-MULTISET. Let's check performance:

rwijk@ORA11GR1> declare
  2    o_customer to_customer;
  3  begin
  4    runstats_pkg.rs_start;
  5    for i in 1..10000
  6    loop
  7      o_customer := customer_with_collect(1);
  8    end loop;
  9    runstats_pkg.rs_middle;
 10    for i in 1..10000
 11    loop
 12      o_customer := customer_with_multiset(1);
 13    end loop;
 14    runstats_pkg.rs_stop(1000);
 15  end;
 16  /
Run1 draaide in 171 hsecs
Run2 draaide in 130 hsecs
Run1 draaide in 131.54% van de tijd

Naam                                              Run1        Run2    Verschil
STAT.undo change vector size                    10,264       2,844      -7,420
LATCH.shared pool                               10,236           8     -10,228
STAT.buffer is pinned count                          0      20,000      20,000
STAT.redo size                                  30,784       3,780     -27,004

Run1 latches totaal versus run2 -- verschil en percentage
Run1      Run2  Verschil     Pct
183,334   170,551   -12,783 107.50%

PL/SQL procedure successfully completed.

That's 0-2.