Notes on Oracle

Comparing Schemas: Red Gate Schema Compare for Oracle

2012-01-26T16:17:00.000+01:00

This week I attended a webinar by Cary Millsap, organized by Red Gate. It was called "Real Developers DO use Tools" and was focussed on, well, tools. At the end of Cary's talk, James Murtagh showed a demo of their Schema Compare tool. The demo wasn't flawless, but he recovered nicely :) (note to self: don't do live demo's). And this - the demo, not the occuring error - triggered a memory that I wanted to write a blog on the Schema Compare tool.
If you missed the webinar you can find it on the website of Red Gate here: http://www.red-gate.com/products/oracle-development/deployment-suite-for-oracle/webinars/webinar-archive
I had the pleasure of meeting James Murtagh during the UKOUG annual conference at the end of last year. James also hosts the All Things Oracle site. While we were discussing different topics (even non-oracle) he asked me which tool is my "development tool of choice". The tool I use predominantly is PL/SQL Developer by Allround Automations. "We have a plugin for that",... time to take a closer look at the tool.
First the installation of Schema Compare, simply following the wizard and you're done in a jiffy.

Even though I found out later, if you're on at least PL/SQL Developer 9.0.1, that it is not necessary to download the product separately. This can also be done from within the plugin for PL/SQL Developer. When you're on an older version of PL/SQL Developer, as I am, you need to install the plugin yourself.
Next the plugin, which can be found on the website of Allround Automations in the plugin listing.

Installing a plugin in PL/SQL Developer is always very easy, just double click the executable and you're set.
The big question with plugins in PL/SQL Developer is: "How can you access the plugin?" In this case it is very simple, not hidden in some of the other menu's, but available in the main toolbar.

Time to try out the Schema Compare from my PL/SQL Developer environment. It start the tool automatically, and prompt you for the database connections. Unfortunately it doesn't use the same "connection list" as PL/SQL Developer, so you will have to enter the connection information yourself. At least for me it didn't work as I hoped, I checked with James Murtagh and showed me that it actually does work. Maybe a PL/SQL Developer version specific thingy (I use version 8).
Fortunate for me, the plugin now support Oracle Instant Client (thanks Tom).

Just fill in the Source and the Target database connection information, pick the schemas that you want to compare and push the "Compare Schema" button.
Next is a progress window shown... The following screenshot took me several takes to get, the comparison is done really fast. Of course this was done on my test database with only several hundreds of objects in the user schemas, but even on our production system the speed was amazing.... The following screenshot took me several takes to get, maybe I'm just too slow.

When the comparison is done, you get an overview of all the differences that exist between the two schemas as shown in the next screenshot.

Double clicking on the big blue arrow will reverse the comparison, which can come in very handy if you reverse the Source and Target databases, right, James?
Now you can inspect all differences, and decide which differences you want to deploy to the Target schema. Just check the boxes of the objects you want to deploy.
After you make your selection, start up the Deployment Wizard (top of the screen, which is visible in the screenshot. Resizing the window apparently moves the button to the second line of buttons.).
In three steps you can deploy what you have selected.
In step one, you decide the deployment method - I choose a deployment script. What is kind of odd that the default "Open the script in " option is set to Oracle SQL Developer, even though I started the program from PL/SQL Developer. Can be changed easily, odd none the less.

The second step analyses the dependencies for you. In my example I choose to deploy a few sequences, which apparently have a dependency in some triggers, packages and even a view.

The last step is an overview of the steps that are going to be taken, like dropping object, creating or recreating objects. Always a good idea to review, you can never be too careful.

Maybe it's a language thing - I'm not a native English speaker - but in step two of the deployment wizard, where it shows the referenced objects, there is a check box with the label "Deploy all affected objects (Recommended)". My assumption was that the objects listed, like the packages, triggers and the view were going to be deployed to the target database. As often with assumptions, I was wrong. The only thing regarding these objects in the deployment script is "Drop the object", which seems odd to me. Deploying sequences can - at best - invalidate some objects, no reason to drop the affected objects.

Overall Impression

I like the way it works, right from my development tool I can start it up. Would be nice if I could use my existing database connections (all listed in PL/SQL Developer) but that's only a minor detail. But this should work and when I upgrade to a newer version of PL/SQL Developer it should be gone. The connections can be saved in Schema Compare as well.
The differences between the two schemas are mapped out in a very orderly fashion. Differences of objects which exist in both the source as the target schema, objects that exist in either the source or the target, and there is even a section of objects which are identical in both schemas. This list is very clear, easy to navigate through and the differences per object are highlighted so you can easily see the differences.
Personally I would also opt to have a script which I can review and keep in version control, instead of having it deploy automatically. Maybe I'm just paranoid, but that's the way I would do it. Of course you can also do an automatic deployment, followed by another comparison, and deploy again, continuing until there are no more differences left, but this wouldn't be my choice.
This doesn't mean you can't review the script before you run it, you can. And I strongly suggest you do this.
What really impressed me was the speed of the comparison. Comparing two schemas is done really quickly, even with a couple of medium sized production schemas.

Upgrade Oracle XE from 10 to 11: A word of caution

2012-01-11T17:58:00.000+01:00

Finally I had some time to upgrade my Oracle XE database from version 10 to version 11, something I wanted to try out but could never find the time to do it. I wanted to try it out, to see if it all worked as advertised, usually it does. This time however I ran into some problems with the export and import of the APEX applications, good thing it was just on my sandbox database.The first step in the documentation (link at the bottom of this blogpost) is to log in as SYS and run the gen_inst.sql script which comes bundled with the Oracle XE11 database.

Running the gen_inst.sql script creates a number of scripts in the directory, each being an APEX application.

Now the thing to be cautious about: check the generated application install scripts.. or read the rest of this blogpost before running the gen_inst.sql script.
I found out the hard way that they don't always work as expected. When following along with the "regular upgrade steps" (export the data, uninstall the Oracle XE10 database, install the Oracle XE11 database, import the data), the final step is to install all the applications into the newly created database. When doing so, the process stopped...

As you can see in the image above, the script halted at line 19 waiting for input... entering a slash (/) resulted in the exception shown, any other input would have resulted in a similar exception. Upon examination of the application scripts, I discovered this:

The gen_inst.sql script didn't generate the complete application script, it ran out of the DBMS_OUTPUT buffer resulting in a ORU-10027 exception (buffer overflow).
The cause of this exception is the following code in the gen_inst.sql script


set serveroutput on size 1000000

This instruction limits the output buffer to 1000000 bytes of data.Ever since Oracle 10g Release 2, it is possible to do this


set serveroutput on size unlimited

Changing this line in the gen_inst.sql will generate correct application files. Note that there are multiple place in the script where this command is issued (two if I'm not mistaken).
And just to be sure that this command also works in Oracle Express Edition 10g:

Finally, another problem I ran into, if you use the APEXLIB and you created a separate schema with a name like "APEXLIB", this schema is not exported with the EXPDP command. When I exported my APEXLIB schema and imported it into the target database a lot of views, packages and triggers stayed invalid. I decided to download a new version of APEXLIB and install that in the newly created database, and all was fine.Documentation links:

APEX: Friendlier exception message from AJAX call

2012-01-03T16:43:00.000+01:00

While it is very easy to create a "Form and a Report" on a single table (or view), just follow the wizard, for end users it is not always intuitive that they should navigate to the form page to remove the record. A nicer solution is the one described by Anthony Rayner in this demo page. This solution uses a number of -very simple- dynamic actions to include a little trashcan on the report page.

In this blogpost I will not describe how to create the trashcan functionality in the report, this is already done by Anthony, but about how to handle this:

You might not be scared by this message, but your users might be. Probably most of them have no idea what is meant by

AJAX call returned server error ORA-02292: integrity constraint (NUIJTEN.SYS_C006303602) violated - child record found for Execute PL/SQL Code.

This exception occurs when you create the PL/SQL Code to delete the department like this

Instead of handling the exception in the application, I prefer to have actions done in stored procedures - preferably packages. So to remove a department I will write a procedure like this:

Here is the code so you don't have to type it in, but can copy and paste it.


create or replace procedure remove_dept (p_deptno IN NUMBER)
is
   e_constraint exception;
   pragma exception_init (e_constraint, -2292);
begin
   delete from dept d
    where d.deptno = p_deptno;
exception
   when e_constraint
   then
      htp.p ('{"error":"Department has Employees"}');
end remove_dept;

The key thing in the code is the use of HTP in the exception handler. This will return a JSON object with a user friendly error message.Just replace the original delete statement with a call to the procedure:

and when the user tries to remove a department which still has employees, he will see a friendlier message

You can try out an example on my demo pages.

Generate multiple rows

2012-01-02T15:40:00.001+01:00

For the first post of the year, one of my New Year's resolutions is to write more posts than last year which shouldn't be too hard, I wanted to collect some different ways of generating multiple records. Sometimes I need, for whatever reason, generate multiple records. These are a few different ways of doing so.This is not really "generating multiple rows", but just a simple way of having multiple lines of output. Just take a big table and limit the number of rows by using "where rownum <= ":


SQL> select 'Happy New Year' msg
  2    from all_objects
  3   where rownum <= 10
  4  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>

One of my favorite ways is to use the "Connect by level" trick:


SQL> select 'Happy New Year' msg
  2    from dual
  3  connect by level <= 10
  4  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>

Another way is to use the MODEL clause (Oracle 10g). I still find the MODEL clause quite hard to read and understand.


SQL> select msg
  2    from dual
  3    model
  4   dimension by (0 d)
  5   measures (cast ('m' as varchar2(30)) msg)
  6   rules iterate (10)
  7     (msg [iteration_number] = 'Happy New Year')
  8  /

MSG
------------------------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>

And lastly the Recursive Subquery Factoring way (Oracle 11g)


SQL> with lots(r)
  2  as
  3  (select 1 r from dual
  4    union all
  5   select r+1 from lots
  6   where r < 10
  7  )
  8  select 'Happy New Year' msg
  9    from lots
 10  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

I know there are more ways of generating multiple rows as output, this is not meant as a complete list of all possibilities. This post was meant to sent a subliminal message to wish you a Happy New Year!

Looking back at UKOUG 2011

2011-12-11T11:49:00.001+01:00

Now that the UKOUG annual conference in Birmingham is over, it's time to write my thoughts down. As this was the second time that I attended the UKOUG conference, I already knew that it is a big conference. Lots of great speakers and a very good agenda. On the agenda were very interesting session, sometimes making it very hard to choose which session to go to. Guess you can't complain about that. On the Sunday before the actual conference starts, the OakTable organized a special day. Originally I didn't plan to attending this day, as I was flying in in the afternoon. Turned out, I was on time to attend Connor McDonald's session. Lucky me, Connor is an awesome presenter, so I decided to stay for the rest of the day. Very enjoyable, they even had OakTable beer before the final OakTable panel session.
In the evening there was the Ace dinner, in a nearby Thai restaurant. Good company, good food. A little nerdy perhaps, but as it turned out there were 42 Ace's and Ace Directors attending.
On Monday I attended some really good sessions, like Kyle Hailey on SQL Tuning, Bryn Llewellyn on Using the PL/SQL Hierarchical Performance Profiler, and Connor McDonald on Partitioning 101.
Late in the evening Cary Millsap did a keynote session: Learning about Life through Business and Software. Only one word for this session: Wow! The things he talked about are still going around in my mind.
Tuesday morning, right after Roel Hartman's session Done in 60 seconds, was my own session Who's afraid of Analytic Functions? - for which I was quite nervous. Got some nice feedback on the session, so was quite pleased with it. Of course I had to go and see Carl Dudley's session on Analysing Your Data with Analytic Functions - check out the competition, so to speak. Michael Salt's session on Indexing: It's All In The Index was also very interesting.
Wednesday was time to head back home, so packed up my bags and went to see Jonathan Lewis' session on Redo. Can you keep an audience captivated for an hour talking about Redo? Jonathan Lewis can. Sadly this was my last session.
Because I arrived early at the Birmingham International Airport, I could get an earlier -originally delayed- flight back to The Netherlands. Now I can look back at a wonderful conference....
Oh,... did I mention I received the Inspiring Presentation Award for my session I did last year?Update: Marco told me that Connor McDonald has a new Blog.

APEX: Make a report row clickable in Report

2011-11-29T10:54:00.001+01:00

When you create a "Report with Form", there will be an icon in the report which allows you to navigate to the form page. Only when the user clicks the icon this navigation will take place. For the current project, this was not what they wanted. They wanted to click on the row instead of just the icon. This can be simply implemented using jQuery.
For this example we are going to use a "Report with Form" and modify it as described below.
All of our pages in the application have a page alias, the Report page alias is "EMP001" and the Form page has an alias "EMP002".
The default behaviour will appear in the page like the below screenshot, the cursor will turn into a small hand only when you move your mouse over the icon.

But we want to achieve the following: moving the cursor over the whole row changes the cursor to a small hand indicating that the whole row is clickable. And not only that, also that the whole row is in fact clickable.

To achieve this we would need to "move" (or copy) the anchor to row-level.

When you look at the generated HTML code, it will look something similar to this:

The highlighted line in the picture is the icon which is shown in the Report page (alias: EMP001). As you can see in the image, the anchor (the "a" tag) has an href attribute which contains all the information it needs to navigate to the Form page (alias: EMP002). Even the page alias is in there, and this is what we will use to select the correct href.
Because the page alias is in the anchor, we can use the jQuery selector to get the appropriate element

$('a[href*="EMP002"]')

This will result in an array of anchor elements which we want to manipulate. For each of the elements in the array we want to retrieve the href attribute. The href attribute, we will keep in a local variable named "lnk". Now the code will look like:

$('a[href*="EMP002"]').each(function(index) { 
    lnk = $(this).attr('href');

});

This variable with the href attribute is going to be place at row level as a "data-href". From the current element we need to move up to the row level (tr) and add the attribute.

   $(this).parent()
          .parent('tr')
    .attr('data-href', lnk)

To make it clickable, we also need to add a click event to the row. This is where jQuery really shows its power, we can "chain" the click event to our selector. The code will grow slightly to the following:

 $(this).parent()
          .parent('tr')
    .attr('data-href', lnk)
    .click(function(){
      window.location=$(this).attr('data-href');
    })

To give the user the feedback that the row is clickable, we need to change the cursor to a little hand. Again we chain the mouseover event to the selector

$(this).parent()
          .parent('tr')
    .attr('data-href', lnk)
    .click(function(){
      window.location=$(this).attr('data-href');
    })
    .mouseover(function(){
      $(this).css('cursor', 'pointer');
    })

And because we also want to change the cursor back to default when the user move the cursor away from the rows in the report, the mouseleave event is also required

   $(this).parent()
          .parent('tr')
    .attr('data-href', lnk)
    .click(function(){
      window.location=$(this).attr('data-href');
    })
    .mouseover(function(){
      $(this).css('cursor', 'pointer');
    })
    .mouseleave(function(){
      $(this).css('cursor', 'default');
    })

Now that the jQuery code is complete, we can add this to the page

Double click at page level to open the page properties, and paste the jQuery code in the section labelled "Execute when Page Loads" and we are all set.

When you run the page you will notice that the whole row is clickable.
Inspecting the HTML after we add all the jQuery code will show that there is an anchor at the row level now, just what we wanted.

To make copy the code for your own use, the completed jQuery code will look like this

$('a[href*="EMP002"]').each(function(index) {
   lnk = $(this).attr('href');
   $(this).parent()
          .parent('tr')
    .attr('data-href', lnk)
    .click(function(){
      window.location=$(this).attr('data-href');
    })
    .mouseover(function(){
      $(this).css('cursor', 'pointer');
    })
    .mouseleave(function(){
      $(this).css('cursor', 'default');
    })
});

And to see this in action you can find a demo on apex.oracle.com

Upgrade to APEX 4.1: Invalid Login Credentials

2011-11-07T17:22:00.002+01:00

Always a good idea to upgrade on a Friday.. not... In the past I did numerous upgrades to the latest APEX release, and never really encountered big problems. It was always very smooth, kudos to the APEX-team. If something went wrong it was mostly my own fault, which I will admit now. Because of the history of going very smoothly, I dare to do an upgrade on Friday morning.And it did go smoothly, the upgrade to APEX 4.1 ... until...
Until I started up the application, then I got this "Invalid Login Credentials:

This had me scratching my head for a while.... For this application we are using a Custom Authentication and it turns out that we ran into bug number: 13045147. At the end of the day, I found a way that worked for me to get around this bug.
Before you start this, I strongly advise you to backup your application and everything else your fond of. This worked for me, hopefully it works for you.
Wait!.... There is a Patch for this bug:

... with a release date of November 7th,... coincidence?... ;)

Step 1. Copy your original Login Page (very handy to keep around in case you need to copy regions over)
This can easily be done if you navigate to your Login page, and choose Create > New Page as Copy

Copy it to you own application

Leave all the defaults in place (or at least that is what I did)

Step 2: Now you can remove your Login page.

When you remove your Login page, you will get a warning:

Choose "Permanently Delete Page".... did I already tell you to make a backup of your application before you start following these steps? This might be your last chance to do so.
Step 3: New authentication and new Login Page
Navigate to Shared Components

and choose "Authentication Schemes"

Create a new Authentication Scheme, based on a pre-configured scheme from the gallery

Choose Custom from the select list

Then you will see two additional regions on the page, where you specify the functions and procedures you want to use. Currently this application only uses a custom (table based) authentication, so I fill in the packaged function which I use

The signature of the function should look like this

   function validate (p_username in varchar2
                     ,p_password in varchar2
                     )
      return boolean;

When you complete this step, not only is the new Authentication Scheme created, but it is also the active one. And as a bonus, you will get a new Login page as well.

You can see it is the active Authentication Scheme, as it is labelled as "Current" in the Authentication Overview

After all these steps the authentication works as before, just restore (copy) the extra regions from the original Login page to the new Login page and we're all set.

Using jQuery to determine the checked items

2011-10-26T12:08:00.001+02:00

In one of my APEX pages, I needed to know in javascript which checkboxes are checked. The checked values are placed in an item as a semi-colon (;) delimited list. To be able to do this I created a Dynamic Action which was quite easy.. what wasn't that easy (at least for me, and that's why I want to keep it here so the next time I need this) was to write the javascript expression to get the values.jQuery is simply amazing. Using a "simple" expression it is "easy" to get exactly what was needed.


$('input:checkbox:checked').map(function() {
  return $(this).val();
}).get().join(';');

And the complete Dynamic Action to see the settings that I used:

Probably there is an even easier way to get the same results, I'd be more than interested to learn that...

Oracle Open World 2011: Oracle Database 11g Features for Developers by Connor McDonald

2011-10-04T19:11:00.000+02:00

Yesterday I had the pleasure of attending a session by Connor McDonald. I heard a lot about him, his presentation style, and I even did a book review back in 2005. Everybody I talk to who attended a session by Connor - ever - is always very positive on his presentation style. And it is very impressive, humor, technical knowledge, all the ingredients are there to keep you focused on the content. The opening slide, he put on as you walked in the room invited you to move forward as the presentation has code sample in a fontsize which he showed on the slide. The room filled up, and it was a large room. There was another benefit of being in the front of the room, apart from being able to read the slides, he handed out chocolates - starting from the front. Talking to Connor later in the evening, he told me it takes months to prepare for a presentation, and it really shows. If you get a chance to see Connor do a presentation, attend it and make sure to arrive early!Part of the presentation was on Edition Based Redefinition, and because I presented on that subject before I believed there was a flaw in his presentation. But there was not, I was mistaken. The rest of this blogpost shows you where I was mistaken, and Connor was absolutely right.When creating an editioning view, I believed it was "required" to name the columns in your view instead of using the wildcard "*" to select the columns. Shielding the editioning view from table alterations. So let's start with an edition enabled user to setup our table.


SQL> conn alex/alex@xe11
Connected.
SQL> alter user alex enable editions
  2  /

User altered.

SQL> select *
  2    from v$version
  3  /

BANNER
---------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

5 rows selected.

SQL> create table "_EMP"
  2  (empno number
  3  ,ename varchar2(20)
  4  )
  5  /

Table created.

As you can see in the above code snippet, I run this test on Oracle Database 11g Express Edition. The tablename I picked up from Connor's session. Naming your table something freakish will (hopefully) stop developers from using the table directly in their code. I like that naming convention. Let's continue with two editions


SQL> create edition r1 as child of ora$base
  2  /

Edition created.

SQL>
SQL> create edition r2 as child of r1
  2  /

Edition created.

In each of the editions we will create an editioning view using the wildcard "*" to select the columns


SQL> alter session set edition = r1
  2  /

Session altered.

SQL> create or replace
  2  editioning view emp
  3  as
  4  select *
  5    from "_EMP"
  6  /

View created.

SQL>
SQL> alter session set edition = r2
  2  /

Session altered.

SQL> create or replace
  2  editioning view emp
  3  as
  4  select *
  5    from "_EMP"
  6  /

View created.

SQL>

I believed, wrongly, that the wildcard "*" would be dynamic, meaning that if the table definition would change both editioning view would include the new column as well. This is not the case.The editioning views don't even get invalidated...



SQL> alter session set edition = ora$base
  2  /

Session altered.

SQL> alter table "_EMP"
  2  add something varchar2(10)
  3  /

Table altered.

SQL> select owner
  2       , object_name
  3      , status
  4    from all_objects_ae
  5   where object_name = 'EMP'
  6  /

OWNER                          OBJECT_NAME                    STATUS
------------------------------ ------------------------------ -------
ALEX                           EMP                            VALID
ALEX                           EMP                            VALID

2 rows selected.

Even though they don't get invalidated, the definition of the editioning views surely must be changed then....


SQL>
SQL> alter session set edition = r1
  2  /

Session altered.

SQL> desc emp
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ----------------------

 EMPNO                                                                      NUMBER
 ENAME                                                                      VARCHAR2(20)

SQL>
SQL> alter session set edition =  r2
  2  /

Session altered.

SQL> desc emp
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ----------------------

 EMPNO                                                                      NUMBER
 ENAME                                                                      VARCHAR2(20)

SQL>

No. The definition includes the columns which existed before we altered the table.What is stored in the metadata, you may wonder. Let's go and find out:


SQL> alter session set edition = r1
  2  /

Session altered.

SQL> set long 50000
SQL> select dbms_metadata.get_ddl ('VIEW'
  2                               ,'EMP'
  3                                                      )
  4    from dual
  5  /

DBMS_METADATA.GET_DDL('VIEW','EMP')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONING VIEW "ALEX"."EMP" ("EMPNO", "ENAME") AS
  select "EMPNO","ENAME"
  from "_EMP"


1 row selected.

SQL>
SQL> alter session set edition =  r2
  2  /

Session altered.

SQL> select dbms_metadata.get_ddl ('VIEW'
  2                               ,'EMP'
  3                                                      )
  4    from dual
  5  /

DBMS_METADATA.GET_DDL('VIEW','EMP')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONING VIEW "ALEX"."EMP" ("EMPNO", "ENAME") AS
  select "EMPNO","ENAME"
  from "_EMP"


1 row selected.

As you can see in the output above, the actual column names are in the metadata, shielding changes to the editioning views when the table definition changes.Connor was absolutely right about the mechanics of Edition Based Redefinition. Does this mean you should use the wildcard "*"in your editioning views? I believe not, I believe you should explicitly name the columns that you want to include in a specific edition. This way it is more clear to the developers, and DBA alike. Rerunning the scripts might all of sudden include columns in an edition which weren't suppose to be there. When you explicitly name your columns, you won't have this problem.Just the last bit, cleaning up my environment:



SQL> alter session set edition = ora$base
  2  /

Session altered.

SQL> drop edition r2 cascade
  2  /

Edition dropped.

SQL> drop edition r1 cascade
  2  /

Edition dropped.

SQL> drop table "_EMP" purge
  2  /

Table dropped.

Oracle Open World 2011; Unstructured Data and Multimedia SIG

2011-10-02T21:15:00.000+02:00

Oracle Open World is underway. My first session, which I didn't plan, was on Unstructured Data. There is a lot of unstructured data going around, as opposed to relational data, and it will only increase in the future. The number of images and videos will only increase in the future.Especially for this, Marcelle Kratochvil is putting together an Oracle Unstructured Data with Multimedia SIG, just to spread the word on unstructured data and multimedia. She even put up a website (to be moved in the future) with white papers, preseentations, and links to other useful information. This site can be found at https://sites.google.com/site/ommuds/home. Head over there and check it out.She also showed some very nice examples of customers using multimedia. Unfortunately I had to leave at this time. Even though I didn't plan on attending this session, it was very interesting - too bad I had to leave for a different session, but I will surely be investigating more on unstructured data.

Where am I, what am I doing.. Oracle Open World 2011

2011-10-02T01:00:00.000+02:00

This post is more for me than it is for you, at least I will have a place to quickly check where am I supposed to be going next.The rest of this post is basically just my schedule for the upcoming week in San Francisco.Really looking forward to all the sessions, meeting old friends and making new ones. So if you see me walking around, please come up and say hello.

Quick: How long is this string?

2011-09-09T14:41:00.000+02:00

Riddle me this: how long is the following string:


to_char (10, '0999')

Did you answer four? Then, sorry to say, you are wrong. The correct answer is five.


SQL> select '['||to_char (10, '0999') ||']'
  2    from dual
  3  /

'['||TO
-------
[ 0010]

As you can see, there is a space at the beginning of the string. This space is reserved for the sign (either a plus "+" or a minus "-" ).If you don't want this in your output, you can do something like this


SQL> select '['||to_char (10, 'fm0999') ||']'
  2    from dual
  3  /

'['||TO
-------
[0010]

And as you can see, the space is no longer there.What if you do want the sign to appear in the output? Use this format mask


SQL> select '['||to_char (10, 's0999') ||']'
  2    from dual
  3 /

'['||TO
-------
[+0010]

Link

Oracle Documentation on Format Models

Oracle Open World / Java One Preview bij AMIS

2011-09-08T21:22:00.000+02:00

Op maandag 19 september zal er bij AMIS een Oracle Open World Preview sessie worden gehouden. Een groot deel van de Nederlanders die het dit jaar gelukt is om aanwezig te zijn als spreker op Oracle Open World zullen hun presentaties geven in Nieuwegein. AMIS-ers Lucas Jellema, Alex Nuijten, Peter Ebell en Aino Andriessen zullen in drie parallel sessies presentaties verzorgen. Daarnaast zullen andere Nederlandse presentatoren - zie hieronder de lijst met sprekers - presentaties verzorgen.Heb je dit jaar niet de kans om in San Fransisco aanwezig te zijn, meld je dan aan voor deze preview sessie Oracle Open World 2011!

	Zaal 1	Zaal 2	Zaal 3
17:00 - 18:00	Lucas Jellema	Aino Andriessen	Alex Nuijten
18:00 - 19:00	Diner	Diner	Diner
19:00 - 20:00	Jacco Landlust	Kees Jan Koster	Peter Ebell
20:00 - 21:00	Lonneke Dikmans	Kees Jan Koster/ Jeroen Borgers

Lucas Jellema - Instant Agility in Oracle Fusion Middleware Through Design Time @ Runtime

This session will discuss quality aspects of an ADF Fusion application. It will demonstrate how to address, manage and measure quality and will show what tools can be used to leverage the development process to create high quality ADF Fusion applications.

Jacco Landlust - Deployment Patterns for Oracle Fusion Middleware 11g

Oracle Fusion Middleware 11g is a comprehensive software suite that can be installed in a wide range of different topologies. In this session, an Oracle ACE Director (Simon Haslam) and an Oracle ACE (Jacco H. Landlust) combine their real-world experience in a range of organizations (including supermarkets, government departments, and banks) to explain the decisions you have to make in designing a middleware infrastructure.The presentation covers Oracle WebLogic Server, supporting infrastructure such as Oracle Internet Directory, and layered products such as Oracle SOA Suite. Topics include domain planning, clustering options, handling virtualization, patching strategy, and promoting software from test to production.

Lonneke Dikmans - Approach to SOA: Making This a Successful Endeavor for the Whole Organization

This session starts by explaining key features of SOA. There are several reasons to start with SOA: it can be a strategic choice, it can be started from an IT perspective, or the endeavor can be started from a specific department or business issue. To make the SOA effort successful, it is important to decide where to start. Selecting the right tooling, training, and projects is an important success factor. This session discusses Oracle SOA Suite and Oracle Unified Business Process Management Suite and explains how the suites fit into the roadmap to SOA. At the end of this session, you will know what SOA means, when it is best used, and how you can get there.

Aino Andriessen - Spend some quality time on you ADF Application

A key factor in succesfull (ADF Fusion) application development is good quality management. And although everybody agrees on that, many projects still struggle with the definition of quality let alone with the implementation, measurement and enforcement of a quality process. Hight quality improves the system's stability and maintainability and allows for better and faster development and is thus beneficial both to the owner as wel as the developer.

Kees Jan Koster - Cache Fundamentals, Dangers, and Tuning

This presentation is a tour of the surprisingly large number of caches in a typical Java application and the systems it is part of. You'll take home a practical approach to examining the characteristics of a cache and will be able to compare caching solutions.

Kees Jan Koster en Jeroen Borgers - Java Tuning Puzzlers

This session looks at some strange and bizarre exceptions, error messages, and performance measurements. It discusses each of them and tries to resolve them. You are invited to chip in, offer solutions, ask questions, and learn about some of the more obscure corners of Java.

Peter Ebell - Bridging the gap between SOA and the Database

All too often, they are perceived as two different worlds. There’s the realm of the Oracle SOA Suite, where everything revolves around technologies like XML, Web Services and BPEL. And there’s your databases, containing your company’s data and business logic in tables, views and PL/SQL logic. For SOA to be successful, these two worlds must be able to interact closely. This presentation will provide a thorough overview of the many ways in which the SOA Suite can connect to the database (for instance through the Database Adapter, SDO, EDN or Native DBWS), and vice versa: how services and processes in the SOA Suite can be invoked from within the database (using among others AQ, UTL_HTTP and DBMS_EPG). Illustrated by demos, the possibilities of each of these techniques will be made clear. Also, some common solutions and scenarios will be discusses using real life examples.

Alex Nuijten - Who’s afraid of Analytic Functions?

“I never had the need for Analytic Functions”, is one of the phrases that is heard quite often. Maybe that’s true, maybe it isn’t. Recognizing the need for a particular feature can only occur when you have enough exposure to the feature. In this presentation a number of real life examples will be covered which will show the power of Analytic Functions and the ease of solving the challenge at hand. You can learn the syntax from the Oracle Documentation, but you will learn to recognize “the need for them” by real life examples.Inschrijven kan hier.

Business Rule: Only One per Day, but keep the time

2011-08-24T14:45:00.007+02:00

The business rule states:

Only one entry is allowed per ID and per day and the time should be recorded.

The table involved (simplified for the blog post)


SQL> create table test

  2  (id         number

  3  ,inspection_dt date

  4  );

Wouldn't it be nice if it was possible to do it like this?


SQL> create table test

  2  (id         number

  3  ,inspection_dt date

  4  ,constraint one_per_day unique (id, trunc (inspection_dt))

  5  );

,constraint one_per_day unique (id, trunc (inspection_dt))

                                    *

ERROR at line 4:

ORA-00904: : invalid identifier

This way you still have the complete date information (time is a component of the date column), and only use the TRUNC (inspection_dt) to constrain the data entry.
As you can tell from the error message, this is not allowed.
Oracle 11g Release 1 introduced Virtual Columns which can implement this requirement declaratively.

A Virtual Column is a column which is based on an expression which can be used in e.g. constraints - just what the doctor ordered.


SQL> create table test

  2  (id         number

  3  ,inspection_dt date

  4  ,inspection_day as (trunc (inspection_dt))

  5  ,constraint one_per_day unique (id, inspection_day)

  6  );



Table created.

The virtual column is defined on line 4 in the above CREATE statement.
If the table already exists, you can also add the Virtual Column with an ALTER statement.


SQL> alter table test add (

  2    inspection_date date

  3    generated always

  4    as

  5      (trunc (inspection_dt))

  6    virtual

  7  )

  8  /



Table altered.

To test the requirement, we simply insert date with SYSDATE into the table - wait a little bit (one second) - and insert again with SYSDATE.


SQL> insert into test (id, inspection_dt) values (1, sysdate)

  2  /



1 row created.



SQL> begin

  2     dbms_lock.sleep (1);

  3  end;

  4  /



PL/SQL procedure successfully completed.



SQL> insert into test (id, inspection_dt) values (1, sysdate)

  2  /

insert into test (id, inspection_dt) values (1, sysdate)

*

ERROR at line 1:

ORA-00001: unique constraint (ALEX.ONE_PER_DAY) violated

Why the wait? Because I scripted this test (for the blog) SYSDATE has only second-granularity, so the records could be inserted in the same second. Which would have the same result as a unique constraint on the ID and INSPECTION_DT.

As you can see from the output in the above script, only one record is inserted into the table, the other attempt yields an exception.
Just to prove that the time component of the INSPECTION_DT column is recorded as per requirement:


SQL> select id

  2       , to_char (inspection_dt, 'dd-mm-yyyy hh24:mi:ss')

  3    from test

  4  /



        ID TO_CHAR(INSPECTION_

---------- -------------------

         1 24-08-2011 05:43:54

If you don't have Oracle 11g, there is a way to implement this requirement with a Unique Function Based Index (will require at least Oracle 9i - I ran my scripts on an Oracle 10g Release 2).
We start of with the same table:


SQL> create table test

  2  (id         number

  3  ,inspection_dt date

  4  );



Table created.

And add the Unique Function Based Index on it.


SQL> create unique index one_per_day on test

  2  (id, trunc (inspection_dt))

  3  /



Index created.

The Function Based Index is based on the same expression we used earlier, the TRUNC (inspection_dt). Because this index is created as Unique, it will implement the requirement as stated.
Same testscript as before:


SQL> insert into test values (1, sysdate)

  2  /



1 row created.



SQL> begin

  2     dbms_lock.sleep (1);

  3  end;

  4  /



PL/SQL procedure successfully completed.



SQL> insert into test values (1, sysdate)

  2  /

insert into test values (1, sysdate)

*

ERROR at line 1:

ORA-00001: unique constraint (ALEX.ONE_PER_DAY) violated





SQL> select id

  2       , to_char (inspection_dt, 'dd-mm-yyyy hh24:mi:ss')

  3    from test

  4  /



        ID TO_CHAR(INSPECTION_

---------- -------------------

         1 24-08-2011 05:43:52

Links

Oracle 9i docs
Oracle Base on Virtual Columns

UPDATE

My colleague Martijn Hoekstra just pointed out that Function Based Indexes were introduced in Oracle 8i, not Oracle 9i.
Oracle-Base on Function Based Indexes. Thank you Martijn for this correction.

Splitting a comma delimited string the RegExp way, Part Two

2011-08-08T11:19:00.004+02:00

Over two years ago I wrote about a way to split a comma delimited string using Regular Expresssions. Just a little while ago someone asked how to split it when you have more records involved than just one (as I used in my example).

For this example I use the dataset as AnthonyJ used in the comments. The explanation for the regular expression can be found in the original post.


SQL> with test as
  2  (
  3  select 1 id, 'joey,anthony,marvin' str from dual union all
  4  select 5 id, 'tony,glenn' str from dual union all
  5  select 8 id, 'john' str from dual
  6  )
  7  select id
  8       , str
  9       , regexp_substr (str, '[^,]+', 1, rn) split
 10    from test
 11    cross
 12    join (select rownum rn
 13            from (select max (length (regexp_replace (str, '[^,]+'))) + 1 mx
 14                    from test
 15                 )
 16         connect by level <= mx
 17         )
 18   where regexp_substr (str, '[^,]+', 1, rn) is not null
 19   order by id
 20  ;

        ID STR                 SPLIT
---------- ------------------- -------------------
         1 joey,anthony,marvin joey
         1 joey,anthony,marvin marvin
         1 joey,anthony,marvin anthony
         5 tony,glenn          tony
         5 tony,glenn          glenn
         8 john                john

The trick here is in lines 11 through 17. The Cross Join is there to create multiple records, but no more than the longest intended individual words (three in this case).
Because each ID would result in three records, which is alright for ID 1, it will also create three records for ID 5 and ID 8. Line 18 removes these extra records.
If you use Oracle 11g, you can also use REGEXP_COUNT instead of the combination of REGEXP_REPLACE and LENGTH, which would look like this:


  cross 
  join (select rownum rn 
          from (select max (regexp_count (str, ',') + 1) mx
                  from test
               )
       connect by level <= mx
       )

Regexp_Count

ODTUG KScope 2011: looking back

2011-07-06T20:09:00.005+02:00

Last week the yearly ODTUG KScope conference took place in Long Beach, California. I always need to time to let this wonderful event really sink in. The number of sessions and the wealth of information that you get is really quite extraordinary.
In this blogpost I will highlight some of the sessions that I attended.

Sunday

Sunday is always a symposium day, this year I attended the APEX track. The Oracle Application Express Development team filled us in on what to expect from the next APEX release, 4.1. The most exciting, or at least to me, is the improved exception handling.

Monday

The "real" conference starts with the Opening General Session. This session wasn't in the conference center, but in the Long Beach Performing Arts Center and what an opening it was.... My colleague Marco filmed parts of it, simply hilarious.
After the General Session, I attended Cary Millsap's "My case for Agile Methods". It is always very enjoyable to listen to Cary. One of the other sessions that I look forward to seeing what Patrick Wolf's "Improved Error Handling in Oracle Application Express 4.1". Can't wait for the 4.1 version to be released and use it for real.
In the evening there was the Database Guru Panel, with Cary Millsap, Steven Feuerstein and Tom Kyte. These guys are truly awesome, the knowledge they share is just fabulous. Some very interesting discussions filled the evening.

Tuesday

The first session in the morning was my own, "Who's afraid of Analytic Functions?". Around 60 attended my session and I was very pleased with that.
The Oracle ACE Program organized a very special lunch, a so called "Lunch and Learn" session. In different rooms, different Oracle ACE's and Oracle ACE Directors had panels. Together with Eddie Awad and Steven Feuerstein, I was in the Database Development panel. Sometimes it can be quite a challenge to eat lunch and answer questions at the same time.

Wednesday

Carsten Czarski did a great presentation "It's all about Location: APEX, Spatial, and Maps". Nice demo's and clear explanation.
The other session that I really enjoyed was Eddie Awad's "Five Features you ought to know about the Oracle Scheduler", great examples, great explanation. Great job, Eddie!
Doug Gault's "Creating a Spark Line plugin from Scratch", where he shows how to create a plugin for APEX was a fantastic way to end the day. End the day? Well, not completely...
The Wednesday evening is always reserved for the Grand Event - All aboard the Queen Mary, a magnificent party with fireworks to top it off.

Thursday

The closing session I did attend. What the real shocker was, was that I got the "Best Speaker Award - Database Development Track",.. still can't believe it.

Oracle 11g Express Edition bug?

2011-06-26T15:23:00.007+02:00

Of course you know that Oracle has released 11g Express Edition, still in Beta though. That is probably why I ran into something weird -must be a bug- while playing with it. Here is what I did:


SQL> set echo on

SQL> col "Current_Edition" format a20

SQL> col object_name format a20

SQL> 

SQL> select sys_context('userenv'

  2                    ,'current_edition_name'

  3                    ) "Current_Edition"

  4    from dual

  5  /



Current_Edition                                                                                                         

--------------------                                                                                                    

ORA$BASE                                                                                                                



1 row selected.



SQL> 

SQL> create or replace

  2  procedure hello

  3  is

  4  begin

  5     dbms_output.put_line ('Hello World');

  6  end hello;

  7  /



Procedure created.



SQL> 

SQL> begin

  2     hello;

  3  end;

  4  /

Hello World                                                                                                             



PL/SQL procedure successfully completed.



SQL> 

SQL> select object_name

  2       , object_type

  3       , edition_name

  4    from user_objects_ae

  5   where object_name = 'HELLO'

  6  /



OBJECT_NAME          OBJECT_TYPE         EDITION_NAME                                                                   

-------------------- ------------------- ------------------------------                                                 

HELLO                PROCEDURE                                                                                          



1 row selected.



SQL>

Notice how the Edition_name column is empty...
That is not right, it should show the name of the edition, which it does in Oracle Enterprise Edition as can be seen here.
UPDATE How wrong I was,... of course this is correct; the user wasn't Edition Enabled.
Let's continue and see what happens when you introduce a new edition.


SQL> 

SQL> create edition R1 as child of ora$base

  2  /



Edition created.



SQL> 

SQL> 

SQL> alter session set edition = R1

  2  /



Session altered.



SQL> 

SQL> create or replace

  2  procedure hello

  3  is

  4  begin

  5     dbms_output.put_line ('Hello Universe');

  6  end hello;

  7  /



Procedure created.



SQL> 

SQL> begin

  2     hello;

  3  end;

  4  /

Hello Universe                                                                                                          



PL/SQL procedure successfully completed.



SQL> 

SQL> select object_name

  2       , object_type

  3       , edition_name

  4    from user_objects_ae

  5   where object_name = 'HELLO'

  6  /



OBJECT_NAME          OBJECT_TYPE         EDITION_NAME                                                                   

-------------------- ------------------- ------------------------------                                                 

HELLO                PROCEDURE                                                                                          



1 row selected.

The procedure works as expected, showing the right information but there is something really weird going on.
There should be two entries in the USER_OBJECTS_AE, one for ORA$BASE and one for R1...

What if we were to retire the ORA$BASE edition? Let's try that.


SQL> 

SQL> conn sys/oracle@xe11 as sysdba

Connected.

SQL> 

SQL> ALTER DATABASE DEFAULT EDITION = r1

  2  /



Database altered.



SQL> drop edition ora$base cascade

  2  /



Edition dropped.

And now for the ORA-00600...

After a shutdown (of my VM), I get this message when using DBMS_OUTPUT:


SQL> begin

  2     hello;

  3  end;

  4  /

begin

*

ERROR at line 1:

ORA-22303: type "SYS"."DBMSOUTPUT_LINESARRAY" not found

ORA-00600: internal error code, arguments: [kkaegen_get_edition_name_1], [], [], [], [], [], [], [], [], [], [], []

ORA-06508: PL/SQL: could not find program unit being called: "PUBLIC.DBMS_OUTPUT"

ORA-06512: at "ALEX.HELLO", line 4

ORA-06512: at line 2

By looking at it, I get a feeling that it has something to do with EBR...
UPDATE: After contacting Bryn Llewellyn, PL/SQL product manager, a bug was filled: 12758386.

Links

Retiring Editions
Oracle Database 11.2 Express Edition Beta

ODTUG KScope Preview bij AMIS

2011-05-21T09:38:00.005+02:00

Ook dit jaar, namelijk op dinsdag 14 Juni, organiseert AMIS de ODTUG Preview. Het jaarlijkse congres van de ODTUG, de Oracle Development Tools Users Group, vind dit jaar plaats in Longbeach, California van 26 tot en met 30 juni. Het is niet voor iedereen weggelegd om daar naar toe te gaan. AMIS biedt, alweer voor het vijfde achtereenvolgende jaar, aan geïnteresseerden de kans om een selectie van de presentaties die daar te zien zijn bij te wonen. Een aantal Europese sprekers zal tijdens de AMIS ODTUG preview presentatie laten zien die ook in de Verenigde Staten worden gehouden.
Tijdens de AMIS ODTUG Preview zullen er drie keer drie parallelle sessies worden gehouden met verschillende onderwerpen zoals APEX, database development, ADF, JHeadstart en SOA.

Programma:

Tijd	Track 1	Track 2	Track 3
16:30	Welkom en Registratie
17:00	XFILES, the APEX 4 Version: The Truth is in There... Marco Gralike & Roel Hartman	ADF Developers - Make the Database Work for You Lucas Jellema	Pipelined Table Functions Patrick Barel
18:00	Dinner
19:00	APEX Face/Off - Designing a GUI with APEX Templates and Themes Christian Rokitta	BPMN: The New Silver Bullet? Lonneke Dikmans	Oracle JHeadstart: Superior Productivity in Developing Best-practice ADF Web Applications Steven Davelaar
20:15	Who's Afraid of Analytic Functions? Alex Nuijten	Overview of Eventing in Oracle SOA Suite 11g Ronald van Luttikhuizen	...and Thus Your Forms 'Automagically' Disappeared Luc Bors

Dit evenement is met name bedoeld voor ontwikkelaars.
Uiteraard zijn er aan dit event geen kosten verbonden, maar het aantal plaatsen voor dit evenement is beperkt, wacht niet te lang. Vol is vol.
Inschrijven via www.amis.nl

Planboard DBA Symposium: Registration Opened

2011-04-13T20:16:00.002+02:00

The 6th Planboard DBA Symposium is opened for registration. I'm very pleased to be presenting again at this "For DBA by DBA Symposium". Especially since I'm not really a DBA... :)
Guess the organization committee was pleased with the presentation I did for the fourth Symposium. That time I was scheduled opposite Harald van Breederode, this time it's Frits Hoogland. Hopefully someone will show up for my session... or should I say: "Sorry Frits, too bad nobody showed up at your session". :)
This time I'm doing one of my favorite presentations: "SQL Holmes: The Case of the Missing Performance". Looking forward to it. Hope to meet you there.

Should you expose a Stored Procedure via XMLType?

2010-12-23T14:15:00.000+01:00

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


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

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

One function returns an XMLType (named get_department_xml) and the other returns an Object Type (named get_department_ot). They both return data from the EMPLOYEES and DEPARTEMENTS tables in the HR schema.
The implementation of the package body is based on this blogpost.

With a database adapter in the SOA Suite you can expose each of these functions. To be really useful you will need to add a mediator.

On the right hand side, in the above image, the database adapters are shown. The purple things are the mediators where the actual mapping takes place (XSLT).

The biggest difference between the two implementations can be found in the Mapper file.

As you can see in the image above, the functions returns something. That "something" could basically be anything. Not a whole lot of information.

Using the Object Type as a returntype from the function makes a big difference.

Here you can see the complete structure of the Object Type (which is a quite complex Object Type). Makes Mapping a lot easier.

Creating an XMLType based on Object Types

2010-12-23T13:44:00.001+01:00

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

For this example I will use the HR schema, and in it are the DEPARTMENTS and EMPLOYEES tables, which has a Master-Detail relation between them. The DEPARTMENTS table
has a MANAGER_ID (the manager of the department). The EMPLOYEES table also has a MANAGER_ID (the person who the employee reports to).


SQL> select d.department_name
  2       , d.manager_id
  3       , e.first_name
  4       , e.last_name
  5       , e.manager_id
  6    from departments d
  7    join employees   e
  8      on d.department_id = e.department_id
  9   where d.department_id = 20
 10  /

DEPARTMENT_NAME                MANAGER_ID FIRST_NAME           LAST_NAME                 MANAGER_ID
------------------------------ ---------- -------------------- ------------------------- ----------
Marketing                             201 Michael              Hartstein                     100
Marketing                             201 Pat                  Fay                           201

What I want is an XML structured like this:


<departments>
   <department>
      <departmentname>Marketing
      <department_manager>Steven King
      <employees>
         <employee>
            <name>Michael Hartstein
         </employee>
         <employee>
            <name>Pat Fay
         </employee>
      </employees>
   </department>
</departments>

In order to get that you can write a SQL statement like this one:


SQL> set long 90000 lines 80
SQL> select xmlelement("departments"
  2                   ,(select xmlagg(xmlelement("department"
  3                                             ,xmlelement ("departmentname"
  4                                                         ,d.department_name
  5                                                         )
  6                                             ,xmlelement ("department_manager"
  7                                                         ,(select m.first_name || ' ' ||
  8                                                                  m.last_name
  9                                                             from employees m
 10                                                            where m.employee_id = d.manager_id
 11                                                           )
 12                                                          )
 13                                             ,xmlforest ((select xmlagg(xmlelement("employee"
 14                                                                                  ,e.first_name || ' ' ||
 15                                                                                   e.last_name
 16                                                                                  )
 17                                                                       )
 18                                                            from employees e
 19                                                           where e.department_id = d.department_id
 20                                                         ) as "employees"
 21                                                        )
 22                                             )
 23                                  )
 24                       from departments d
 25                      where d.department_id = 20
 26                    )
 27                   )
 28    from dual
 29  /

XMLELEMENT("DEPARTMENTS",(SELECTXMLAGG(XMLELEMENT("DEPARTMENT",XMLELEMENT("DEPAR
--------------------------------------------------------------------------------
<departments>Marketinganager>Michael HartsteinMichael Hartst
einPat Fay>

But the are probably other ways of getting the same results as well. What you can take away from this statement is that it is quite hard to write and maintain.
Look at all those parentheses, enough to drive you crazy...

There is also the possibility of turning Object Types into XMLType, let's explore this route.
First some Object Types describing our desired XML output


SQL> create type employee_ot as object
  2  (employee_id   number
  3  ,employee_name varchar2 (60)
  4  );
  5  /

Type created.

SQL> create type employees_tt is table of employee_ot
  2  /

Type created.

SQL>
SQL> create type department_ot is object
  2  (departmentname    varchar2(30)
  3  ,departmentmanager employee_ot
  4  ,employees         employees_tt
  5  );
  6  /

Type created.

SQL> create type departments_tt is table of department_ot
  2  /

Type created.

SQL> create type all_departments_ot is object
  2  (departments departments_tt);
  3  /

Type created.

Now we can use these Object Types to construct a query like this


SQL> select department_ot (d.department_name
  2                       ,(select employee_ot (m.employee_id
  3                                           , m.first_name||' '||m.last_name
  4                                           )
  5                           from employees m
  6                          where m.employee_id = d.manager_id
  7                         )
  8                      ,cast ( multiset (select employee_ot (e.employee_id
  9                                    ,e.first_name||' '||e.last_name
 10                                    )
 11                                    from employees e where e.department_id = d.department_id
 12                                    )as employees_tt)
 13                        )
 14    from departments d
 15   where d.department_id = 20
 16  /

DEPARTMENT_OT(D.DEPARTMENT_NAME,(SELECTEMPLOYEE_OT(M.EMPLOYEE_ID,M.FIRST_NAME||'
--------------------------------------------------------------------------------
DEPARTMENT_OT('Marketing', EMPLOYEE_OT(201, 'Michael Hartstein'), EMPLOYEES_TT(E
MPLOYEE_OT(201, 'Michael Hartstein'), EMPLOYEE_OT(202, 'Pat Fay')))

So What, you say?
Well, if you do a describe of the XMLTYPE (like in SQL*Plus), you will see this:


SQL> desc xmltype
...
METHOD
------
 FINAL CONSTRUCTOR FUNCTION XMLTYPE RETURNS SELF AS RESULT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 XMLDATA                        UNDEFINED               IN
 SCHEMA                         VARCHAR2                IN     DEFAULT
 ELEMENT                        VARCHAR2                IN     DEFAULT
 VALIDATED                      NUMBER                  IN     DEFAULT

...

You can use an Object Type as an input to create an XMLTYPE, pretty nifty.
The structure of the Object Types needs to match the structure of the XML.
Just add the XMLTYPE to the query and presto:


SQL> select xmltype (
  2        all_departments_ot (cast (
  3         collect (department_ot (d.department_name
  4                                ,(select employee_ot (m.employee_id
  5                                                     ,m.first_name||' '||m.last_name
  6                                                     )
  7                                    from employees m
  8                                   where m.employee_id = d.manager_id
  9                                 )
 10                                ,cast ( multiset (select employee_ot (e.employee_id
 11                                                                     ,e.first_name||' '||e.last_name
 12                                                                     )
 13                                                    from employees e
 14                                                   where e.department_id = d.department_id
 15                                                 )
 16                                        as employees_tt
 17                                       )
 18                                 )
 19                 ) as departments_tt)))
 20    from departments d
 21   where d.department_id = 20
 22  /

XMLTYPE(ALL_DEPARTMENTS_OT(CAST(COLLECT(DEPARTMENT_OT(D.DEPARTMENT_NAME,(SELECTEMPLOYEE_OT(M.EMPLOYEE_I

-------------------------------------------------------------------------------------------------------

<ALL_DEPARTMENTS_OT>
  <DEPARTMENTS>
    <DEPARTMENT_OT>
      <DEPARTMENTNAME>Marketing
      <DEPARTMENTMANAGER>
        <EMPLOYEE_ID>201
        <EMPLOYEE_NAME>Michael Hartstein
      </DEPARTMENTMANAGER>
      <EMPLOYEES>
        <EMPLOYEE_OT>
          <EMPLOYEE_ID>201
          <EMPLOYEE_NAME>Michael Hartstein
        </EMPLOYEE_OT>
        <EMPLOYEE_OT>
          <EMPLOYEE_ID>202
          <EMPLOYEE_NAME>Pat Fay
        </EMPLOYEE_OT>
      </EMPLOYEES>
    </DEPARTMENT_OT>
  </DEPARTMENTS>
</ALL_DEPARTMENTS_OT>

Using DBMS_UTILITY to aggregate strings

2010-12-20T13:52:00.006+01:00

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

There is a procedure which allows you to change an Associative Array into a comma delimited string. This procedure is called: "TABLE_TO_COMMA".
First the version on which I ran these scripts:


SQL> select *
  2    from v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Now for the proper usage of the TABLE_TO_COMMA procedure:


SQL> declare
  2     names_list dbms_utility.uncl_array;
  3     len  number;
  4     str varchar2(32767);
  5  begin
  6     names_list (1) := 'this';
  7     names_list (2) := 'that';
  8     names_list (3) := 'the other';
  9     dbms_utility.table_to_comma (tab => names_list
 10                                 ,tablen => len
 11                                 ,list => str
 12                                 );
 13     dbms_output.put_line ('The delimited string: '||str);
 14     dbms_output.put_line ('The length of the collection is '||to_char (len));
 15  end;
 16  /
The delimited string: this,that,the other
The length of the collection is 3

PL/SQL procedure successfully completed.

As you can see in the results the collection is converted to a comma separated string. The number of elements in the collection is also shown.

The first thing that popped into mind was the most notable limitation of DBMS_UTILITY.TABLE_TO_COMMA: you can't pass in a collection of numbers.


SQL> declare
  2     names_list dbms_utility.uncl_array;
  3     len  number;
  4     str varchar2(32767);
  5  begin
  6     names_list (1) := 1;
  7     names_list (2) := 2;
  8     names_list (3) := 3;
  9     dbms_utility.table_to_comma (tab => names_list
 10                                 ,tablen => len
 11                                 ,list => str
 12                                 );
 13     dbms_output.put_line ('The delimited string: '||str);
 14     dbms_output.put_line ('The length of the collection is '||to_char (len));
 15  end;
 16  /
The delimited string: 1,2,3
The length of the collection is 3

PL/SQL procedure successfully completed.

Euh,... well, you can now.
The signature of TABLE_TO_COMMA in Oracle 9i was:


DBMS_UTILITY.TABLE_TO_COMMA ( 
   tab    IN  UNCL_ARRAY, 
   tablen OUT BINARY_INTEGER,
   list   OUT VARCHAR2);

The definition of UNCL_ARRAY (Oracle 9i) is:

Lists of "USER"."NAME"."COLUMN"@LINK should be stored here.

And because of this you couldn't pass in numbers. Numbers are not valid identifiers.
apparently things changed, passing in numbers is allowed nowadays.

Oracle 10g introduced this overloading:


DBMS_UTILITY.TABLE_TO_COMMA ( 
   tab    IN  lname_array,
   tablen OUT BINARY_INTEGER,
   list   OUT VARCHAR2);

Since that limitation is gone, should you use TABLE_TO_COMMA to aggregate strings? I don't think you should (but it depends - as always). If you need data from the database using SQL, you might as well stay within the SQL realm and use SQL techniques to accomplish this goal.

There is some other oddities I just discovered playing with TABLE_TO_COMMA. The procedure only works with dense collections starting from 1 (one). Using a sparse collection won't show you the complete collection:


SQL> declare
  2     names_list dbms_utility.uncl_array;
  3     len  number;
  4     str varchar2(32767);
  5  begin
  6     names_list (1) := 'this';
  7     names_list (2) := 'that';
  8     names_list (3) := 'the other';
  9     names_list (42) := 'something';
 10     dbms_utility.table_to_comma (tab => names_list
 11                                 ,tablen => len
 12                                 ,list => str
 13                                 );
 14     dbms_output.put_line ('The delimited string: '||str);
 15     dbms_output.put_line ('The length of the collection is '||to_char (len));
 16  end;
 17  /
The delimited string: this,that,the other
The length of the collection is 3

PL/SQL procedure successfully completed.

Starting at a different value won't show you anything


SQL> declare
  2     names_list dbms_utility.uncl_array;
  3     len  number;
  4     str varchar2(32767);
  5  begin
  6     names_list (11) := 'this';
  7     names_list (12) := 'that';
  8     names_list (13) := 'the other';
  9     names_list (14) := 'something';
 10     dbms_utility.table_to_comma (tab => names_list
 11                                 ,tablen => len
 12                                 ,list => str
 13                                 );
 14     dbms_output.put_line ('The delimited string: '||str);
 15     dbms_output.put_line ('The length of the collection is '||to_char (len));
 16  end;
 17  /
The delimited string:
The length of the collection is 0

PL/SQL procedure successfully completed.

All the entries starting at zero (or lower) won't show up in the output.


SQL> declare
  2     names_list dbms_utility.uncl_array;
  3     len  number;
  4     str varchar2(32767);
  5  begin
  6     names_list (0) := 'what';
  7     names_list (1) := 'this';
  8     names_list (2) := 'that';
  9     names_list (3) := 'the other';
 10     names_list (4) := 'something';
 11     dbms_utility.table_to_comma (tab => names_list
 12                                 ,tablen => len
 13                                 ,list => str
 14                                 );
 15     dbms_output.put_line ('The delimited string: '||str);
 16     dbms_output.put_line ('The length of the collection is '||to_char (len));
 17  end;
 18  /
The delimited string: this,that,the other,something
The length of the collection is 4

PL/SQL procedure successfully completed.

Because of these oddities I wouldn't use DBMS_UTILITY.TABLE_TO_COMMA to create a comma separated string.
Links
Oracle Documentation on LISTAGG
Oracle 9i Documentation on DBMS_UTILITY
Oracle 10g Documentation on DBMS_UTILITY
Oracle 11g Documentation on DBMS_UTILITY
Tim Hall on String Aggregation Techniques

My First UKOUG - day two and three

2010-12-16T09:05:00.004+01:00

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

On the second I arrived late for the sessions, running into the room at 08:45 when the session was starting,... well starting, not really. The speaker, Martin Widlake on "My SQL is suddenly performing badly and nothing has changed", was even later than I was. All in all, the session was not very good. The content was good, but he kept rushing through it. At least he agrees himself.
The next session was on APEX and GIS mapping. I like APEX. I like Spatial. The combination would be great. It was a very generic presentation of what is needed to get everything up and running. Nice session by Marin Huljev and Dalibor Kusic.
Sue Harper showed the new features of SQL Developer Data Modeler. It's always very enjoyable to see Sue present. I've seen her present multiple times, and her demo's sometimes do different things than she expects. That doesn't matter, she always recovers really well. If you want to know about the new features - like Subversion support - and listen to Sue explaining them, follow this link.
Randolf Geist did an excellent session on Advanced Oracle Troubleshooting. It's too much information to cover in a small blog like this one.
Even though Hall 5 was very cold, the two sessions I added there were worth while. Tanel Poder did a session on performance tuning and troubleshooting. A lot of the methods and scripts were also covered by Randolf Geist, but that didn't matter. A very good session.
Chris Roderick had a session there called "Oracle Performance Through Understanding". He works for Cern and they deal with a lot of data. And I really mean a lot!
Therefor getting the design of your application right and optimized for performance is key. He explained the concepts well and gave some pointers.
Edition Based Redefinition is one of the killer features of the Oracle 11g Release 2 database, on which I have presented and written a few times before, and I'm always interested to hear what other people cover when they talk about this feature. Melanie Caffrey had a session on this feature, based on a case study. It still amazes me how wonderful the functionality is...
Last session of UKOUG - lots of people were already leaving home because of the weather conditions (and some returned because of the weather conditions) - that I attended was by Christian Antognini on Transaction Management Internals. Even though I'm a "hardcore" developer I have this fascination with the internal workings of Oracle and I really enjoyed this session. Finally I understand why and how Delayed Block Cleanout works :)

My First UKOUG - day one

2010-12-06T15:24:00.002+01:00

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

The line up of speakers was very impressive (amongst others): Tom Kyte, Cary Millsap, Jonathan Lewis, Graham Wood, John Scott, and the list goes on and on and on...

The first session I went to was by Tom Kyte. Even though I had already seen "What's new in Oracle Database Application Development" during the Oracle Open World, it is still a very good session. My other objective was to check out the room (after lunch on Monday, I had the same room).
Next was "Simplifying your Data Audits with Oracle 11g's Total Recall" by Melanie Caffrey. In this session she described the way she used Total Recall (a.k.a. Flashback Data Archive) for data auditing. After the presentation there was enough room for discussion which was very valuable. Something I forgot, but was reminded of in this session: Total Recall underwent some in Oracle 11g Release 2, like the support for DDL.
Graham Wood did a session on "Features you probably didn't know and are FREE", which he refined to "mostly free". One of the things he mentioned was the "ADVANCED" argument of the Display_Cursor function in the DBMS_XPLAN package. Just a short demo:


SQL> set serveroutput off
SQL>
SQL> var empno number
SQL>
SQL> exec :empno := 7788

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2    from emp
  3   where empno = :empno
  4  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20

1 row selected.

SQL>
SQL> select *
  2    from table (dbms_xplan.display_cursor (null, null, 'ADVANCED'))
  3  /

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

SQL_ID  0nb4mtjxa5k10, child number 0
-------------------------------------
select *   from emp  where empno = :empno

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMP@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :EMPNO (NUMBER): 7788

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=:EMPNO)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
       "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22],
       "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
       "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]


49 rows selected.

Notice all the extra information that you get, very cool.
After Lunch I had my own session on Analytic Functions. It took place in Hall One - one of the largest rooms on site (still very impressed with that). Had some nice feedback, thank you Boneist.
Debra Lilley taped the first two minutes of my session,... nervous: yes
Unfortunately Pete Finnigan's session was cancelled, but that gave me some room to visit the OTN lounge and talk to some people.
That SQL is a weird and wonderful language I already knew, but in the session of Carl Dudley he put all of it together in a single session.
Last show of the day was the Panto...
Later day two and three.

Change SYSDATE for testing

2010-11-03T10:44:00.005+01:00

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

Of course I needed to make sure that it works as SYSDATE changes. Of course I am not refering to the changing of the time component of SYSDATE.
To change SYSDATE you can use this command. Note that it changes SYSDATE for the whole system.


SQL> alter system set fixed_date ='2011-08-05'
  2  /

System altered.

Querying SYSDATE from SQL*Plus will show the effect:


SQL> select sysdate
  2    from dual
  3  /

SYSDATE
---------
05-AUG-11

Also note that SYSDATE is now fixed, not even the time component changes:


SQL> alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss'
  2  /

Session altered.

SQL> select sysdate
  2    from dual
  3  /

SYSDATE
-------------------
05-08-2011 00:00:00

SQL> /

SYSDATE
-------------------
05-08-2011 00:00:00

Refreshing the APEX page also reflects the changed SYSDATE.

To restore the real SYSDATE, issue this command.


SQL> alter system set fixed_date = none
  2  /

System altered.

SQL> select sysdate
  2    from dual
  3  /

SYSDATE
-------------------
03-11-2010 10:53:40

I knew about this functionality, but never thought I would actually need it.
If you want the plugin, you can download it by following this link.

Edition Based Redefinition and USER_OBJECTS_AE

2010-11-01T16:14:00.004+01:00

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

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

Let's first start within the default edition (the one you get when you upgrade to or install an Oracle 11g Release 2 database) called ORA$BASE.


SQL> select sys_context('userenv'
  2                    ,'current_edition_name'
  3                    ) "Current_Edition"
  4    from dual
  5  /

Current_Edition
--------------------
ORA$BASE

Within this edition we will create a procedure called "hello" which simple shows "hello world".


SQL> create or replace
  2  procedure hello
  3  is
  4  begin
  5     dbms_output.put_line ('Hello World');
  6  end hello;
  7  /

Procedure created.

SQL> begin
  2     hello;
  3  end;
  4  /
Hello World

PL/SQL procedure successfully completed.

Querying the USER_OBJECT_AE datadictionary view results in a single record


SQL> select object_name
  2       , object_type
  3       , edition_name
  4    from user_objects_ae
  5   where object_name = 'HELLO'
  6  /

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- -----------------
HELLO                PROCEDURE           ORA$BASE

Introducing a new edition as a child of ORA$BASE, in which we will create a "newer" version of the "hello"-procedure.


SQL> create edition R1 as child of ora$base
  2  /

Edition created.

SQL> alter session set edition = R1
  2  /

Session altered.

When we call the "hello"-procedure in this new edition (named R1), we get the same result as before. The procedure is inherited from the parent edition (named ORA$BASE).


SQL> begin
  2     hello;
  3  end;
  4  /
Hello World

PL/SQL procedure successfully completed.

The datadictionary view USER_OBJECTS_AE shows just a single version of the "hello"-procedure. This more or less proofs that the procedure is not copied over to the new version (named R1). There is still only one version of the procedure.


SQL> select object_name
  2       , object_type
  3       , edition_name
  4    from user_objects_ae
  5   where object_name = 'HELLO'
  6  /

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- ------------
HELLO                PROCEDURE           ORA$BASE

Creating a new version of the "hello"-procedure will -of course- produce a different result when executed.


SQL> create or replace
  2  procedure hello
  3  is
  4  begin
  5     dbms_output.put_line ('Hello Universe');
  6  end hello;
  7  /

Procedure created.

SQL> begin
  2     hello;
  3  end;
  4  /
Hello Universe

This new version of the procedure will override the inherited version of the ORA$BASE edition. This is also reflected in the USER_OBJECTS_AE view:



SQL> select object_name
  2       , object_type
  3       , edition_name
  4    from user_objects_ae
  5   where object_name = 'HELLO'
  6  /

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- ------------------
HELLO                PROCEDURE           ORA$BASE
HELLO                PROCEDURE           R1

Now you can see there are two versions of the procedure named "hello", one in each edition.

Drop the Procedure
When you create a new edition, all editionable objects are inherited into the latest edition. If you don't want an object to "be there" in the latest edition, simply drop it. It won't exist anymore in the latest edition. It will exist in the older edition though.
Instead of creating the newer procedure in the latest edition (named R1), dropping it will also lead to an entry in the USER_OBJECTS_AE view.


SQL> drop procedure hello
  2  /

Procedure dropped.

SQL> select object_name
  2       , object_type
  3       , edition_name
  4    from user_objects_ae
  5   where object_name = 'HELLO'
  6  /

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- -------------
HELLO                PROCEDURE           ORA$BASE
HELLO                NON-EXISTENT        R1

As you can see from the results, the procedure "hello" doesn't exist any more in the latest edition (named R1). The object_type "NON-EXISTENT" gives that away.

Inline View Check Option

2010-10-10T12:01:00.000+02:00

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

It is possible to perform DML operations using a view (some restrictions apply), as shown in the next example.


SQL> create table temp
  2  (x int);

Table created.

SQL> insert into temp values (1);

1 row created.

SQL> insert into temp values (100);

1 row created.

SQL> create view vw_temp
  2  as
  3  select *
  4    from temp
  5   where x <= 10
  6  ;

View created.

SQL> select *
  2    from vw_temp
  3  ;

         X
----------
         1

SQL> insert into vw_temp values (2);

1 row created.

SQL> insert into vw_temp values (102);

1 row created.

SQL> select *
  2    from vw_temp
  3  ;

         X
----------
         1
         2

What is quite noticable is that it is possible to insert records using the view which are not be visible through the view. To prevent this behaviour you can use the Check Option.


SQL> drop view vw_temp;

View dropped.

SQL>
SQL> create view vw_temp
  2  as
  3  select *
  4    from temp
  5   where x <= 10
  6   with check option
  7  ;

View created.

SQL>
SQL> select *
  2    from vw_temp
  3  ;

         X
----------
         1
         2

SQL>
SQL> insert into vw_temp values (2);

1 row created.

SQL> insert into vw_temp values (102);
insert into vw_temp values (102)
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

Now, with the Check Option in place it is not possible to do DML through the view if the records are not visible through the view in the first place. The value "102" would never show up in the result set when querying the view. Simply because the view has the predicate:


where x <= 10

This functionality has been around for a very, very long time.
What I didn't know, until recently, is that the same Check Option is also available with Inline Views.
First I'll show the INSERT using an Inline View, next the INSERT using an Inline View with Check Option.


SQL> create table test
  2  (x int)
  3  /

Table created.

SQL> insert into (select x
  2                 from test
  3              )
  4  values (100)
  5  /

1 row created.

SQL> insert into (select x
  2                 from test
  3                where x <= 10
  4              )
  5  values (100)
  6  /

1 row created.

SQL>
SQL> insert into (select x
  2                 from test
  3                where x <= 10
  4                with check option
  5              )
  6  values (100)
  7  /
               from test
                    *
ERROR at line 2:
ORA-01402: view WITH CHECK OPTION where-clause violation

This also works well with bind variables, of course:


SQL> var b number
SQL> begin
  2     :b := 100;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> insert into (select x
  2                 from test
  3                where x <= 10
  4                with check option
  5              )
  6  values (:b)
  7  /
               from test
                    *
ERROR at line 2:
ORA-01402: view WITH CHECK OPTION where-clause violation

Something similar to the Check Option is available when you use Virtual Private Database (VPD).
When you add a policy to implemnent your VPD, using the dbms_rls.add_policy procedure, one of the parameters is named "UPDATE_CHECK", which is similar to the Check Option.
Records which are not visible when the VPD policy is applied can't be manipulated when you pass in TRUE for this parameter.

All scripts in this post are executed in this version:


SQL> select *
  2    from v$version
  3  /

BANNER
-------------------------------------------------------------
Personal Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Going to OOW2010 - Long Wednesday

2010-09-16T19:04:00.007+02:00

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

Last time I went to the USA (ODTUG in Washington, DC) the kids had a hard time. Because I had to leave in the middle of night (around 03:00 AM) I had to say goodbye the night before. After that they had a hard time getting to sleep. This time I planned to do things different. They were coming with me to the train station, say goodbye there so at least they could get a good night sleep.
It's been a while that I took the train anywhere, and this time I had the opportunity to go on a high speed train from Rotterdam to Schiphol Airport. "Because of the one year anniversity of Fyra (the train company) you will get a complimentary coffee". Excellent! Too bad I had to get off the train before I encountered the catering. Oh well,... The whole trip only took a little over an hour to get to the airport, nice.
Meeting my colleague Lucas Jellema at the airport, getting some work done for our joint presentation.Meeting Lonneke Dikmans and Mike van Alst at the gate.
The flight was smooth, planned on doing some work but watched movies instead.
The view from the hotel room on the ninth is fantastic, overlooking a lake and Oracle HQ right behind that (I will post a picture later).
Dinner was good, met up with Chris Muir, Richard Foote, Marcel Kratochvil, Lucas Jellema and Mike van Alst.
Because it was only 08:30 PM (local time) I felt it was too early to go to bed so I joined Chris to the bar. Debra Lilley and some other Oracle Ace Directors but I really couldn't keep my eyes open. Time to call defeat and go to bed.
Thursday morning at 5 o'clock (not local time, but time back home... it was only nine local time) I fell asleep.

Update 18 September:
Finally,... here's the photo:

Oracle OpenWorld, the countdown has begun

2010-09-08T12:26:00.008+02:00

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

On Thursday and Friday I am going to attend the Director Briefing at Oracle HQ in Redwood. Another first. I did pass by the Oracle HQ before and looked at it and that’s as close as I got.

There are so many sessions to choose from during the conference, it's going to be tough to figure out where to go. That’s going to be the chore for this afternoon.

Some of the social events are already on my calendar.

Sunday, September 19
07:00 PM	Annual ACE dinner	Commodore Cruises, Pier 40
Monday, September 20
04:00 - 05:00 PM	Ace Director and Java Champions Meet and Greet	Mason Street Tent
06:30 - 08:30 PM	Benelux Happy Hour	Restaurant Lulu, Folsom Street
07:30 - 10:30 PM	OTN Night Party	Howard Street Tent
Tuesday, September 21
07:30 PM	APEX Meetup	4th Street Bar & Deli
09:00 PM - 01:00 AM	Benelux Cocktail Party	Ruby Skye Club, 420 Mason Street
Wednesday, September 22
05:30 PM	Bloggers Meetup	Lower Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street
07:30 PM - 12:00 AM	Oracle Appreciation Event	Treasure Island

It’s going to be a busy week,... Now you know what I will be doing in the "off hours", hopefully we get a chance to meet face to face.

Birthday Gift from Oracle: Ace Director

2010-08-06T13:30:00.001+02:00

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

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

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

APEX 4.0: Removing Plugins

2010-08-05T13:00:00.004+02:00

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

Normally when you want to delete anything from APEX, you need to go in the EDIT mode of the item that you want to remove. At the top of the edit page there will be a few buttons at your disposal. Most of the time labelled something like "Cancel", "Apply Changes" and "Delete".
The logical thing to do when you want to remove a plugin, is to go to the edit page of the plugin and push the "Delete" button. But,...

Where is the "Delete" button?
Turns out that the "Delete" button is hidden from view when you are using the plugin in your application. And that makes sense. Say you are using my Watermark plugin on your page and you delete the plugin. What should happen to the item? Should it be deleted as well? Should it be changed to a text item? You decide, so APEX doesn't make this decision for you.
On the right side of the Edit page (edit page for the plugin) there is a Task List.

In this task list is a link labelled "Utilization". Follow the link to get an overview of where the plugin is being used.

Now you can start changing the items that use the plugin, until the plugin is no longer used. After you have done this, the "Delete" button will be shown and the plugin can be removed.

Tip: If you have Utilization Overview, open each reference in a separate Tab (assuming you're using tabbed browsing). After navigating from the overview to the edit page for the item, you are rerouted back to the Shared Components page. Getting back to the overview requires navigating to the Plugin page, the plugin you want to remove, using the Utilization link from the Task list and finally you are back on the overview page.. Lots of clicks. Easier to open an extra tab and refresh the Utilization overview.

APEX 4.0 Plugin: Watermark

2010-07-23T11:31:00.023+02:00

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

Most of the APEX 4 Plugin tutorials that I encountered are structured like "click here", "copy this". To me this doesn't aid in my understanding of the plugin mechanism. Needless to say that plugin tutorials written for APEX 4 EA (Early Adapter) are less effective, since the interface for creating plugin has changed. Currently the only way to learn how to create a plugin is going through someone else's plugin code and try to figure out what it is they did. Which can be quite challenging.

Enough for the rant. Time to get back to work.

Step 1. Download the jQuery plugin.
For this plugin, I used the Watermark jQuery plugin. On this page you can also see how the plugin should be used.
Step 2. Create a new plugin in APEX
Go to the Shared Components of your application and choose "Plug-ins" in the "User Interface" section.

On the Plugin overview page, click the CREATE button. Fill out the "Name" and "Internal Name" items, choose "Item" for Type and click the CREATE button. Why click the button now? Well, it turns out that the type of plugin can reveal extra properties on this page. So, if you're follow other "click here - copy this"-tutorials make sure to click the CREATE button after you filled out the name, internal and type.

Name: Watermark

Internal Name: nl.amis.watermark

Type: Item

Step 3: Upload the javascript library
To upload the javascript, go to the files section of the plugin edit/create form, and follow the wizard to upload the javascript file. As easy as that. Why do you need to do this? The plugin contains all necessary information to be exported into other applications. As this plugin needs the javascript library, include it with the plugin.
Step 4: Custom Attributes
What do you need to define the watermark? First of all some text, because that is the text that will be shown as the watermark. Second, the plugin allows you to define your own color instead of the standard light grey.
Go to the section labelled "Custom Attributes" and create two attributes:
First:

Scope: component

Display sequence: 10

Label: Watermark

Type: Text

Required: Yes

Help Text: The text that you want to appear as a Watermark in the item.

Second:

Scope: component

Display sequence: 20

Label: Color

Type: Text

Required: No

Default Value: #369

Help Text: The color of the Watermark. Default is #369, which is a light grey.

Why do you need to do this? You need to create some attributes to define the appearance of the plugin, in this case the text and the color of the text, giving some flexibility to the plugin.
Step 5: Create a Render function
The Render function is the code that generates the plugin on the form. What it needs to render is determined by the type of plugin, in this case an Input item. The signature of the render function looks like this


function <name of function> (

    p_item                in apex_plugin.t_page_item,

    p_plugin              in apex_plugin.t_plugin,

    p_value               in varchar2,

    p_is_readonly         in boolean,

    p_is_printer_friendly in boolean )

    return apex_plugin.t_page_item_render_result

Tip: To get the signature of the render function, click on the "Render Function Name" in the Callback section of the Plugin create/edit page.
In order for the plugin to render correctly, you need to adhere to this signature.

Using the HTP package, it's fairly easy to create an Input item.
What you need to construct is something like this


sys.htp.p ('<input type="text" name="'||l_name

           ||'" id="'||p_item.name||'" '

           ||'value="'||l_value

           ||'" size="'||p_item.element_width

           ||'" '||'maxlength="'||p_item.element_max_length

           ||'" '||p_item.element_attributes||' />');

Watch for the quotes and double quotes!

In the code above there is a reference to a local variable, named "l_name". In order to get the name attribute of the input item, you need to call the Apex_Plugin package:


l_name := apex_plugin.get_input_name_for_page_item (false);

The APEX_PLUGIN is actually a synonym for the WWV_FLOW_PLUGIN package. Reading the note from the package specification shows that you need this function before using the HTP package:


--==============================================================================

-- Returns the name attribute which has to be used for a HTML input element if

-- you want that the value of the element is stored in session state when the

-- page is submitted. If you have a HTML input element which returns multiple

-- values (eg. select list with multiple="multiple") you have

-- to set p_is_multi_value.

-- Note: This function has to be called before you write something to the

--       HTTP buffer with HTP.P(RN)

--==============================================================================

function get_input_name_for_page_item (

    p_is_multi_value in boolean )

    return varchar2;

Two more things to do in constructing the rendering function, include the javascript library and write the jQuery code to "attach" the watermark to the item.
Including the javascript library is as easy as calling a package,...


apex_javascript.add_library (p_name      => 'jquery.watermarkinput'

                            ,p_directory => p_plugin.file_prefix

                            ,p_version   => null

                            );

Note: Do not include the extension of the javascript library. In other words: omit the ".js" extension.
"Attaching" (by lack of better term) the watermark to the item, we need to refer to the documentation that goes with the Watermark plugin. This is what the render function needs to generate as "on Load" code:


jQuery(function($){

   $("#suffix").Watermark("Suffix","#369");

});

Using the Apex_Javascript package allows you to add this code to the "document ready" function. If you are not familiar with this term, forget about it. Just remember that the jQuery code is executed after the page is shown and all good things happen there. Want to know more? Google: "jQuery document ready" and you will find lots of tutorials on using this function.
The only thing we need to generate is the line which starts with the dollar ($) sign.
Something like this:


apex_javascript.add_onload_code (p_code =>

      '$("#'||p_item.name||'").Watermark("'||l_watermark||'","'||l_color||'");'

      );

Watch those quotes and double quotation marks again.

Now the complete rendering function looks like this, including some debug information. Notice the use of the Custom Attributes (defined as p_item.attribute_01 and p_item.attribute_02):


function render_watermark (p_item                in apex_plugin.t_page_item

                          ,p_plugin              in apex_plugin.t_plugin

                          ,p_value               in varchar2

                          ,p_is_readonly         in boolean

                          ,p_is_printer_friendly in boolean

                          )

   return apex_plugin.t_page_item_render_result

is

   l_value varchar2(32767) := sys.htf.escape_sc (p_value);

   l_name  varchar2(30);

   l_watermark apex_application_page_items.attribute_01%type := p_item.attribute_01;

   l_color     apex_application_page_items.attribute_02%type := coalesce (p_item.attribute_02, '#369');

   retval apex_plugin.t_page_item_render_result;

begin

   if apex_application.g_debug

   then

      apex_plugin_util.debug_page_item (p_plugin    => p_plugin

                                       ,p_page_item => p_item

                                       );

   end if;

   l_name := apex_plugin.get_input_name_for_page_item (false);

   if p_is_readonly or p_is_printer_friendly

   then

      if p_is_readonly and not p_is_printer_friendly

      then         

         sys.htp.p ('<input type="hidden" name="'||l_name||'" '||

                    'id="'||p_item.name||'" value="'||l_value||'" />'

                   );

      end if;

      sys.htp.p ('<span id="'||p_item.name||'_DISPLAY" '||

                 coalesce (p_item.element_attributes, 'class="display_only"')||

                 '>'||l_value||''

                );

   else

      sys.htp.p ('<input type="text" name="'||l_name||'" id="'||p_item.name||'" '||

                  'value="'||l_value||'" size="'||p_item.element_width||'" '||

                  'maxlength="'||p_item.element_max_length||'" '||

                  p_item.element_attributes||' />');

      apex_javascript.add_library (p_name      => 'jquery.watermarkinput'

                                  ,p_directory => p_plugin.file_prefix

                                  ,p_version   => null

                                  );

      apex_javascript.add_onload_code (p_code =>

      '$("#'||p_item.name||'").Watermark("'||l_watermark||'","'||l_color||'");'

      );

   end if;

   return retval;

end render_watermark;

I also set some standard attributes as depicted below.

Step 6: Validation
Now we are almost done. You could try it out now. But there is a catch,... What do you think gets stored in the database if you don't enter a value for the item? Exactly, the watermark text that you provide. In order to prevent that from happening, I searched for ways to use the functionality that the Watermark plugin provides.


function UseData(){

   $.Watermark.HideAll();

   //Do Stuff

   $.Watermark.ShowAll();

}

But was unable to find the correct location where to add this. So I decided to implement a "validation" function.
In the Callback section, where you also specify the render function, add a validation function - in this case call it "validate_watermark".
The function is not really a validation functionality, the only thing that it does is set the session state for the item to NULL - only when the value of the item is equal to the watermark text.


function validate_watermark (p_item   in apex_plugin.t_page_item

                            ,p_plugin in apex_plugin.t_plugin

                            ,p_value  in varchar2

                            )

   return apex_plugin.t_page_item_validation_result

is

   retval apex_plugin.t_page_item_validation_result;

   l_watermark    apex_application_page_items.attribute_01%type := p_item.attribute_01;   

begin

   if apex_application.g_debug

   then

      apex_plugin_util.debug_page_item (p_plugin    => p_plugin

                                       ,p_page_item => p_item

                                       );

   end if;

   if p_value = l_watermark

   then

      -- Set session state to NULL for the item

      apex_util.set_session_state(p_item.name, null);

   end if;

   return retval;

end validate_watermark;

Creating a simple plugin like this one is not very hard. Writing this blog took a lot more time than creating the actual plugin. The hardest part was trying to figure out what exactly is needed to render the item and how to fit the jQuery samples in the PL/SQL code.

Links
Watermark by Josh Bush (Digital Bush)
Easy reference for HTML Color Codes
A lot more plugins for APEX
jQuery homepage

Connecting Without TNSNames With EZConnect

2010-07-11T12:00:00.001+02:00

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

Just having a single shortcut on my desktop to connect to either the Oracle 10g database or the Oracle 11g database would make it easier to switch databases and show the differences between the two. I like the SQL*Plus Windows a little bit better over the SQL*Plus commandline version, it just feels more natural. I tried using SQL Developer for the demo's, but found zooming in and out to show snippets of SQL too distracting from the actual statement.
Because I probably forget what to use as the connect string, I had to write this down somewhere. And since this is my "Things I got to remember" blog,.. here it is:


SQL> conn alex/alex@//localhost/orcl11
Connected.
SQL> 
SQL> select *
  2    from v$version
  3  /

BANNER
------------------------------------------------------------
Personal Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

This works because I have EZCONNECT in my SQLNet.Ora:


NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Credit goes to Tom Kyte, of course.
Links
Oracle Magazine: On Connecting, Pivoting, and Learning New Tricks

Looking back at ODTUG Kaleidoscope 2010

2010-07-05T16:11:00.002+02:00

In case you missed it (how is that possible?) last week was the annual ODTUG conference, Kaleidoscope in Washington DC. In this post I will share some of, what I consider, the highlights of this event.
I always enjoy going to this conference, it's nice to see old friends and make some new ones.
During the Sunday symposium I attended "APEX: From A to Z". During this full day several presentations showed each step in creating an Application with APEX. They (the presentors) used the same application each time, continuing where the previous speaker stopped and introducing their own subject.
All aspects of application development were covered this day, from Modelling to Globalization.

Monday
On Monday, the regular sessions started. During the General Session the yearly awards were announced, I am proud to say that I was nominated for "Best Paper" and Cary Millsap won the award. Congratulations, Cary!

The APEX keynote was slightly disappointing, it was more "preaching to the choir". Paul Broughton did a session called "APEX through the eyes of a beginner" in which he told his first experience with APEX, he was quite nervous. He did a good job in sharing his story.

Anton Nielsen at the end did a session called "APEX: A Window into the Oracle database" which was more about the Oracle database than it was about APEX. I really enjoyed this session, probably because it was different than other APEX sessions. Most APEX sessions focus on the user interface of APEX, this session showed how to leverage Oracle Database functionality with APEX. Or was it because of his plug for my own session? ;)

Tuesday
Maria Colgan talked for an hour about the Explain Plan. This session gave a nice introduction into reading the Explain Plan and how to make sense of it. She also gave some pointers as where to start to look in case you run into trouble.

The vendor session that I attended,... we shall speak of it no more... very disappointing. Instead of showing what the product was about, it only stated some general remarks...

"Using Collections in APEX: The Definitive Intro" by Raj Mattamal was very informative. The demo that Raj did showed the power of collections. He could have explained some of the concepts a little better though.

Another session by Maria Colgan which was also very good. This time it was on "Finally Plan Stability During Database Upgrade with SQL Plan Management". During this session she explained the concepts of SQL Plan Management.
The last session of the day was by Jonathan Lewis "Co-operating with the Database". In this session he explained the "good and bad" on bind variables and the limitations of "what Oracle can do for you" and how you (as a developer) can help Oracle to respond efficiently.

Wednesday
After my own session, I attended "Thinking Clearly About Performance" by Cary Millsap. I like the way Cary does his presentations, even though I've heard this presentation before, I go there for the "show".

The next one was "Performance Instrumentation" by Karen Morton. This session was mainly about the ILO tool and Instrumentation in general. Previously the tool was called Hotsos-ILO and I've written about this on a previous occasion.

Next up was Tim St. Hilaire with "APEX and AJAX - where to start" which was a nice introduction into using AJAX within an APEX environment. Even though APEX 4.0 has recently been released, where a lot of AJAX-like things can be hidden from the developer, this overview makes it clear what is involved with AJAX calls.

Thursday
On Thursday I planned to go to "Oracle Development Sins and how Hackers (Can) Abuse Them" - 3 parts.. unfortunately this serie got cancelled. I knew it ahead of time, and was struggling hard to find a good alternative. It boiled down to not being able to make my mind up and eventually not going to any session at all. Looking back, I should have attended "Adding Statistics to Oracle's Application Express" by Rory McClean...
As this was the last day of the Kaleidoscope conference, Chris (Muir) and myself went to the Mall to do the necessary shopping. Around 6 pm we met up with Debra Lilley and Stanley to have a nice dinner at a Thai restaurant followed by an evening site seeing on the National Mall. Very nice.
Friday
Getting ready to fly back home. But first the Soccergame which put us in a good mood to fly back: Netherlands - Brazil...

To conclude: This was an excellent ODTUG Kaleidoscope. Hope to be there next year.

Analytic Function bug and National Championship

2010-06-14T11:41:00.006+02:00

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

In the Oracle Documentation it says that the DENSE_RANK is the Olympic Ranking:

... or the maximum (LAST) dense rank (also called olympic rank).

The major difference between the RANK and the DENSE_RANK function is the way the hand out numbers after a draw is encountered.
Taking Scott's EMP table as an example:


SQL> select ename
  2       , sal
  3       , rank () over (order by sal desc) rk
  4       , dense_rank () over (order by sal desc) dr
  5    from emp
  6   where deptno = 20
  7  /

ENAME             SAL         RK         DR
---------- ---------- ---------- ----------
SCOTT            3000          1          1
FORD             3000          1          1
JONES            2975          3          2
ADAMS            1100          4          3
SMITH             800          5          4

Notice how Scott and Ford have the same salary, and notice how the ranking in the last two columns differ. Jones with a salary of 2975 is either ranked third or second - depending on the use of RANK or DENSE_RANK.

According to the officials I spoke to yesterday, if there is a draw and you have a split first place there will be no Silver medal. This would mean that the Olympic Ranking would be RANK and not DENSE_RANK.

Probably I'm mistaken (again) so would appreciate your comments and corrections... :)

Links
Oracle Documentation

Analytic Function bug?

2010-06-10T20:01:00.005+02:00

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

While changing one of the demo's, I rediscovered of what I believe to be a documentation bug. The demo is about showing the Windowing Clause with Analytic Functions. If you are not familiar with Analytic Functions, attend my session and you will know all about it... ;)
Anyway, to create a running total using the EMP table use the SUM with the default Windowing Clause and you're good to go. You would have to know what the default Windowing Clause is in that case.
The documentation states

If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Let's go back to the example, a running total on Scott's EMP table


SQL> select ename
  2       , sal
  3       , sum (sal) over (order by ename) running_total
  4    from emp
  5   where deptno = 20
  6   order by ename
  7  ;
 
ENAME            SAL RUNNING_TOTAL
---------- --------- -------------
ADAMS           1100          1100
FORD            3000          4100
JONES           2975          7075
SCOTT           3000         10075
SMITH            800         10875

Because we omitted the Windowing Clause, we get the Default - range between unbounded preceding and current row.
Being explicit about the Windowing Clause, we get the same results - just as we expect.


SQL> select ename
  2       , sal
  3       , sum (sal) over (order by ename
  4                         range between unbounded preceding
  5                                   and current row
  6                        ) running_total
  7    from emp
  8   where deptno = 20
  9   order by ename
 10  ;
 
ENAME            SAL RUNNING_TOTAL
---------- --------- -------------
ADAMS           1100          1100
FORD            3000          4100
JONES           2975          7075
SCOTT           3000         10075
SMITH            800         10875

And the short notation of the Default Windowing Clause also yields the same results


SQL> select ename
  2       , sal
  3       , sum (sal) over (order by ename
  4                         range unbounded preceding
  5                        ) running_total
  6    from emp
  7   where deptno = 20
  8   order by ename
  9  ;
 
ENAME            SAL RUNNING_TOTAL
---------- --------- -------------
ADAMS           1100          1100
FORD            3000          4100
JONES           2975          7075
SCOTT           3000         10075
SMITH            800         10875

Why do I believe this to be a bug because of the definition of a Range Window.

RANGE specifies the window as a logical offset.

and this part:

If value_expr evaluates to a numeric value, then the ORDER BY expr must be a numeric or DATE data type.

If value_expr evaluates to an interval value, then the ORDER BY expr must be a DATE data type

Look back at the examples and notice that I didn't use a Numeric or DATE data type in the ORDER BY expression - I used a VARCHAR2 (ename is after all a VARCHAR2). And this is in contradiction with the documentation quotes from above.
Using the Range Window - not the Default one - with the ename column raises an exception


SQL> select ename
  2       , sal
  3       , sum (sal) over (order by ename
  4                         range between 400 preceding
  5                                   and 400 following
  6                         ) running_total
  7    from emp
  8   where deptno = 20
  9   order by sal
 10  /
 
select ename
     , sal
     , sum (sal) over (order by ename
                       range between 400 preceding
                                 and 400 following
                       ) running_total
  from emp
 where deptno = 20
 order by sal
 
ORA-00902: invalid datatype

I believe the Default Windowing Clause should be


rows between unbounded preceding and current row

And to use the Default Windowing explicitly would show this:


SQL> select ename
  2       , sal
  3       , sum (sal) over (order by ename
  4                         rows between unbounded preceding
  5                                   and current row -- Should be the default Window Clause
  6                        ) running_total
  7    from emp
  8   where deptno = 20
  9   order by ename
 10  ;
 
ENAME            SAL RUNNING_TOTAL
---------- --------- -------------
ADAMS           1100          1100
FORD            3000          4100
JONES           2975          7075
SCOTT           3000         10075
SMITH            800         10875

Maybe, probably, I misinterpret the Oracle Documentation. If you believe the Oracle Documentation is correct, can you try and explain it to me?
I did make a note of it in the Oracle Documentation, you can add user comments, but it never made it past the moderators... I guess they don't agree with me. ;)
Links
Oracle Documentation on Analytic Functions

Check your Datatype, also in Check Constraints

2010-06-04T11:15:00.007+02:00

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


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

As you can see in the query different predicates are used. One compares the "solved" column to a NUMBER and the other compares it to a CHAR.

Without seeing the table definition a lot of things could be the cause of not being able to see any data. A reproducible test case would be nice.


SQL> CREATE TABLE  "PROBLEM" 
  2     (   "PROBLEM_ID" NUMBER NOT NULL ENABLE, 
  3     "PROBLEM_DESCRIPTION" VARCHAR2(4000), 
  4     "ACTION_TAKEN" VARCHAR2(4000), 
  5     "TRANSACTION_ID" NUMBER NOT NULL ENABLE, 
  6     "SOLVED" NUMBER NOT NULL ENABLE, 
  7      CONSTRAINT "PROBLEM_PK" PRIMARY KEY ("PROBLEM_ID") ENABLE, 
  8      CONSTRAINT "SOLVED_VALUE" CHECK ( "SOLVED" IN ('1', '0')) enable
  9     )
 10  / 

Table created.

SQL>  
SQL>  
SQL> CREATE INDEX  "PROBLEM_IDX1" ON  "PROBLEM" ("TRANSACTION_ID")
  2  / 

Index created.

SQL>  
SQL>  CREATE SEQUENCE   "PROBLEM_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER  NOCYCLE
  2  / 

Sequence created.

SQL>  
SQL> CREATE OR REPLACE TRIGGER  "BI_PROBLEM" 
  2    before insert on "PROBLEM"               
  3    for each row  
  4  begin   
  5      select "PROBLEM_SEQ".nextval into :NEW.PROBLEM_ID from dual; 
  6  end; 
  7   
  8  / 

Trigger created.

SQL> ALTER TRIGGER  "BI_PROBLEM" ENABLE
  2  / 

Trigger altered.

SQL> insert into problem
  2     (problem_id
  3     ,problem_description
  4     ,transaction_id
  5     ,solved
  6     )
  7  values
  8     (22
  9     ,'the problem'
 10     ,81
 11     ,0
 12     );

1 row created.

SQL>    
SQL> insert into problem
  2     (problem_id
  3     ,problem_description
  4     ,transaction_id
  5     ,solved
  6     )
  7  values
  8     (2
  9     ,'There are'
 10     ,81
 11     ,0
 12     );

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> SELECT * 
  2    FROM problem 
  3   WHERE solved = '0';

no rows selected

SQL> SELECT *
  2    FROM problem
  3   WHERE solved = 0;

no rows selected

And there you have it, even though we created two records each with a 0 (zero) for the "solved" column, no rows were selected by either of the SELECT statements issued.
How could this be possible?
Let's have a look at the Explain plan for one of the statements:


SQL> select *
  2    from problem
  3   where solved = 0
  4  /

no rows selected

SQL> select *
  2    from table (dbms_xplan.display_cursor)
  3  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID  dz2r64h4m6f2z, child number 0
-------------------------------------
select *   from problem  where solved = 0

Plan hash value: 4173894327

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |     1 (100)|          |
|*  1 |  FILTER            |         |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| PROBLEM |     2 |  8086 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("SOLVED"=0)

Note
-----
   - dynamic sampling used for this statement

The filter with operation id 1 in the Explain Plan shows "NULL IS NOT NULL". As this predicate is always false no data is shown. According to the "Inside the Oracle Optimizer"-blog this effectively removes the table from the query.

The "NULL IS NOT NULL" filter is a false constant predicate, that will prevent the table scan from even taking place.

So because of the filter the data isn't shown, but why is the filter introduced? There must be something wrong here. In the past I've used similar queries on similar tables which all seemed to be working well. Why is this scenario different?
Let's have a closer look at the table definition


CREATE TABLE  "PROBLEM" 
   (   "PROBLEM_ID" NUMBER NOT NULL ENABLE, 
   "PROBLEM_DESCRIPTION" VARCHAR2(4000), 
   "ACTION_TAKEN" VARCHAR2(4000), 
   "TRANSACTION_ID" NUMBER NOT NULL ENABLE, 
   "SOLVED" NUMBER NOT NULL ENABLE, 
    CONSTRAINT "PROBLEM_PK" PRIMARY KEY ("PROBLEM_ID") ENABLE, 
    CONSTRAINT "SOLVED_VALUE" CHECK ( "SOLVED" IN ('1', '0')) enable
   )
/

Notice anything unusual?
No?...
How about now


...
   "SOLVED" NUMBER
...
  "SOLVED_VALUE" CHECK ( "SOLVED" IN ('1', '0'))
...

Notice the quotes in the Check Constraint definition? The datatype of the column is NUMBER and the Check in the Check constraint uses CHAR.
Could this be the cause? Let's verify


SQL> CREATE TABLE  "PROBLEM" 
  2     (   "PROBLEM_ID" NUMBER NOT NULL ENABLE, 
  3     "PROBLEM_DESCRIPTION" VARCHAR2(4000), 
  4     "ACTION_TAKEN" VARCHAR2(4000), 
  5     "TRANSACTION_ID" NUMBER NOT NULL ENABLE, 
  6     "SOLVED" NUMBER NOT NULL ENABLE, 
  7      CONSTRAINT "PROBLEM_PK" PRIMARY KEY ("PROBLEM_ID") ENABLE, 
  8      CONSTRAINT "SOLVED_VALUE" CHECK ( "SOLVED" IN (1, 0)) enable
  9     )
 10  / 

Table created.

SQL> insert into problem
  2     (problem_id
  3     ,problem_description
  4     ,transaction_id
  5     ,solved
  6     )
  7  values
  8     (22
  9     ,'the problem'
 10     ,81
 11     ,0
 12     );

1 row created.

SQL> 
SQL> select problem_description
  2    from problem
  3  /

PROBLEM_DESCRIPTION
----------------------------------------------------------------------------
the problem

And the filter is eliminated from the Explain Plan


SQL> select *
  2    from table (dbms_xplan.display_cursor)
  3  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID  18wkx6g72p35j, child number 0
-------------------------------------
select problem_description   from problem

Plan hash value: 3149918165

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| PROBLEM |     1 |  2002 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Puzzling
Why does these queries give output, even with the original Check Constraint in place? The only difference is using bind variable instead of hard coded values...


SQL> var b number
SQL> 
SQL> begin
  2     :b := 0;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select problem_description
  2    from problem
  3   where solved = :b
  4  /

PROBLEM_DESCRIPTION
----------------------------------------
the problem
There are

SQL> select problem_description
  2    from problem
  3   where solved = to_char (:b)
  4  /

PROBLEM_DESCRIPTION
----------------------------------------
the problem
There are

Any suggestions?

Oracle 11g Release 2
As always, things change with different versions. Until now this code has been run on Oracle 10g:


SQL> conn alex/alex@orcl
Connected.
SQL> select *
  2    from v$version
  3  /

BANNER
-------------------------------------------------------------
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

With Oracle 11g Release 2 the original queries (and original table definition) do return records:


SQL> select *
  2    from v$version
  3  /

BANNER
--------------------------------------------------------------

Personal Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> SELECT problem_description
  2    FROM problem
  3   WHERE solved = '0';

PROBLEM_DESCRIPTION
--------------------------------------------------------------

the problem
There are

SQL> SELECT problem_description
  2    FROM problem
  3   WHERE solved = 0;

PROBLEM_DESCRIPTION
--------------------------------------------------------------

the problem
There are

Links
Read the whole thread here.
Inside the Oracle Optimizer

Oracle 11gR2: Editions and SQL Developer

2010-04-07T09:20:00.017+02:00

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

When you connect with SQL Developer to an Oracle 11g Release 2 database, there are a number of extra folders in the browser.

Following the demo I used for the Planboard Symposium, I created a few Editions, which would result in a tree structure.

With a simple Right-click on the Edition of your choice, you can change your current Edition, like changing to the Base Edition.

And changing the Editions also changes the Editioning Views. Note that the underlying tables are none editionable objects.

While playing around with the Editions I noticed something weird though.
First change to another edition named "Release_1":

And create a new Editioning View, using SQL*Plus:

And because this Editioning View includes a new column, Language, we also need a Forward Cross Edition Trigger to be compatible with older Editions:

The SQL Developer browser has a folder for the Cross Edition Triggers which now includes the newly created trigger:

But now the strange bit; when you try to edit the Cross Edition Trigger code in SQL Developer, it doesn't show any code.

At least not in the place where you would expect it, the Cross Edition Trigger code does show up in context of the underlying Base Table, named EMP_BASE:

And here's the testscript that I used:


conn alex/alex@orcl11

set echo on
cl scr

select *
  from v$version
/

pause

cl scr

set echo off
prompt 
prompt [== Setup ==]
prompt 
set echo on

create table emp
as
select *
  from scott.emp
/

pause

set echo off
prompt 
prompt [== Editions ==]
prompt 
set echo on

create edition base as child of ora$base
/

create edition release_1 as child of base
/

create edition release_2 as child of release_1
/

pause

cl scr

set echo off
prompt 
prompt [== Rename Base Table ==]
prompt 
set echo on

alter table emp rename to emp_base
/

pause

set echo off
prompt 
prompt [== Change Session Edition ==]
prompt 
set echo on

alter session set edition = base
/

pause

select sys_context('userenv', 'current_edition_name') "Current_Edition"
  from dual
/


pause
cl scr

set echo off
prompt 
prompt [== Editioning View Base Release ==]
prompt 
set echo on

create or replace editioning view emp
as
select empno
     , ename
     , deptno
     , job
     , hiredate
     , sal
     , comm
     , mgr
  from emp_base
/

pause


desc emp

pause

select *
  from emp
/

pause

cl scr
set echo off
prompt 
prompt [== Change Session Edition ==]
prompt 
set echo on

alter session set edition = release_1
/

pause

select sys_context('userenv', 'current_edition_name') "Current_Edition" 
from dual
/

pause
set echo off
prompt 
prompt [== Add additional column to Table ==]
prompt 
set echo on

alter table emp_base
add (language varchar2(2) null)
/

pause

desc emp_base

pause
set echo off
prompt 
prompt [== Editioning View Emp inherited ==]
prompt 
set echo on

desc emp

pause
cl scr

set echo off
prompt 
prompt [== Editioning View Release 1 ==]
prompt 
set echo on

create or replace editioning view emp
as
select empno
     , ename
     , deptno
     , job
     , language
     , hiredate
     , sal
     , comm
     , mgr
  from emp_base
/

pause

desc emp

pause

select *
  from emp
/

pause
cl scr
set echo off
prompt 
prompt [== Function Derive Language Value ==]
prompt 
set echo on

create or replace
function get_language (p_job in varchar2)
   return varchar2
is
begin
  return case p_job 
         when 'MANAGER' then 'fr' 
         else 'en'
         end;
end get_language;
/

pause


set echo off
prompt 
prompt [== Forward Cross Edition Trigger        ==]
prompt [== DML on earlier editions fire trigger ==]
prompt 
set echo on


create or replace trigger EMP_1_2_Fwd_Xed
before insert or update on emp_base
for each row
forward crossedition
disable
begin 
   :new.language := get_language(:new.job);
end EMP_1_2_Fwd_Xed; 
/

pause
set echo off
prompt 
prompt [== Enable Trigger ==]
prompt 
set echo on

alter trigger EMP_1_2_Fwd_Xed enable
/


pause
cl scr
set echo off
prompt 
prompt [== Test Trigger from Base Edition ==]
prompt 
set echo on

alter session set edition = base
/

pause

update emp
   set job = 'PA'
 where job = 'CLERK'
   and deptno = 10
/

pause

select *  
  from emp
 where job = 'PA'
   and deptno = 10
/

pause

select *
  from emp_base
 where job = 'PA'
   and deptno = 10
/

commit
/

pause
cl scr
set echo off
prompt 
prompt [== Change to Release_1 Edition ==]
prompt 
set echo on

alter session set edition = release_1
/

select ename
     , job
     , language
  from emp
/

pause

set echo off
prompt 
prompt [== Use Forward Crossedition Trigger     ==]
prompt [== to upgrade existing records in table ==]
prompt [== Consider: DBMS_PARALLEL_EXECUTE      ==]
prompt 
set echo on

declare 
  c number := dbms_sql.open_cursor(); 
  x number; 
begin 
   dbms_sql.parse
   ( c => c
   , Language_Flag => dbms_sql.native
   , Statement => 'UPDATE EMP_BASE
                   SET EMPNO = EMPNO'
   , Apply_Crossedition_Trigger => 'EMP_1_2_Fwd_Xed'
   );
   x := dbms_sql.execute(c); 
   dbms_sql.close_cursor(c); 
   commit; 
end; 
/

pause

select ename
     , job
     , language
  from emp
/

pause

cl scr

set echo off
prompt 
prompt [== Change to Release_2 Edition ==]
prompt 
set echo on

alter session set edition = release_2
/

set echo off
prompt 
prompt [== Change to Mandatory Column ==]
prompt 
set echo on

alter table emp_base
modify 
( language varchar2(2)  not null 
)
/

pause
set echo off
prompt 
prompt [== Add extra columns ==]
prompt 
set echo on

alter table emp_base
add 
( first_name varchar2(25)
, last_name  varchar2(25)
)
/

pause

set echo off
prompt 
prompt [== Editioning View Emp Release_2 ==]
prompt 
set echo on

create or replace editioning view emp
as
select empno
     , first_name
     , last_name
     , deptno
     , job
     , language
     , hiredate
     , sal
     , comm
     , mgr
  from emp_base
/

pause
cl scr
set lines 140

select *
  from emp
/


pause
set echo off
prompt 
prompt [== Forward Crossedition Trigger ==]
prompt [== Derive First and Last name   ==]
prompt 
set echo on

create or replace trigger EMP_1_3_Fwd_Xed
before insert or update of ename on emp_base
for each row
forward crossedition
disable
begin 
  :new.last_name := :new.ename;
end EMP_1_3_Fwd_Xed; 
/

alter trigger EMP_1_3_Fwd_Xed enable
/

pause
cl scr
set echo off
prompt 
prompt [== Apply Forward Crossedition Trigger ==]
prompt 
set echo on

declare 
  c number := dbms_sql.open_cursor(); 
  x number; 
begin 
  dbms_sql.parse
  ( c => c
  , Language_Flag => dbms_sql.native
  , Statement => 'UPDATE EMP_BASE
                  SET ENAME = ENAME'
  , Apply_Crossedition_Trigger => 'EMP_1_3_Fwd_Xed'
  );
  x := dbms_sql.execute(c); 
  dbms_sql.close_cursor(c); 
  commit; 
end; 
/

pause

select * 
  from emp
/

pause
cl scr
set echo off
prompt 
prompt [== Reverse Crossedition Trigger ==]
prompt [== Set ename based on LastName  ==]
prompt 
set echo on

create or replace trigger EMP_3_2_Rve_Xed
before insert or update of  last_name on emp_base
for each row
reverse crossedition
disable
begin 
  :new.ename := substr(:new.last_name, 1, 10);
end EMP_3_2_Rve_Xed; 
/


alter trigger EMP_3_2_Rve_Xed enable
/


pause

select sys_context ('userenv', 'current_edition_name') "Current_Edition"
  from dual
/

pause

set echo off
prompt 
prompt [== Change Lastname from Release_2 Edition ==]
prompt 
set echo on

update emp
   set last_name = 'MARTINA'
 where last_name = 'MARTIN'
/

commit
/

pause

select ename
     , last_name
  from emp_base
/

pause

alter session set edition = release_1
/

select ename
  from emp
/
pause

update emp
   set ename = 'MARTIN'
 where ename = 'MARTINA'
/

pause

select ename
     , last_name
  from emp_base
/

commit
/

pause

cl scr

set echo off
prompt
prompt [== Clean up==]
prompt
set echo on


alter session set edition = ora$base
/

pause

set echo off
prompt
prompt [== Make Editions Unusable ==]
prompt
set echo on

alter edition release_2 unusable
/
alter edition release_1 unusable
/
alter edition base unusable
/

pause

set echo off
prompt
prompt [== Drop Editions ==]
prompt
set echo on

drop edition release_2 cascade
/
drop edition release_1 cascade
/
drop edition base cascade
/
pause

set echo off
prompt
prompt [== Remove Base Table ==]
prompt
set echo on

drop table emp_base purge
/

drop view emp
/

Change the XML Prolog using XMLROOT

2010-03-29T09:00:00.000+02:00

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


<?xml version="1.0"?>

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

With the XMLRoot function you can create the XML Prolog. Besides the XML value, you need to specify the Version as well. This can be any value that you want.


SQL> select xmlroot (xmltype ('that')
  2                 ,version '24.5'
  3                 ) "XMLRoot"
  4    from dual
  5  /

XMLRoot
---------------------------------------------------------
<?xml version="24.5"?>
<this>that</this>

Quite strangly, you can also specify that you want NO VALUE for the Version. Why strange? Look at this


SQL> select xmlroot (xmltype ('that')
  2                 ,version no value
  3                 ) "XMLRoot"
  4    from dual
  5  /

XMLRoot
----------------------------------------------------
<?xml version="1.0"?>
<this>that</this>

It seems a bit odd to me, specifying NO VALUE but you get a value anyway. This behavior is documented to do so, but why not call it DEFAULT?
The last argument is optional and allows you to create a STANDALONE clause. The exact meaning of it is not clear to me, one day I will find out what it means.


SQL> select xmlroot (xmltype ('that')
  2                 ,version '1.0'
  3                 ,standalone YES
  4                 ) "XMLRoot"
  5    from dual
  6  /

XMLRoot
--------------------------------------------------
<?xml version="1.0" standalone="yes"?>
<this>that</this>

As you can see there is no argument where you can specify the encoding. After searching for a long-long time, I gave my search and decided to concatenate the XML Prolog to the XML (after having extracting it as a CLOB). Not a nice solution, but it works.
And than suddenly last week on the Oracle Technology Network SQL and PL/SQL forum someone asked how to specify the encoding in the XML Prolog... Now why didn't I think of that? I spend way too much time on that forum, and I never thought of it of posting a question there. Anyway, because you can put whatever you want in the Version argument,


SQL> select xmlroot (xmltype ('that')
  2                 ,version 'whatever'
  3                 ) "XMLRoot"
  4    from dual
  5  /

XMLRoot
-----------------------------------------------------
<?xml version="whatever"?>
<this>that</this>

you can also place the encoding that you want in there.


SQL> select xmlroot (xmltype ('that')
  2                 , version '1.0" encoding="utf-8'
  3                 ) "XMLRoot"
  4    from dual
  5  /

XMLRoot
----------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<this>that</this>

Just mind the quotes and double quotes in the Version argument.

Links

OTN SQL and PL/SQL Discussion forum thread
Oracle Documentation on XMLRoot

Identifying the "last" record using the LEAD function

2010-02-25T10:00:00.000+01:00

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

First let's create a sample table - a simple one with just one column.


SQL> create table test
  2  as
  3  select rownum id
  4    from dual
  5   connect by level <= 5
  6  ;

Table created.

The content of this table are just 5 consecutive numbers, ranging from 1 through 5.


SQL> select id
  2    from test
  3   order by id
  4  ;

        ID
----------
         1
         2
         3
         4
         5

Since we are going to use the LEAD function, it's important to understand what the LEAD function does. With the LEAD function it is possible to access values from others rows in the resultset. The value that is returned is the next one in line, therefore the ORDER BY in the analytic function is important.
Below is the effect shown of the LEAD function. For the first line of the resultset, the value of the second line is shown. For the second line, the third one is shown etcetera. For the last line in the resultset there is no "next line", a NULL is shown.


SQL> select id
  2       , lead (id) over (order by id) ind_last_record
  3    from test
  4   order by id
  5  ;

        ID IND_LAST_RECORD
---------- ---------------
         1               2
         2               3
         3               4
         4               5
         5

Because we want to identify the "last" record, based on the sorted ID, a CASE expression is used where we look at the results of the LEAD:


SQL> select id
  2       , case
  3         when lead (id) over (order by id) is null
  4         then 'Yes'
  5         end ind_last_record
  6    from test
  7   order by id
  8  ;

        ID IND
---------- ---
         1
         2
         3
         4
         5 Yes

And there it is, the last record has the indicator "Yes".

The LEAD function can take up to three arguments. The first argument is the value that you want returned. The second argument is the number of rows that you want to look ahead - the default is 1. The third argument is the value you want to see in case the LEAD function returns NULL - the default is NULL.
Because of the optional arguments we can simplify the query to use "Yes" as the last argument when the LEAD would return NULL.


SQL> select id
  2       , lead (to_char (id), 1, 'Yes') over (order by id)
  3         ind_last_record
  4    from test
  5   order by id
  6  ;

        ID IND_LAST_RECORD
---------- ----------------------------------------
         1 2
         2 3
         3 4
         4 5
         5 Yes

For the first argument a TO_CHAR is required, otherwise the exception ORA-1722 (Invalid Number) is raised - After all, "Yes" is not a NUMBER.
Last step to take: only the indicator for the last record is required, let's change the first argument to NULL instead of TO_CHAR (id)


SQL> select id
  2       , lead (null, 1, 'Yes') over (order by id)
  3         ind_last_record
  4    from test
  5   order by id
  6  ;

        ID IND
---------- ---
         1
         2
         3
         4
         5 Yes

Pretty nifty trick, if I say so myself.... :)
... and don't forget to cleanup the testset.


SQL> drop table test purge
  2  ;

Table dropped.

Oracle documentation
Lead Analytic Function

XMLType, RefCursor and renaming ROW and ROWSET tags

2010-02-23T10:00:00.003+01:00

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

First let me show you how the initial function works and what the XML result was they returned.


SQL> create function rc_to_xml (p_rc in sys_refcursor)
  2     return xmltype
  3  is
  4  begin
  5     return xmltype (p_rc);
  6  end rc_to_xml;
  7  /

Function created.

The function has an input parameter which is a Ref Cursor and returns an XMLType. The XMLType constructor can take a Ref Cursor and transform that into an XMLType, pretty cool functionality. In order to test this functionality, we need two local variables - one for the Ref Cursor and one for the XMLType.


SQL> declare
  2     refC sys_refcursor;
  3     x    xmltype;
  4  begin
  5     open refC for
  6     select ename
  7          , job
  8          , sal
  9       from emp
 10      where rownum <= 2
 11     ;
 12     x := rc_to_xml (refC);
 13     dbms_output.put_line (x.extract ('/*').getClobVal());
 14  end;
 15  /

Using DBMS_OUTPUT to see the actual content of the XMLType, we will get this result:


<ROWSET>
  
    SMITH
    CLERK
    800
  
  
    ALLEN
    SALESMAN
    1600
  
</ROWSET>

A nice XMLType is the result. The only "problem" is the standard tags used by the XMLType. In this project we didn't want to have the standard ROWSET and ROW tags, we wanted to have more meaningful names.
Changing the function to change these tags on the fly would work


SQL> create or replace
  2  function rc_to_xml (p_rc     in sys_refcursor
  3                     ,p_rowset in varchar2
  4                     ,p_row    in varchar2
  5                     )
  6     return xmltype
  7  is
  8     retval xmltype;
  9     c      clob;
 10  begin
 11     retval := xmltype (p_rc);
 12     c := retval.getClobVal();
 13     c := replace (c, '', '<'||p_rowset||'>');
 14     c := replace (c, '', '');
 15     c := replace (c, '', '<'||p_row||'>');
 16     c := replace (c, '', '');
 17     retval := xmltype (c);
 18     return retval;
 19  end rc_to_xml;
 20  /

Function created.

Just to make sure it works as expected, a small testcase:


SQL> declare
  2     refC sys_refcursor;
  3     x    xmltype;
  4  begin
  5     open refC for
  6     select ename
  7          , job
  8          , sal
  9       from emp
 10      where rownum <= 2
 11     ;
 12     x := rc_to_xml (refC
 13                    ,'DEPT'
 14                    ,'EMP'
 15                    );
 16     dbms_output.put_line (x.extract ('/*').getClobVal());
 17  end;
 18  /
<DEPT>
  
    SMITH
    CLERK
    800
  
  
    ALLEN
    SALESMAN
    1600
  
</DEPT>

And that did the trick for quite some time (years actually).

This technique was used quite heavily for reporting purposes, it even works fine with nesting one XMLType in another:


SQL> create or replace
  2  function all_emps (p_deptno in dept.deptno%type)
  3     return xmltype
  4  is
  5     rc sys_refcursor;
  6  begin
  7     open rc for select ename
  8                      , job
  9                      , sal
 10                   from emp
 11                  where deptno = p_deptno;
 12     return rc_to_xml (rc
 13                      ,'EMPS'
 14                      ,'EMP'
 15                      );
 16  end all_emps;
 17  /

Function created.

In the below code sample you can see the above function being used in the Ref Cursor on line 8.


SQL> declare
  2     refC sys_refcursor;
  3     x    xmltype;
  4  begin
  5     open refC for
  6     select deptno
  7          , dname
  8          , all_emps (deptno) employees
  9       from dept
 10      where deptno = 10
 11     ;
 12     x := rc_to_xml (refC
 13                    ,'DEPTS'
 14                    ,'DEPT'
 15                    );
 16     dbms_output.put_line (x.extract ('/*').getClobVal());
 17  end;
 18  /
<DEPTS>
  
    10
    ACCOUNTING
    
      
        

    CLARK
          MANAGER
          2450
        
        
          KING
          PRESIDENT
          5000
        
        
          MILLER
          CLERK
          1300
        
      
    
  
</DEPTS>


PL/SQL procedure successfully completed.

Very flexible, used extensively throughout the reporting packages.

Last week problems started to occur with these packages however. XMLTypes were not being created correctly, strange characters started to appear causing the XML to be invalid. But not with all reports, not all the time. It appeared that if the reports became to large, this problem would occur.
After looking for the cause for quite some time, we had this feeling that it had to do with the datatype conversions going on between the XMLType and the Clob. This transformation is done in the REPLACE part of the function where the ROWSET and ROW tags are replaced.
First we started looking into creating a "REPLACE function for CLOB", which is according to the documentation not necessary... what a waste of time...
I couldn't help it, but I was getting the feeling that I wasn't the first one with this "problem". Surely there must be a way to rename the ROWSET and ROW tags when you create an XML based on a Ref Cursor?
Probably I was looking in the wrong direction, instead of treating the XML as a string, I should stay in the "XML Domain".
After more searching, finally I came across this procedure: setRowTag in the dbms_xmlgen package - just what the doctor ordered. All the convoluted code we used earlier was no longer necessary.


create or replace 
function rc_to_xml (p_rc     in sys_refcursor
                   ,p_rowset in varchar2
                   ,p_row    in varchar2
                   ) 
   return xmltype
is
   retval xmltype;
   ctx dbms_xmlgen.ctxHandle;
begin
   ctx := dbms_xmlgen.newContext (p_rc);
   dbms_xmlgen.setRowTag (ctx, p_row_name);
   dbms_xmlgen.setRowSetTag (ctx, p_rowset_name);
   retval := dbms_xmlgen.getXMLType (ctx);
   dbms_xmlgen.closeContext (ctx);
   return retval;
end rc_to_xml;
/

It's always good to realize that when you are trying to solve a certain problem someone must have come across your problem before and maybe even solved it. Hope my story will save you some time when you are trying to rename the ROWSET and ROW tags.

Oracle Documentation
REPLACE; it also works with CLOB
DBMS_XMLGEN - all subprograms

The Case for the Case Statement

2010-01-16T12:05:00.000+01:00

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

The CASE statement, like the CASE expression, comes in two flavors: Simple and Searched. To illustrate, this is the Simple CASE


SQL> declare
  2     selector varchar2(1);
  3  begin
  4     selector := 'Y';
  5     case selector
  6     when 'Y' then
  7        dbms_output.put_line ('Yes!');
  8     when 'N' then
  9        dbms_output.put_line ('No..');
 10     end case;
 11  end;
 12  /
Yes!

and this is the Searched Case


SQL> declare
  2     selector varchar2(1);
  3  begin
  4     selector := 'Y';
  5     case 
  6     when selector = 'Y' then
  7        dbms_output.put_line ('Yes!');
  8     when selector = 'N' then
  9        dbms_output.put_line ('No..');
 10     end case;
 11  end;
 12  /
Yes!

Notice the subtle difference in lines 5 and 6.
The difference is the type of comparison that you do. With the Simple Case, you do an equality comparison and with the Searched Case, you can do whatever you feel like - in this case also an equality comparison. When you want to use a comparison with NULL, than your only option is to use a Searched Case. An equality comparison with NULL is always False.
Also note that the Case Statement ends with an "END CASE", just like the IF statement ends with an "END IF".

Can you replace an IF statement with a CASE statement? Well, yes and no. Let me explain.
The IF statement and the CASE statement look very much alike, the difference is in whether or not a match is found.
Say you want to create a function which return a Yes or No depending on your input value. Of course this is a completely stupid example, but it's the idea that counts.


SQL> create or replace
  2  function fun (p_in in varchar2)
  3     return varchar2
  4  is
  5     l_in   varchar2 (1);
  6     retval varchar2 (20);
  7  begin
  8     l_in := upper (p_in);
  9     if l_in = 'Y'
 10     then
 11        retval := 'Yes!';
 12     elsif l_in = 'N'
 13     then
 14        retval := 'No...';
 15     end if;
 16     return retval;
 17  end fun;
 18  /

Function created.

So far, so good. Let's see if it works as expected.


SQL> begin
  2     dbms_output.put_line ('Input Y: '||fun ('Y'));
  3     dbms_output.put_line ('Input N: '||fun ('N'));
  4  end;
  5  /
Input Y: Yes!
Input N: No...

PL/SQL procedure successfully completed.

Looks good to me, but what if someone would call the function with a different input value?


SQL> 
SQL> begin
  2     dbms_output.put_line ('Input Z: '||fun ('Z'));
  3  end;
  4  /
Input Z:

PL/SQL procedure successfully completed.

Hmmmm,... the function is successfully completed, but the return value is NULL. If this alright, than you're done. If the Business Rule says it is not allowed, than you would need to change your code. For instance with an ELSE in the IF statement, which raises an exception. Or use the CASE statement and see how you can benefit from it.
First let's rewrite the function:


SQL> create or replace
  2  function fun (p_in in varchar2)
  3     return varchar2
  4  is
  5     l_in   varchar2 (1);
  6     retval varchar2 (20);
  7  begin
  8     l_in := upper (p_in);
  9     case l_in
 10     when 'Y' then
 11        retval := 'Yes!';
 12     when 'N' then
 13        retval := 'No...';
 14     end case;
 15     return retval;
 16  end fun;
 17  /

Function created.

...and do the same test we did before


SQL> 
SQL> begin
  2     dbms_output.put_line ('Input Y: '||fun ('Y'));
  3     dbms_output.put_line ('Input N: '||fun ('N'));
  4  end;
  5  /
Input Y: Yes!
Input N: No...

PL/SQL procedure successfully completed.

So far, so good. Now for the not allowed value:


SQL> begin
  2     dbms_output.put_line ('Input Z: '||fun ('Z'));
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at "ALEX.FUN", line 8
ORA-06512: at line 2

As you can see, it blows up in your face. Because there is not a match for the incoming value, the Case statement raises an exception - something that the IF statement doesn't do for you out-of-the-box.
This is just what the Business Rule ordered. No need for extra coding to raise an exception.
Can this exception be handled gracefully? Yes, it can. Oracle gives us the "CASE_NOT_FOUND" exception.


SQL> begin
  2     dbms_output.put_line ('Input Z: '||fun ('Z'));
  3  exception
  4     when case_not_found
  5     then
  6        dbms_output.put_line ('Gracefully Handled');
  7  end;
  8  /
Gracefully Handled

So, can the IF statement be replaced: yes. Can it be done without thinking about the consequences: no.

Best Wishes

2009-12-22T10:00:00.002+01:00

Let SQL do the talking:


select decode
      (sign (floor (maxwidth/2) - rownum)
      ,1, lpad (' ', floor (maxwidth/2) - (rownum -1))
         ||rpad ('*', 2 * (rownum -1), ' *')
      ,lpad ('* * *', floor (maxwidth/2) + 3)) "Merry Christmas"
from all_objects
,    (select 40 as maxwidth from dual)
where rownum < floor (maxwidth/2) + 5

... and in case your wondering what the result would be, I added a screenshot.

Best wishes and a Happy New Year!

ODTUG Kaleidoscope 2010: Accepted Abstract

2009-12-14T10:00:00.000+01:00

My presentation on Analytic Functions is accepted for the ODTUG Kaleidoscope 2010 conference. The other abstract that I sent in is an alternate, standby if you will. As you can see on the right side, I have attended (and presented) at the ODTUG yearly conference since 2005.

The yearly ODTUG Kaleidoscope is one of my favorite conferences to go to. Besides the really good sessions, there is always lots of time for networking.
This year it will be held in Washington, D.C..
The Abstracts:
Analytic Functions Revisited

Even though Oracle has enriched SQL with Analytic Functions a long time ago, they are still relatively unknown. Analytic Functions add inter-row calculations, aggregates over multiple dimensions, or rank assignment based on values within a group of values - all without a GROUP BY clause. They can make your life as a developer a lot easier once you get Analytic Functions in your fingertips. Even though the syntax may seem daunting at first, it becomes second nature very quickly. Oracle 11gR2 brings you new and exciting Analytic Functions.
This presentation will cover the syntax and is packed with real life examples of how to apply them on a daily basis.See the performance increase with just a few lines of code and bring you sheer development joy.

SQL Holmes - The Revisited Case of the Missing Performance

During Kaleidoscope 2009 a case study was unfolded to investigate the cause of a slow performing query. In this presentation we will revisit this case and investigate further possibly leading to a solution for the Missing Performance.

Did the database just "have a bad day"? Was the evil DBA to blame? The PL/SQL developer who didn't get enough coffee? Or was it the application sending the "wrong" query in the first place?

In this classic "whodunnit" you will take a tour past the crime scene. Investigate the query, use the tools of the trade and collect all the relevant information. Follow the trail to uncover the truth and nothing but the truth...

We'll see if I have to prepare for one or two presentations when the time comes....
Hope to see you there.

Revenge of the Multibyte Characters

2009-11-23T10:00:00.000+01:00

In The Netherlands, where I'm from, we do have Multibyte characters. Names with diacritic characters occur frequently.
In a prior post I've written about using Oracle text to perform diacritic searches.

This post is about the table definition. Every now and then this problem rears its ugly head, that's why I decided to write this little note on it.

Let's look at the problem at hand. First we'll create a table:

SQL> create table test
  2  (name varchar2 (7))
  3  /

Table created.

No problem so far.. but what happens when we try to insert a name with diacritics into this table?

SQL> insert into test values ('Alèx')
  2  /

1 row created.

No problem. Or so it seems... what is the length of the name that I just inserted into the table?

SQL> select length (name)
  2    from test
  3  /

LENGTH(NAME)
------------
           4

Even though the name contains a diacritic character, the length is four. But what if we insert a name with length seven with diacritic characters?

SQL> rollback
  2  /

Rollback complete.

SQL> 
SQL> insert into test values ('Thérèse')
  2  /
insert into test values ('Thérèse')
                         *
ERROR at line 1:
ORA-12899: value too large for column "ALEX"."TEST"."NAME" (actual: 9, maximum: 7)

The error message is not quite clear. It says the name is too long, but when you count them like

T h é r è s e 
1 2 3 4 5 6 7

it should match.... Of course it has to do with the diacritic characters in the name.

We can check this by using the LENGTHB function (notice the "B" at the end of Length)

SQL> with local_name
  2  as
  3  (select 'Thérèse' name
  4     from dual
  5  )
  6  select length (name)
  7       , lengthb (name)
  8    from local_name
  9  /

LENGTH(NAME) LENGTHB(NAME)
------------ -------------
           7             9

When we query the length in Bytes, it becomes obvious. The last name we inserted was too long in Bytes. How to fix this? Either increase the length of the column, or change it from BYTE to CHAR (and I don't mean the datatype CHAR)

SQL> drop table test
  2  /

Table dropped.

SQL> create table test
  2  (name varchar2 (7 char))
  3  /

Table created.

Now the column will allow up to seven characters in it regardless of the byte size of the data. Or at least that's how I interpret the documentation.
The name which we couldn't insert with a prior INSERT statement, now comfortably fits into the column:

SQL> insert into test values ('Thérèse')
  2  /

1 row created.

So, when you use Multibyte characters, check you Length Semantics!

You can check your setting with this statement:

SQL> show parameter nls_length_semantics

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
nls_length_semantics                 string      BYTE

And just to clean up the test table.

SQL> drop table test
  2  /

Table dropped.

Links
Oracle Documentation

(Non Oracle): Shocking Truth

2009-11-23T09:41:00.004+01:00

Do you know what it looks like when a couple of guys are trying out how much electricity runs through an electric fence?

The guys in this clip are my nephews Michiel, Randy, and Jesper. During a family reunion gettaway they couldn't resist trying out the electirc fence across the street... My brother just happened to be filming...

Planboard Symposium, the day after.

2009-11-18T16:28:00.005+01:00

Yesterday the Planboard Symposium, a symposium for DBA presented by DBA, took place in Driebergen, The Netherlands.
The setup of this conference is a little different from what I'm used to. Only two parallel sessions to choose from with lots of time in between the sessions, which is nice for networking.
For everybody who attended my session, a big "Thank you". For everybody who decided to attend Harald's session, you missed out on something good, and I finished on time ;)
As a consolodation price for those of you that didn't attend the Planboard Symposium, here is my presentation.

Edition Based Redefinition

View more presentations from AlexNuijten.

An Empty Clob is not NULL, it's NOT NULL

2009-11-13T08:00:00.001+01:00

Oracle recommends using CLOB instead of LONG for columns in the database. We all know this, right? Using CLOB is a lot easier than trying to manipulate LONG. Makes our life a lot easier.
But there is something about using CLOBs that I didn't know. As you might have guessed from the title it has to do with NULL...

First let's set up a table for testing:


SQL> create table test
  2  (id   number
  3  ,text clob
  4  );

Table created.

Along with some sample data:


SQL> insert into test values (1, null);

1 row created.

SQL> 
SQL> insert into test values (2, 'some data is here');

1 row created.

SQL> 
SQL> insert into test values (3, empty_clob());

1 row created.

SQL> 
SQL> commit;

Commit complete.

At first I expected that this query would show the records with the ID 1 and 3, but this is not the case.


SQL> select *
  2    from test
  3   where text is null
  4  /

        ID TEXT
---------- -------------
         1

An Empty_Clob is not NULL. According to the documentation:

EMPTY means that the LOB is initialized, but not populated with data.

When you run the following query, you can see this


SQL> select *
  2    from test
  3   where text is not null
  4  /

        ID TEXT
---------- -----------------
         2 some data is here
         3

Unfortunately we can't use the DUMP function to see what the Empty_Clob is made up of.


SQL> select dump (text)
  2    from test
  3  /
select dump (text)
             *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

So how can you execute a SQL statement to find the NULL and the Empty_Clob?

One way would be to look at the length of the CLOB column, just like


SQL> select id
  2       , text
  3    from test
  4   where length (text) = 0
  5  /

        ID TEXT
---------- ------------------
         3

But than you would only select the Empty_Clob, not the NULL. Adding an extra predicate could solve this.


SQL> select id
  2       , text
  3    from test
  4   where length (text) = 0
  5      or text is null
  6  /

        ID TEXT
---------- ------------------
         1
         3

Another way to do this, is using NULLIF. NULLIF is one of those "obscure" functions that are rarely used, at least I hardly ever see this function used.


SQL> select id
  2       , text
  3    from test
  4   where nullif (length (text), 0) is null
  5  /

        ID TEXT
---------- ----------------------------------
         1
         3

Personally I like this use of NULLIF. What do you think?

Cleanup:


SQL> drop table test
  2  /

Table dropped.

Links to the Oracle Documentation:
DUMP
EMPTY_CLOB
NULLIF

Create Users with DBMS_METADATA

2009-11-11T08:00:00.005+01:00

Not too long ago I wrote a blog on using DBMS_METADATA to extract DDL for tables, so when I got an assignment to migrate Users I immediately thought of using DBMS_METADATA to do this.

The User Migration would consist of all the users in the database being renamed following a new convention. Don't ask why they wanted this, they had some very good reasons to want so.
All the users had to be recreated along with all their privileges to their new name.
At first I thought of writing all sorts of queries against the datadictionary which sounded like a daunting task. On second thought it dawned to me that a CREATE USER statement is DDL and DDL can be extracted using DBMS_METADATA.
Google is your friend at times like that. Turned out you can use DBMS_METADATA to generate the CREATE USER statement, very easily.

The way we did it was create a table with the "old" and the "new" usernames, and join this to the appropriate DBA_* view. In the script below the name of this table is "USER_MIGRATIE" containing two columns: "old_name" and "new_name"
Instead of boring you with the details, here is the script we used to generate the scripts


--create table user_migratie
--(old_name varchar2(30)
--,new_name varchar2(30)
--);

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

set long 2000000000
set head off
set pages 0
set feedback off
set termout off


select replace (
          dbms_metadata.get_ddl ('USER', upper (mig.old_name))
               , upper (mig.old_name), upper (mig.new_name)
       )
  from user_migratie mig
  join dba_users     usr
    on usr.username = upper (old_name)
 union all
select replace (
          dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', upper (old_name))
               , upper (old_name), upper (new_name)
       )
  from user_migratie mig
  join dba_ts_quotas tq
    on tq.username = upper (mig.old_name)
 union all 
 select replace (
          dbms_metadata.get_granted_ddl ('ROLE_GRANT', upper (old_name))
                       , upper (old_name), upper (new_name)
       )
  from user_migratie mig
  join dba_role_privs rpv
    on rpv.grantee = upper (mig.old_name)
 union all
select replace (
          dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', upper (old_name))
               , upper (old_name), upper (new_name)
       )
  from user_migratie mig
  join dba_sys_privs spv
    on spv.grantee = upper (mig.old_name)
 union all
select replace (
          dbms_metadata.get_granted_ddl ('OBJECT_GRANT', upper (old_name))
               , upper (old_name), upper (new_name)
       )
  from user_migratie mig
  join dba_tab_privs tpv
    on tpv.grantee = upper (mig.old_name)
 union all
select case
        when ((select count(*)
                 from dba_role_privs
                where grantee = upper (old_name)
                  and default_role = 'YES'
                  and rownum = 1
               ) > 0
             )
       then replace (
            dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', upper (old_name))
               , upper (old_name), upper (new_name))
       end
  from user_migratie
 union all
select replace (
         dbms_metadata.get_ddl('PROFILE', profile)
        , old_name, new_name)
  from user_migratie mig
  join dba_users     usr
    on usr.username = upper (mig.old_name)
 where usr.profile <> 'DEFAULT'
.


spool create_user_like.sql
/
spool off


ed create_user_like.sql

Links I used for creating this script:
Oracle Alchemist
askTom
Oracle Documentation

Importing an Excel into an Oracle database

2009-10-26T10:24:00.014+01:00

There are several ways to do this, but I will only show you how to do this using SQL Developer. Why? Because I just came across this functionality the other day.
However there are some things you have to pay attention to, you might get frustrated with it otherwise.

Let's start with an Excel file.

This (very) simple Excel sheet shows three columns. The "A" column is a DATE; the "B" column is a number with a Financial Format; the "C" column is a string.

In the navigator of SQL Developer, when you right click on the Tables tab, you will get a context menu. One of the options is "Import Data ..."

An dialog is shown to locate your file.

After you locate you file, the Data Import Wizard is shown. In this Wizard, you can specify which page in your Excel you want to impot. Remember that our first column was a DATE?
Excel has it's own definition of what a DATE is. It's a numeric representation.
As you can see in the screenshot below, the Import Wizard doesn't recognize this Date format. Let's see the effect of this, for now let's continue.

Notice that you can also choose to have a script generated for you to create an External Table or to have a SQL*Loader file.

In the second step of the Import Wizard you can choose which columns you want to include in the destination table. For this simple test, we will select all of the columns.

Step three of the Wizard is to define the destination table. Notice two things here:

we changed the datatype of the first column to DATE

we changed the column name of the first colunmn to "date_col " in lowercase

The fourth and last step includes a verification step

Using the "Verify" button will do a sanity check of the data which is going to be imported. As you can see in the screenshot above, it does signal a mismatch between the data in the Excel sheet and the datatype of the destination column.
Also notice that the "Voltooien" (Dutch for "Finish") is greyed out. You can reactivate this button by navigating back to the third page and forward to the fourth page in the wizard.

We'll continue with "Voltooien" (="Finish"). The Message log will show you:

The table that we wanted is created. Inserting data into the didn't succeed though. If you look closely at the generated INSERT statement, you will notice that the column name is in lowercase. Hmmm...
The table definition changed the lowercase to an uppercase columnname - and rightfully so: "Thou shalt never create case sensitive objects"

Let's correct the situation and use a CSV instead of a native Excel format.
First save the Excel sheet as a CSV. But... first check your delimiter and change it if necessary to a comma.
To change this you need to Customize the Regional Settings. As you can see in the screenshot below, the default list separator is a semicolon. Change this to a comma.

After this step save your Excel file as a CSV.
Use the Data Import Wizard as before now selecting the newly saved CSV file. The first column will now show you a "real" date instead of a number.

Continuing with the Import Wizard, we now will use an uppercase column name, use the correct datatype and provide the format mask. For the number column in the CSV you will need to provide the appropriate Scale and Precision in order to load the data correctly. This is not shown in the screenshot.

When we verify the data in the next tab, we will get a "SUCCES" on every check. "Voltooien" (= "Finish") and we're set.

The table is created, the data is loaded into the table.

One last catch: The transaction is not automatically ended. You need to explicitly end it by either a COMMIT or a ROLLBACK.

Just to recap:
Next time to need to import data from an Excel sheet into an Oracle database, pay attention to the following points

Verify you delimiter, change if necessary: Control panel --> Regional setting --> Customize --> List Separator

Save the Excel as a CSV

Import the Data using the newly created CSV

Rename columns if needed and enter these in Uppercase

Provide the correct format mask when importing dates

Provide the correct format mask when importing numbers

End your transaction (COMMIT or ROLLBACK)

UPDATE 28-10-2009

A colleague of mine just pointed out that you can supply the delimiter when you import a CSV file. For whatever reason I completely missed this, first signs of aging? In the screenshot above I've circled this setting. Thank you, Peter for pointing this out.

Oracle PL/SQL Programming Preview

2009-10-13T16:56:00.006+02:00

This post will be mainly in Dutch. It's about a Preview we are doing in our office in Nieuwegein, The Netherlands.
The sessions will probably be in Dutch, but can be changed on the fly to English if desired.
If you are not able to make it to our office, you still have a chance to see Patrick do his presentations in Atlanta, Georgia during the Oracle PL/SQL Programming Conference, November 10 and 11.
You can register by following this link.

Our Event in Nieuwegein will be on October 20 (Thanks Erik for pointing out the date was missing)

Here is the agenda for this evening:

De Oracle PL/SQL Programming conference vind dit jaar in Atlanta, Georgia plaats. Patrick Barel is daarbij om een drietal presentaties te geven. Voor degene die niet aanwezig zullen zijn in Atlanta vind deze OPP-Preview plaats.
Tijdens deze KC zal Patrick twee van deze presentaties geven.
Na het diner zal Alex een re-run geven van zijn ODTUG presentatie: "SQL Holmes".

Het programma voor deze avond:
16:30
"Pipelined table functions" - Patrick Barel
Pipelined table functions offer an ideal convergence of the elegance and simplicity of PL/SQL with the performance of SQL. Complex data transformations are effortless to develop and support with PL/SQL, yet to achieve high-performance data processing, we often resort to set-based SQL solutions. Pipelined functions bridge the gap between the two methods effortlessly, but they also have some unique performance features of their own, making them a superb performance optimization tool.

18:00
diner: Chinees

Na diner (rond 19:00):
"SQL Holmes: The Case of the Missing Performance" - Alex Nuijten
During this presentation, a case study is unfolded to reveal the true cause of a slow performing query. Did the database just "have a bad day"? Was the evil DBA to blame? The PL/SQL developer who didn't get enough coffee? Or was it the application sending the "wrong" query in the first place?
In this classic "whodunnit" you will take a tour past the crime scene. Investigate the query, use the tools of the trade and collect all the relevant information. Follow the trail to uncover the truth and nothing but the truth...

20:00
Ter afsluiting:
"Optimizing SQL with Collections" - Patrick Barel
Collections (array-like structures in PL/SQL) are used in two of the most important performance features of PL/SQL: FORALL and BULK COLLECT. This session demonstrates the power of these features and offers in-depth guidance on how to apply them.

Just the plain DDL, please.. DBMS_METADATA

2009-10-09T07:30:00.001+02:00

The other day I needed to get some DDL statements from the datadictionary. In the old days you could write your own queries to extract all this. Nowadays you can use the built in package DBMS_METADATA to get the DDL for you.
Let's take a look at how you can use this. The DDL that I want to extract is the infamous EMP table, normally in the SCOTT schema.


SQL> create table emp
  2  as
  3  select *
  4    from scott.emp
  5  /

Table created.

To make it a little more interesting, we also add the DEPT table and place some constraints on them.


SQL> create table dept
  2  as
  3  select *
  4    from scott.dept
  5  /

Table created.

SQL> 
SQL> alter table dept
  2  add constraint dept_pk primary key (deptno)
  3  /

Table altered.

SQL> 
SQL> alter table emp
  2  add constraint emp_dept_fk foreign key (deptno) references dept (deptno)
  3  /

Table altered.

If you use DBMS_METADATA just like that, you might get more than you asked for.


SQL> set long 5000
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP')
  2    from dual
  3  /

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "ALEX"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPTNO")
          REFERENCES "ALEX"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

As you can in the code block above, you get the complete DDL including the storage clauses and the constraints. If this is not what you want, and I didn't want all this, than you need to modify some transformation parameters.


SQL> 
SQL> begin
  2     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
  3     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
  4     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
  5     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
  6     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Each of these transformation parameters take out bit by bit parts of the generated DDL. After running the above anonymous block we will get the following result.


SQL> select dbms_metadata.get_ddl ('TABLE', 'EMP')
  2    from dual
  3  /

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "ALEX"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   )

That's more like it, just what I was after. The plain DDL for the EMP table.
One of the nice things is that you don't need to modify all the transformation parameters again to go back to the default. They made it really easy to return to the default settings:


SQL> begin
  2     dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'DEFAULT');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>

Documentation on DBMS_METADATA

OPP 5: The Big Fat Book on PL/SQL

2009-10-06T13:27:00.007+02:00

Yesterday I got the fifth edition of "Oracle PL/SQL Programming" by Steven Feuerstein. Another 1200+ pages on PL/SQL. This edition has been updated to cover versions through Oracle 11gR2.
It is loaded with the latest on Oracle PL/SQL Programming, hence the title.
When I started programming in PL/SQL I had a great mentor. When I told him I had a hard time writing PL/SQL, he advised me to get Feuerstein's book (the second edition was just out).
Immediately I loved it, couldn't put it down. I dragged this book everywhere. Reading it in the traffic jam on my way to work, reading it before I went to sleep. By the time I finished that book, people at work started asking me questions about PL/SQL. Next to my copy of the Second Edition are also the Third and the Fourth edition. And now, I need to clear some space on the shelve, the fifth edition will take it's place soon. After I'm finished reading it.
I'm sure this book is a must have for anyone who uses PL/SQL on a daily basis.

I did mention in an earlier blogpost that I was reviewing some chapters for an upcoming book, well it was this book. And needless to say I'm very proud that I was mentioned in the preface. But I have to say I just submitted the first errata. Not a biggie, but still... My lastname in the book was spelled Nuitjen, while it should be Nuijten. Oh well...

Update 22-10-2009:
My friend and colleague Patrick Barel pointed out that Google knows who I am ;)

Planboard Symposium: Registration open

2009-09-28T10:09:00.003+02:00

For the fourth time the Planboard Symposium will be held on November 17. This symposium is unique as it's "for DBA by DBA". This one day symposium will have 5 parallel sessions with lots of time for networking and open discussions.
I am lucky enough to be one of the presentors and my topic will be "Continuous Database Application Evolution in Oracle Database 11g Release 2 ", a mouth full.
This session will discuss a new feature of Oracle 11gR2: Edition Based Redefinition. Not just "a new feature", it's "The new feature".

You can register on the Planboard Symposium site

Oh, and by the way... don't tell anyone I'm not a DBA... ssshhh...
see you there.

Carrying down values with Analytic Functions

2009-09-24T12:01:00.004+02:00

The other day - yesterday actually - I got an email with a question regarding Analytic Functions.
This was the requirement for the query:

In a table there are a CODE, STARTDATE and VAL columns. The combination of CODE and STARTDATE are mandatory, the VAL is optional. I want to get an overview where the VAL column shows the latest (based on the STARTDATE) value. If there is no value filled out for a particular date, it should show the value of the latest entry for that particular CODE.

Let's take a look at an example to clarify the requirement. First of all the data in the table:


COD STARTDATE VAL
--- --------- ----------
A   24-SEP-08 QRS
A   24-OCT-08
A   24-NOV-08
A   24-DEC-08 a
A   24-JAN-09
A   24-FEB-09 XY
A   24-MAR-09 ABC
A   24-APR-09
A   24-MAY-09
B   24-DEC-08 BLA
B   24-JAN-09
B   24-FEB-09
B   24-MAR-09 BLABLA
B   24-APR-09

As you can see in the above output, there are two CODES ("A" and "B"). The combination of CODE and STARTDATE is unique. The last column (VAL) has some values filled out, not all.
The desired output would be:


COD STARTDATE VAL
--- --------- ------
A   24-SEP-08 QRS
A   24-OCT-08 QRS
A   24-NOV-08 QRS
A   24-DEC-08 a
A   24-JAN-09 a
A   24-FEB-09 XY
A   24-MAR-09 ABC
A   24-APR-09 ABC
A   24-MAY-09 ABC
B   24-DEC-08 BLA
B   24-JAN-09 BLA
B   24-FEB-09 BLA
B   24-MAR-09 BLABLA
B   24-APR-09 BLABLA

When the VAL column has no entry (IS NULL) the output should show the latest value of the VAL column - based on the Startdate within the CODE. Latest in this case means: the most recent value of the VAL column with regards to the STARTDATE column of the current record.

Is the requirement clear? Time to solve it. I will show you two ways of solving this, there are possibly many more ways of getting the required output. One way is quite cumbersome, but will work in Oracle 8.1.6 EE and up. The other way is really trivial and works in Oracle 10g and up.

First method, fully explained.
Per CODE, we need to identify which VAL should be carried down, until a different VAL is encountered. To identify these subgroups we use a Case statement:


SQL> select code
  2       , startdate
  3       , val
  4       , case
  5           when val is not null
  6           then 1
  7         end new_val
  8    from tbl
  9   order by code
 10          , startdate
 11  /

COD STARTDATE VAL           NEW_VAL
--- --------- ---------- ----------
A   24-SEP-08 QRS                 1
A   24-OCT-08
A   24-NOV-08
A   24-DEC-08 a                   1
A   24-JAN-09
A   24-FEB-09 XY                  1
A   24-MAR-09 ABC                 1
A   24-APR-09
A   24-MAY-09
B   24-DEC-08 BLA                 1
B   24-JAN-09
B   24-FEB-09
B   24-MAR-09 BLABLA              1
B   24-APR-09

Now the column NEW_VAL shows the marker for each subgroup. Using the "running total" technique we can clearly see which records belong together.


SQL> select code
  2       , startdate
  3       , val
  4       , sum (case
  5                when val is not null
  6                then 1
  7              end
  8             ) over (partition by code
  9                         order by startdate
 10                    ) new_val
 11    from tbl
 12   order by code
 13          , startdate
 14  /

COD STARTDATE VAL           NEW_VAL
--- --------- ---------- ----------
A   24-SEP-08 QRS                 1
A   24-OCT-08                     1
A   24-NOV-08                     1
A   24-DEC-08 a                   2
A   24-JAN-09                     2
A   24-FEB-09 XY                  3
A   24-MAR-09 ABC                 4
A   24-APR-09                     4
A   24-MAY-09                     4
B   24-DEC-08 BLA                 1
B   24-JAN-09                     1
B   24-FEB-09                     1
B   24-MAR-09 BLABLA              2
B   24-APR-09                     2

With the distinction made - you can see the different subgroups, each having a unique number within the partition, we need the first value of the VAL column per subgroup. For that we will use the FIRST_VALUE function. Notice that the partitioning clause in the FIRST_VALUE is using the subgroups we defined in the previous section as well as the CODE.


SQL> select code
  2       , startdate
  3       , first_value (val)
  4             over (partition by code
  5                              , new_val
  6                       order by startdate
  7                  ) new_val
  8    from (
  9  select code
 10       , startdate
 11       , val
 12       , sum (case
 13                when val is not null
 14                then 1
 15              end
 16             ) over (partition by code
 17                         order by startdate
 18                    ) new_val
 19    from tbl
 20  )
 21   order by code
 22          , startdate
 23  /

COD STARTDATE NEW_VAL
--- --------- ----------
A   24-SEP-08 QRS
A   24-OCT-08 QRS
A   24-NOV-08 QRS
A   24-DEC-08 a
A   24-JAN-09 a
A   24-FEB-09 XY
A   24-MAR-09 ABC
A   24-APR-09 ABC
A   24-MAY-09 ABC
B   24-DEC-08 BLA
B   24-JAN-09 BLA
B   24-FEB-09 BLA
B   24-MAR-09 BLABLA
B   24-APR-09 BLABLA

And there you have it, the required result. But it takes a lot of typing. In Oracle 10g we can get the same results, but a lot simpler.
Newly added in Oracle 10g is the IGNORE NULLS clause. And it does exactly what it says, it ignores nulls.
In the result we want to get the last value, per partition within the window that gets larger with the current row. The default windowing clause is Rows Unbounded Preceding (the docs say Range Unbounded Preceding, but I believe this is wrong. Range only works for a numeric offset like with Dates and Numbers)
In the following query, I inserted the windowing clause in there. Just to be very explicit.


SQL> select code
  2       , startdate
  3       , val
  4       , last_value (val ignore nulls)
  5              over (partition by code
  6                        order by startdate
  7                        rows between unbounded preceding
  8                                 and current row
  9                   ) new_val
 10    from tbl
 11  ;

COD STARTDATE VAL        NEW_VAL
--- --------- ---------- ----------
A   24-SEP-08 QRS        QRS
A   24-OCT-08            QRS
A   24-NOV-08            QRS
A   24-DEC-08 a          a
A   24-JAN-09            a
A   24-FEB-09 XY         XY
A   24-MAR-09 ABC        ABC
A   24-APR-09            ABC
A   24-MAY-09            ABC
B   24-DEC-08 BLA        BLA
B   24-JAN-09            BLA
B   24-FEB-09            BLA
B   24-MAR-09 BLABLA     BLABLA
B   24-APR-09            BLABLA

As the Windowing Clause is the default, you can also omit it.


SQL> select code
  2       , startdate
  3       , last_value (val ignore nulls)
  4              over (partition by code
  5                        order by startdate
  6                   ) new_val
  7    from tbl
  8  ;

COD STARTDATE NEW_VAL
--- --------- ----------
A   24-SEP-08 QRS
A   24-OCT-08 QRS
A   24-NOV-08 QRS
A   24-DEC-08 a
A   24-JAN-09 a
A   24-FEB-09 XY
A   24-MAR-09 ABC
A   24-APR-09 ABC
A   24-MAY-09 ABC
B   24-DEC-08 BLA
B   24-JAN-09 BLA
B   24-FEB-09 BLA
B   24-MAR-09 BLABLA
B   24-APR-09 BLABLA

DELETE in the MERGE statement

2009-07-29T09:58:00.002+02:00

The Merge statement was introduced in Oracle 9i and improved upon in Oracle 10g.
In Oracle 9i only the INSERT and UPDATE parts were supported, in Oracle 10g DELETE was added. The "merge_update_clause" and "merge_insert_clause" became optional.
The basic syntax for the MERGE statement:

DELETE can only occur in the "merge_update_clause" of the above schema. This means that it must occur in the WHEN MATCHED THEN clause.

Until recent, I missed this part of the description of the "merge_update_clause" concerning the DELETE operation. First I will show you what I thought, then I'll show you where the behavior is documented.

First we'll create a table with two columns:


SQL> select *
  2    from v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production

SQL> 
SQL> create table t
  2  as
  3  select rownum rn
  4       , 'A' ind
  5    from all_objects
  6   where rownum <= 5
  7  /

Table created.

SQL> update t
  2     set ind = 'D'
  3   where rn = 5
  4  /

1 row updated.

SQL> 
SQL> select *
  2    from t
  3  /

        RN I
---------- -
         1 A
         2 A
         3 A
         4 A
         5 D

Notice that the last record, with RN 5, has an Ind "D".

Next we will merge a record into this table.


SQL> merge into t
  2  using (select 3 i
  3              , 'D' ind 
  4           from dual
  5        ) dat
  6     on (t.rn = dat.i)
  7   when matched then
  8     update set t.ind  = dat.ind
  9     delete where t.ind = 'D' --<--- Here is the DELETE
 10  /

1 row merged.

Only one row? Shouldn't that be two rows? One for the UPDATE and one for the DELETE?
Question for you: Which record(s) is (are) affected by this statement?

My wrong assumption was this:
Record with RN 3 has had the IND column changed to "D" and all records with IND "D" are removed. Effectively removing records with RN 3 and 5.

Now the quote from the documentation.

The only rows affected by this clause are those rows in the destination table that are updated by the merge operation.

This means that records in the destination table are not deleted when they are not updated by the MERGE first.


SQL> select *
  2    from t
  3  /

        RN I
---------- -
         1 A
         2 A
         4 A
         5 D

As you can see the record with RN 5 is still in the table. Because it was not updated in the merge, it was not deleted.
In order to remove some records from the table using the MERGE statement, you need to update these records first. It is not possible to dismiss the UPDATE statement from the MERGE:


SQL> merge into t
  2  using (select 3 i
  3              , 'D' ind 
  4           from dual
  5        ) dat
  6     on (t.rn = dat.i)
  7   when matched then
  8     delete where t.ind = 'D' --<--- Here is the DELETE
  9  /
   delete where t.ind = 'D' --<--- Here is the DELETE
   *
ERROR at line 8:
ORA-00905: missing keyword

... learn something every day.

documentation link

NVL2 - useful? Two use cases

2009-07-21T10:26:00.004+02:00

Sometimes you encounter a function that makes you wonder why Oracle made it. NVL2 is one of those functions, at least I think so. Even though it was introduced quite a long time ago, I've never used it in production code.
Until now that is.
NVL2 was introduced in Oracle 8i (8.1.7 according to Tahiti.Oracle.com)

Syntax
The NVL2 function takes three arguments. The first argument (could also be an expression) is evaluated for NULL or NOT NULL. When this argument evaluates to NOT NULL, the second expression is returned. When the first argument evaluates to NULL then last (third) expression is returned.
It works like this pseudocode:


if argument1 is not null
then 
   argument2;
else
   argument3;
end if;

First Use Case: Concatenate strings with an optional delimiter
In The Netherlands, where I'm from, you can choose the way your last name should be used after marriage.
In general there are four options, say your lastname is "Jansen" and your partners name is "de Vries" - both very common names in The Netherlands - then these are your options:

keep your own lastname: Jansen

use the lastname of your partner: de Vries

your own lastname hyphen partner lastname: Jansen - de Vries

partner lastname hyphen own lastname: de Vries - Jansen

Say we have a table which store your name, your partners name and your preference of "name usage"


drop table names
/

create table names
(lastname         varchar2(35)
,partner_lastname varchar2(35)
,name_usage       varchar2(2)
)
/
insert into names values ('Jansen', 'de Vries', 'O') -- Own
/
insert into names values ('Jansen', 'de Vries', 'P') -- Partner
/
insert into names values ('Jansen', 'de Vries', 'OP') -- Own - Partner
/
insert into names values ('Jansen', 'de Vries', 'PO') -- Partner - Own
/

Using a simple Case Expression we can have the names the way we want them


SQL> select case name_usage
  2         when 'O'  then lastname
  3         when 'P'  then partner_lastname
  4         when 'OP' then lastname||' - '||partner_lastname
  5         when 'PO' then partner_lastname||' - '||lastname
  6         end full_lastname
  7    from names
  8  /

FULL_LASTNAME
------------------------------------------------------------
Jansen
de Vries
Jansen - de Vries
de Vries - Jansen

Looks good sofar. But,... there is always a but... The data is not always up to par. Some records have "inappropriate" name_usage indicators:


insert into names values ('Jansen', null, 'O') -- Own
/
insert into names values ('Jansen', null, 'OP') -- Own - Partner
/
insert into names values ('Jansen', null, 'PO') -- Partner - Own
/

I left out the Partner usage when there is no Partner - this would just be a "regular" NVL.

When we run the query we used before, we will get this output


FULL_LASTNAME
------------------------
Jansen
de Vries
Jansen - de Vries
de Vries - Jansen
Jansen
Jansen -
 - Jansen

7 rows selected.

The last two look strange, don't you agree? There are hyphens there when they shouldn't be.
We could of course write another Case Expression nested inside our already present Case:


SQL> select case name_usage
  2         when 'O'  then lastname
  3         when 'P'  then partner_lastname
  4         when 'OP' then lastname||case
  5                                  when partner_lastname is not null
  6                                  then  ' - '||partner_lastname
  7                                  end
  8         when 'PO' then partner_lastname||case
  9                                          when partner_lastname is not null
 10                                          then  ' - '||lastname
 11                                          end
 12         end full_lastname
 13    from names
 14  /

FULL_LASTNAME
-------------------------------------------------------------------------
Jansen
de Vries
Jansen - de Vries
de Vries - Jansen
Jansen
Jansen


7 rows selected.

As you can see the SQL statement becomes quite bulky, but very easy to understand. It's also possible to use NVL2 to do the same


SQL> select case name_usage
  2         when 'O'  then lastname
  3         when 'P'  then partner_lastname
  4         when 'OP' then lastname ||nvl2 (partner_lastname, ' - ', null)||partner_lastname
  5         when 'PO' then partner_lastname ||nvl2 (partner_lastname, ' - ', null)||lastname
  6         end full_lastname
  7    from names
  8  /

FULL_LASTNAME
-------------------------------------------------------------------------
Jansen
de Vries
Jansen - de Vries
de Vries - Jansen
Jansen
Jansen
Jansen

7 rows selected.

Nice.

Of course the same thing can be achieved using the good ol' DECODE:


SQL> select case name_usage
  2         when 'O'  then lastname
  3         when 'P'  then partner_lastname
  4         when 'OP' then lastname ||decode (partner_lastname
  5                                          , null, null, ' - ')||partner_lastname
  6         when 'PO' then partner_lastname ||decode (partner_lastname
  7                                                  , null, null, ' - ')||lastname
  8         end full_lastname
  9    from names
 10  /

FULL_LASTNAME
-------------------------------------------------------------------------
Jansen
de Vries
Jansen - de Vries
de Vries - Jansen
Jansen
Jansen
Jansen

7 rows selected.

Second Use Case: Implementing an Arc
To implement an Arc relation in the database, the NVL2 function could also be useful.
Let's start with the model


drop table t3
/
drop table t1
/
drop table t2
/

create table t1
(c1 int primary key)
/

create table t2
(c1 int primary key)
/

create table t3
(c1 int primary key
,t1_c1 int references t1
,t2_c1 int references t2
)
/

The Arc should be implemented on the T3 table. Either the relation to T1 should be filled or the relation to T2 should be filled. They should not both be filled for the same record.
This can be implemented with a Case Expression in a Check Constraint


SQL> alter table t3
  2  add constraint arc_chk check (case 
  3                                when t1_c1 is not null 
  4                                 and t2_c1 is null
  5                                then 1
  6                                when t1_c1 is null
  7                                 and t2_c1 is not null
  8                                then 1
  9                                else 0
 10                                end = 1
 11                                )
 12  /

Table altered.

The expression in the Check Constraint is

the value of the Case Expression (either "1" or "0") must be equal to "1"

And to test our Arc implementation:


SQL> insert into t1 values (1)
  2  /

1 row created.

SQL> 
SQL> insert into t2 values (2)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (10, 1, null)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (11, null, 2)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (12, 1, 2)
  2  /
insert into t3 values (12, 1, 2)
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.ARC_CHK) violated


SQL> 
SQL> insert into t3 values (13, null, null)
  2  /
insert into t3 values (13, null, null)
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.ARC_CHK) violated

This seems to work, When either relation is filled, all is well. When both relations are filled, or none of the relations is, the Check Constraint is violated.

Now let's do the same thing with NVL2.


SQL> rollback
  2  /

Rollback complete.

SQL> 
SQL> alter table t3
  2  drop constraint arc_chk
  3  /

Table altered.

SQL> 
SQL> 
SQL> alter table t3
  2  add constraint arc_chk check (nvl2 (t1_c1, 1, 0)
  3                                + nvl2 (t2_c1, 1, 0)
  4                                = 1)
  5  /

Table altered.

The Check Constraint is a lot shorter, but does the same thing.


SQL> 
SQL> insert into t1 values (1)
  2  /

1 row created.

SQL> 
SQL> insert into t2 values (2)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (10, 1, null)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (11, null, 2)
  2  /

1 row created.

SQL> 
SQL> insert into t3 values (12, 1, 2)
  2  /
insert into t3 values (12, 1, 2)
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.ARC_CHK) violated


SQL> 
SQL> insert into t3 values (13, null, null)
  2  /
insert into t3 values (13, null, null)
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.ARC_CHK) violated

The NVL2 function can also take an expression as the first argument. Just for completeness I have taken this example from the documentation:


SELECT last_name, salary, NVL2(commission_pct, 
   salary + (salary * commission_pct), salary) income
   FROM employees WHERE last_name like 'B%'
   ORDER BY last_name;

LAST_NAME                     SALARY     INCOME
------------------------- ---------- ----------
Baer                           10000      10000
Baida                           2900       2900
Banda                           6200       6882
Bates                           7300       8468
Bell                            4000       4000
Bernstein                       9500      11970
Bissot                          3300       3300
Bloom                          10000      12100
Bull                            4100       4100

And there you have it, two use cases - and an example from the docs - for the NVL2 function.

What would be a reason not to use the NVL2 function? One reason would be that the function is quite hard to read (comparable to interpreting DECODE). Another would be that this function is not very well known.
The Use Cases described above are the cases where I use the NVL2 function. Simply because it's so concise. Matter of preference I think.
Do you use it? And how - for which use cases - do you use it?

Making up Data with Partition Outer Join

2009-07-07T12:00:00.004+02:00

Just the other day on the Oracle SQL and PL/SQL forum, someone asked on how to create non-existent rows. This post is not about generating a number of rows.
It's about handling Sparse Data, when you want to fill in some missing records in your result set.
First time I heard about this was in a blog written by Lucas Jellema.
Let's first start out with the table and some test data:


SQL> create table test_table1 
  2  (status varchar2(15)
  3  ,manager number
  4  ,sales number);

Table created.

SQL> insert into test_table1 values ('in process', 14, 100);

1 row created.

SQL> insert into test_table1 values ('in process', 15, 10);

1 row created.

SQL> insert into test_table1 values ('in process', 15, 40);

1 row created.

SQL> insert into test_table1 values ('done', 14, 200);

1 row created.

SQL> insert into test_table1 values ('done', 16, 50);

1 row created.
SQL> select *
  2    from test_table1
  3  /

STATUS             MANAGER      SALES
--------------- ---------- ----------
in process              14        100
in process              15         10
in process              15         40
done                    14        200
done                    16         50

As you can see in the sample data, Manager 14 has entries for the status "in process" and "done". Manager 15 only has entries for "in process". Manager 16 only has a single entry for "done".
The result that we are after is to show for each Manager a total sales value for both statuses "in process" and "done".
When we use a regular SUM and GROUP BY:


SQL> select manager
  2       , status
  3       , sum(sales)
  4    from test_table1
  5   group by manager
  6          , status
  7   order by manager
  8          , status
  9  /

   MANAGER STATUS          SUM(SALES)
---------- --------------- ----------
        14 done                   200
        14 in process             100
        15 in process              50
        16 done                    50

we only see values for records that are actually in the table... Go Figure!

Nice results, but not exactly what we are after. We want an extra record for Manager 15 (with status "done" and sales value of 0) and an extra record for Manager 16 (with status "in process" and also a value of 0).

One way to tackle this problem (or challenge if you prefer) is to use a Partition Outer Join. As far as i know this is not ANSI-SQL, but Oracle specific syntax. Tahiti.Oracle.com calls it an "extension to the ANSI syntax".
To make this query work, we need a "table" (or inline view) which has all possible statuses. Something like


SQL> select 'done' st from dual
  2  union all
  3  select 'in process' from dual
  4  /

ST
----------
done
in process

This inline view will be outer joined to our table.
What makes a Partition Outer Join work differently from a regular Outer Join?
A regular Outer Join will show an extra single record even when a matching value is not present. In our case, this will not make a difference as the values "done" and "in process" are present in our base table.
What we want is to outer join all statuses from the inline view to our base table for each manager.
And this is exactly what the Partition Clause does. It breaks up the result set per manager. Per partition (one for Manager 14, one for Manager 15 and one for Manager 16) we want to outer join to the inline view.

Putting it all together, and here is the final result:


SQL> select manager
  2       , st
  3       , nvl (sum (sales) , 0)
  4    from test_table1 t partition by (manager)
  5   right outer
  6    join (select 'done' st from dual
  7          union all
  8          select 'in process' from dual
  9         ) sts
 10      on (t.status = sts.st)
 11   group by manager
 12          , st
 13   order by manager
 14          , st
 15  /

   MANAGER ST         NVL(SUM(SALES),0)
---------- ---------- -----------------
        14 done                     600
        14 in process               300
        15 done                       0
        15 in process               150
        16 done                     150
        16 in process                 0

6 rows selected.

Each Manager shows an entry for both statuses "done" and "in process", even when this value is not in the base table.

If -for whatever reason- you don't like RIGHT OUTER, just flip the tables around and call it a LEFT OUTER:


SQL> select manager
  2       , st
  3       , nvl (sum (sales) , 0)
  4    from (select 'done' st from dual
  5          union all
  6          select 'in process' from dual
  7         ) sts
  8    left outer
  9    join test_table1 t partition by (manager)
 10      on (t.status = sts.st)
 11   group by manager
 12          , st
 13   order by manager
 14          , st
 15  /

   MANAGER ST         NVL(SUM(SALES),0)
---------- ---------- -----------------
        14 done                     800
        14 in process               400
        15 done                       0
        15 in process               200
        16 done                     200
        16 in process                 0

6 rows selected.

Original question
Lucas Jellema on Partition Outer Join
Oracle 10g Documentation
Oracle 11g Documentation
Rob van Wijk on Interval Based Row Generation

Splitting a comma delimited string the RegExp way

2009-07-03T13:55:00.008+02:00

This is one of those recurring questions on the Oracle Forums of OTN.
How to split a comma delimited string? Of course there are several options how to tackle this problem. One of the most elegant ones, at least I think so, uses a regular expression.

Let's just look at an example


SQL> with test as  
  2  (select 'ABC,DEF,GHI,JKL,MNO' str from dual  
  3  )  
  4  select regexp_substr (str, '[^,]+', 1, rownum) split  
  5    from test  
  6  connect by level <= length (regexp_replace (str, '[^,]+'))  + 1
  7  /  

SPLIT
---------------------------------------------------------------------
ABC
DEF
GHI
JKL
MNO

The first part creates some test data using the WITH clause (aka Subquery Factoring). The actual query with the regular expression starts on line 4.
The expression is


regexp_substr (str, '[^,]+', 1, rownum)

The meaning of "[^,]+" in normal English:

Give me one or more characters which are not in the list

The list consists of all characters between the square brackets. Here the "^" (circumflex) indicates "except" or "not in". The "+" means: one or more times.

The arguments of the REGEXP_SUBSTR determine which part of the string to subtract.
The third argument to the REGEXP_SUBSTR functions tells it where to start with the regular expression. The last argument means which occurence to match.

REGEXP_SUBSTR
Multilingual Regular Expression Syntax

Analytic Function: Finding Gaps

2009-06-12T12:50:00.012+02:00

A little while ago Anton Nielsen posted a blog named "SQL for Spanned Data".

In this blog he describes a challenging query involving a table which stores start and end dates. The challenge as stated by Anton:

The challenge was to create a sql statement to only return contiguous spans by division.

To illustrate what is required:


Id, Division, Start_date, End_date
10 1 09-JUN-2009 10-JUN-2009
11 1 11-JUN-2009 12-JUN-2009
12 1 13-JUN-2009 14-JUN-2009 -- Note the following row is not contiguous
14 1 17-JUN-2009 18-JUN-2009
15 1 19-JUN-2009 20-JUN-2009

The final result will be


Division, Start_date, End_date
1 09-JUN-2009 14-JUN-2009
1 17-JUN-2009 20-JUN-2009

In this post, I will use Analytic Functions to find contiguous spans. As Anton already provided the DDL for this blog, I will not be repeating that here. So, if you want to follow along grab the DDL from his blog and join the fun.

The logic that we are using, is like this

compare the current start date with the previous end date

if the difference between these dates is one day, we have a contiguous span

if the difference is anything else (greater than one day or NULL) then we start a new group of spans

Now that we have a list of numbers (1 and 0) we do a running total, each span group will have it's own number

Get the first and last day of each span group

To compare the current start date with the previous end date, we will use the LAG function. With the LAG function you can "look back" in your result set.
Let's have a look at the effect of this function


SQL> select id
  2       , start_date
  3       , end_date
  4       , lag (end_date) over (partition by division
  5                                  order by start_date
  6                             )
  7    from spantest
  8   where start_date between to_date ('01-06-2009', 'dd-mm-yyyy')
  9                        and to_date ('20-06-2009', 'dd-mm-yyyy')
 10   and division = 1
 11   order by start_date
 12  /

        ID START_DAT END_DATE  LAG(END_D
---------- --------- --------- ---------
     73383 02-JUN-09 03-JUN-09
     73384 04-JUN-09 05-JUN-09 03-JUN-09
     73385 06-JUN-09 07-JUN-09 05-JUN-09
     73386 08-JUN-09 09-JUN-09 07-JUN-09
     73387 10-JUN-09 11-JUN-09 09-JUN-09
     73388 12-JUN-09 13-JUN-09 11-JUN-09
     73389 14-JUN-09 15-JUN-09 13-JUN-09
     73391 18-JUN-09 19-JUN-09 15-JUN-09
     73392 20-JUN-09 21-JUN-09 19-JUN-09

9 rows selected.

The last column is the end date of the previous record. This makes it easy to implement steps 2 and 3. For this we will use a CASE statement


SQL> select id
  2       , start_date
  3       , end_date
  4       , case
  5          when start_date - 
  6             lag (end_date) over (partition by division
  7                                      order by start_date
  8                                 ) = 1
  9          then 0
 10          else 1
 11         end span_group
 12    from spantest
 13   where start_date between to_date ('01-06-2009', 'dd-mm-yyyy')
 14                        and to_date ('20-06-2009', 'dd-mm-yyyy')
 15   and division = 1
 16   order by start_date
 17  /

        ID START_DAT END_DATE  SPAN_GROUP
---------- --------- --------- ----------
     73383 02-JUN-09 03-JUN-09          1
     73384 04-JUN-09 05-JUN-09          0
     73385 06-JUN-09 07-JUN-09          0
     73386 08-JUN-09 09-JUN-09          0
     73387 10-JUN-09 11-JUN-09          0
     73388 12-JUN-09 13-JUN-09          0
     73389 14-JUN-09 15-JUN-09          0
     73391 18-JUN-09 19-JUN-09          1
     73392 20-JUN-09 21-JUN-09          0

9 rows selected.

The last column (named Span_Group) now contains a list of 1 and 0. The "1" indicate the start of a new Span Group. This sample set therefore contains two Span Groups.
Using the Running Total technique, we can more clearly identify the Span Groups. Because it is not possible to nest analytic functions, we push the query we build so far into an inline view. Then we can use the SUM () OVER () on the Span Groups we created earlier.


SQL> select id
  2       , start_date
  3       , end_date
  4       , sum (span_group) over (partition by division
  5                                    order by start_date
  6                               ) span_grps
  7    from (
  8        select id
  9           , division
 10           , start_date
 11           , end_date
 12           , case
 13              when start_date - 
 14                 lag (end_date) over (partition by division
 15                                            order by start_date
 16                                      ) = 1
 17              then 0
 18              else 1
 19              end span_group
 20        from spantest
 21        where start_date between to_date ('01-06-2009', 'dd-mm-yyyy')
 22                             and to_date ('20-06-2009', 'dd-mm-yyyy')
 23        and division = 1
 24        )
 25   order by start_date
 26  /

        ID START_DAT END_DATE   SPAN_GRPS
---------- --------- --------- ----------
     73383 02-JUN-09 03-JUN-09          1
     73384 04-JUN-09 05-JUN-09          1
     73385 06-JUN-09 07-JUN-09          1
     73386 08-JUN-09 09-JUN-09          1
     73387 10-JUN-09 11-JUN-09          1
     73388 12-JUN-09 13-JUN-09          1
     73389 14-JUN-09 15-JUN-09          1
     73391 18-JUN-09 19-JUN-09          2
     73392 20-JUN-09 21-JUN-09          2

9 rows selected.

The last column in the result set (named SPAN_GRPS) now clearly identifies the two groups.

The final thing we need to do is retrieve the earliest start date and the latest end date, a simple aggregate will suffice


SQL> select division
  2       , min (start_date) start_date
  3       , max (end_date)   end_date
  4    from (
  5     select id
  6        , division
  7        , start_date
  8        , end_date
  9        , sum (span_group) over (partition by division
 10                                      order by start_date
 11                                ) span_grps
 12     from (
 13           select id
 14              , division
 15              , start_date
 16              , end_date
 17              , case
 18                 when start_date - 
 19                    lag (end_date) over (partition by division
 20                                               order by start_date
 21                                         ) = 1
 22                 then 0
 23                 else 1
 24                 end span_group
 25           from spantest
 26           where start_date between to_date ('01-06-2009', 'dd-mm-yyyy')
 27                                and to_date ('20-06-2009', 'dd-mm-yyyy')
 28           and division = 1
 29           )
 30     )
 31  group by division, span_grps
 32  /

  DIVISION START_DAT END_DATE
---------- --------- ---------
         1 02-JUN-09 15-JUN-09
         1 18-JUN-09 21-JUN-09

Personally I think that Analytic Functions are a lot easier to understand than the CONNECT BY query. Just out of curiosity I ran both Queries with Autotrace on, and here are the Statistics on the queries:


Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
        248  consistent gets
          0  physical reads
          0  redo size
     112543  bytes sent via SQL*Net to client
       3164  bytes received via SQL*Net from client
        255  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       3800  rows processed

The CONNECT BY Query showed these statistics:


Statistics
---------------------------------------------------
          3  recursive calls
        233  db block gets
       3882  consistent gets
        224  physical reads
        692  redo size
     112543  bytes sent via SQL*Net to client
       3164  bytes received via SQL*Net from client
        255  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
       3800  rows processed

And yes, I did run the latter query a few times to reduce the recursive calls (the first time around 161 recursive calls)

Enough said,.. Another case to show the power of Analytic Functions.
Final Note:
The results which I got from my query are the same as the results from the first query in the original post.
The query, which Anton labeled "Don't run this"... well, I did run it and got these results for the Division 1 in June:


1 02-JUN-09 15-JUN-09
1 04-JUN-09 15-JUN-09
1 06-JUN-09 15-JUN-09
1 08-JUN-09 15-JUN-09
1 10-JUN-09 15-JUN-09
1 12-JUN-09 15-JUN-09
1 14-JUN-09 15-JUN-09

And I must admit, I don't understand these results... They are completely different than my query and Anton's first query. Maybe I don't understand the original requirements,...

SQL Developer: Backspace not working properly

2009-06-11T12:46:00.007+02:00

Nowadays I use SQL Developer on a daily basis, and I like it more and more. But every now and then something rears its ugly head, and that can be annoying.

Just this morning I had one of those encounters. The "Backspace"-key stopped working. Not only the backspace, but DELETE, ENTER, navigation with the arrow keys...

I tried re-installing SQL Developer, reboot the computer... no success.

"Google is your friend" at times like these. One of the first hits I came across was this blog post and that solved my problem.

The only thing you need to do is go from the menu "Tools -> Preferences" and in the Preferences window to "Accelerators", push the button "Load Preset" and pick the Default. And that's it.

Now it works like a charm again.

ODTUG Sneak Preview, the dressed rehearsal

2009-05-26T13:17:00.005+02:00

On Monday, June 15 there will be a sneak preview of the upcoming ODTUG Kaleidoscope Conference. Some of the European speakers (that is Belgian and Dutch) will be doing their presentation. I'm just guessing here, but I can imagine that the presentation will be in Dutch.

The presentation that will take place are:

Aino Andriessen (AMIS Services) - ADF Development: More tales from the Trenches

Lonneke Dikmans (Approach Alliance) - Top Ten Tips: Best Practices for Designing Services, Events, and Business Processes

Olivier Dupont (iAdvise) – APEX at the Belgium airport

Dimitri Gielis (APEX Evangelists) – Mastering an APEX page

Roel Hartman (Logica) – How to integrate APEX and Oracle Forms?

Lucas Jellema (AMIS Services) - Truth and Dare—The Story of How an Oracle Classic Stronghold Successfully Embraced SOA

Toon Koppelaars – Fat databases: A layered approach

Ronald van Luttikhuizen (Approach Alliance) - Customer case: Implementing SOA in a database-centric environment

Alex Nuijten (AMIS Services) – SQL Holmes – The case of the missing performance

Last year we did a similar Sneak Preview which was a great success. If you want to attend then register early, seating is limited. Oh, and did I already mention that this session is free?
More information and registration can be found in the Agenda via the AMIS homepage.

Copy and Paste: Clipboard Items

2009-04-21T11:32:00.001+02:00

By accident I discovered something in SQL Developer. Another one of those things I need to remember for future reference. Hope you can use this feature too.

You know you can use CTRL + C for copying and CTRL + V for pasting. Nothing new there, but what is really neat is that you can use SHIFT + CTRL + V to see the contents of all your clipboard items.

Because I needed to do a lot of copying today, this came in really handy.

Oracle Text: diacritic search

2009-04-06T11:45:00.001+02:00

For the client where I'm currently working, the need arose that we needed to find lastnames regardless of diacritical characters. In Dutch, we have lastnames (and firstname too) where characters like ü, ä, ö, é occur.

The "usual" way to handle this was to add a column to the table, and store the name there without the diacritical characters. Meaning the name "müller" would be in one column, while another column would contain the name "muller", the same letters but without the double dots (called a trema in Dutch) over the "u". A common complaint with this approach, is that when you query with the double dots, you won't be able to find what you are looking for.
There is also the possibility of using Oracle Text to handle this. In the past I have been to a presentation on Oracle Text, but never used it before. It scared me in a certain way. I thought it would be quite complicated to use it, but it turned out to be real easy.

Let's start with the table


create table text_test
(name varchar2(255)
);

insert into text_test values ('muller');
insert into text_test values ('müller');
insert into text_test values ('MULLER');
insert into text_test values ('MÜLLER');
insert into text_test values ('mueller');
insert into text_test values ('MUELLER');
--
insert into text_test values ('möller');
insert into text_test values ('moller');
--
insert into text_test values ('mäller');
insert into text_test values ('maller');
--
insert into text_test values ('Médar');

commit;

In this table are some variations of "Muller", upper- and lowercase and with and without the diacritical character.
In order to use Oracle Text to search for the name regardless of diacritic, we need to create a Custom Lexer. This custom lexer is needed, because we need to change the Base Letter setting. From the documentation:

With base-letter conversions enabled, letters with umlauts, acute accents, cedillas, and the like are converted to their basic forms for indexing, so fiancé is indexed both as fiancé and as fiance, and a query of fiancé returns documents containing either form.

And this is exactly what we're after.

To be able to search using the Base Letter conversion, as described in the quote from the ducmentation, we need to create a Preference.
To change the setting of the Base Letter, the default is NO, we to set this attribute to YES.


begin
   ctxsys.ctx_ddl.create_preference ('cust_lexer','BASIC_LEXER');
   ctxsys.ctx_ddl.set_attribute ('cust_lexer','base_letter','YES'); -- removes diacritics
end;
/

In the above code, we create a preference, called CUST_LEXER. Because the main language will be Dutch, I use the BASIC_LEXER. More information regarding this in the Oracle documentation.
The attribute that I want to override is the BASE_LETTER, so set that attribute to YES. And that should take care of it.
Now the only thing to do is create the Oracle Text index and specify that we want to use our preference set.


CREATE INDEX text_test_idx ON text_test(name) INDEXTYPE IS CTXSYS.CONTEXT
parameters ('LEXER cust_lexer');

Now that we are all set, let's run a query and verify the results.


SQL> select name
  2    from text_test
  3   where contains (name, 'muller') > 0
  4  ;

NAME
-----------------------------------------
müller
MULLER
muller
MÜLLER

And there you have it, it works as advertised.

While writing this blog, a colleague of mine pointed out that in Germany it is accepted to spell the name "müller" as "mueller". Oracle Text can even handle that.
Add this to your preference set, and you're good to go.


ctxsys.ctx_ddl.set_attribute ('cust_lexer','alternate_spelling','GERMAN');

With a little demo:


SQL> select name
  2    from text_test
  3   where contains (name, 'muller') > 0
  4  ;

NAME
-----------------------------------------
muller
müller
MULLER
MÜLLER
mueller
MUELLER

This also works when searching for "Mueller"


SQL> select name
  2    from text_test
  3   where contains (name, 'mueller') > 0
  4  ;

NAME
-----------------------------------------
muller
müller
MULLER
MÜLLER
mueller
MUELLER

The problem with Oracle Text used to be the synchronization of the Text indexes with DML actions. In the old days, you needed to take care of this yourself. Schedule a "make sure the Text index is updated to be in sync with the table". This could mean that after you added a name like "Désiré" to your table, you wouldn't be able to find because it wasn't in the text index.
Starting with Oracle 10g (release 2) you can indicate that the index needs to be synchronized during a COMMIT. Just specify it with the creation of the index, and that's it.


CREATE INDEX text_test_idx ON text_test(name) INDEXTYPE IS CTXSYS.CONTEXT
parameters ('LEXER cust_lexer SYNC (ON COMMIT)');

This Oracle Text is really cool stuff, and this is just the beginning. The Oracle Documentation is a great source of information. A link is included at the bottom.

Finally the cleanup:


drop table text_test cascade constraints
/
begin
   ctxsys.ctx_ddl.drop_preference ('cust_lexer');
end;
/

Oracle Text Documentation

Display Images with SQL Developer

2009-03-18T08:20:00.010+01:00

Images can be stored as a BLOB in the database. SQL Developer can show them as well.

Step 1) Create a Table to store the image in

create table t
(img blob);

Step 2) Upload an Image

Choose an image from your file system, and commit your changes.

Step 3) On the "Data" tab, click the "..." button with the BLOB column.

Step 4) In the dialog box, check the "View as Image" box

And that's it... Looks a lot better than SQL*Plus would display images :)

SQL> select img
  2    from t
  3  /
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL>

Debugging Associative Arrays

2009-03-13T16:23:00.014+01:00

My favorite development tool is PL/SQL Developer by Allround Automations. The Debugger included with this tool is great, I really like it… but… not for Associative Arrays.
Let’s take a look at some code we want to debug


type emp_rt is record
(ename    emp.ename%type
,hiredate emp.hiredate%type
,sal      emp.sal%type
);
type emp_tt is table of emp_rt
index by pls_integer;

First we declare a Record, based on some of the columns of the infamous EMP table. Next we declare the Associative Array based on the Record structure.
Then we need a (packaged) procedure, which we are going to debug. The type declaration and the procedure are both in a Package, of course.


procedure test
is
emps emp_tt;
idx  pls_integer;
begin
select ename
    , hiredate
    , sal
 bulk collect
 into emps
 from emp
;
idx := emps.first;
while idx is not null
loop
  dbms_output.put_line ('Employee: '||emps(idx).ename);
  idx := emps.next(idx);
end loop;
end test;

PL/SQL Developer has a Test Window, where you can run an anonymous block. It is possible to call this window whenever you feel like, no need to be in the context of a Package like in SQL Developer. Nor does the package need a breakpoint. And this is a good thing, 'cause most of time I forget to set breakpoints.

When you are stepping through the code and you want to see the content of the Associative Array, you will get the message:

And that's too bad.

SQL Developer to the Rescue

SQL Developer, Oracle's free IDE, also has a Debugger. And what is really nice about this debugger, is that you can view the content of the Associative Array.

Set a Breakpoint in the procedure (this can only be done when you are in edit mode)
Compile the Package in Debug (with the black compile button
Choose Run --> Debug from the menu

Run the procedure from the Debug window
Execution halts at the Breakpoint

You can look on the "Smart Data" tab to see the content of the Associative Array.

Simply Brilliant! Great Job, Sue!

Overloading Functions that Return Boolean

2009-03-04T15:22:00.006+01:00

When you have a function that returns a Boolean, you know that you can't use it in SQL. SQL just doesn't have a Boolean datatype.

Say you have a function like the following:


function ftest (p_param1 in varchar2
               ,p_param2 in varchar2
               )
   return boolean;

It is easy to overload the function, provided you placed it in a package, to return a datatype which is compatible with SQL.
All too often an overloading would look like


if ftest (param1, param2)
then
   return 1;
else
   return 0;
end if;

Can you spot the flaw in this?
The function will return a zero when the original function returns FALSE or NULL.

A better way to overload is to use SYS.DIUTIL to do the job


   return sys.diutil.bool_to_int (ftest (param1, param2));

This function will return a 1, 0 or NULL.


SQL> begin
  2     dbms_output.put_line ('True : '||sys.diutil.bool_to_int (true));
  3     dbms_output.put_line ('False: '||sys.diutil.bool_to_int (false));
  4     dbms_output.put_line ('NULL : '||sys.diutil.bool_to_int (null));
  5  end;
  6  /
True : 1
False: 0
NULL :

And there is also a "reverse" function, to turn an integer into a Boolean


SQL> begin
  2     if sys.diutil.int_to_bool (1)
  3     then
  4        dbms_output.put_line ('True');
  5     end if;
  6     if not sys.diutil.int_to_bool (0)
  7     then
  8        dbms_output.put_line ('False');
  9     end if;
 10     if sys.diutil.int_to_bool (null) is null
 11     then
 12        dbms_output.put_line ('Null');
 13     end if;
 14  end;
 15  /
True
False
Null

Value Error and Invalid Number

2009-02-06T13:32:00.007+01:00

Currently I'm in the process on reviewing some chapters on an upcoming book... more on that in a later blog, 'cause I am not sure if I can talk about it or not.

One of the chapters is on "Exceptions" and I noticed a line from the Oracle documentation which I didn't notice before. The VALUE_ERROR (ORA-6502) and INVALID_NUMBER (ORA-1722) are predefined exception, which can be handled by name in your exception handler.
Quote from the docs:
"...In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)"

Let's first check the latter part, about the SQL statements:


SQL> select to_number ('a')
  2    from dual
  3  /
select to_number ('a')
                  *
ERROR at line 1:
ORA-01722: invalid number

Now let's check the procedural part, in PL/SQL.
Based on the description, this statement would cause a VALUE_ERROR exception


SQL> declare
  2     n number;
  3  begin
  4     n := 'a';
  5  exception
  6    when value_error
  7    then
  8      dbms_output.put_line ('Value Error');
  9  end;
 10  /
Value Error

PL/SQL procedure successfully completed.

...and it does.

But what if you would use a SQL statement in PL/SQL?
Would this also raise a VALUE_ERROR?


SQL> declare
  2    n number;
  3  begin
  4     select 'a'
  5       into n
  6       from dual
  7     ;
  8  exception
  9    when value_error
 10    then
 11      dbms_output.put_line ('Value Error');
 12  end;
 13  /
Value Error

PL/SQL procedure successfully completed.

Yes, it does. In accordance with the documentation, doing a conversion in procedural statement will raise the VALUE_ERROR exception,...
One last option, let's use the first SQL statement from this blog and use it in PL/SQL:


SQL> declare
  2    n number;
  3  begin
  4    select to_number('a')
  5      into n
  6      from dual
  7    ;
  8  exception
  9    when value_error
 10    then
 11      dbms_output.put_line ('Value Error');
 12    when invalid_number
 13    then
 14      dbms_output.put_line ('Invalid Number');
 15  end;
 16  /
Invalid Number

PL/SQL procedure successfully completed.

As you can see, it raises the INVALID_NUMBER exception. I expected that it would raise the VALUE_ERROR as well.

Tracing the statements reveals the explanation. With the implicit datatype conversion, this is the statement that is executed by the SQL engine (I added some text to the statement to identify the actual statement):


SELECT /*+ implicit_conversion */'a' 
FROM
 DUAL

and with the explicit datatype conversion, this SQL statement gets executed


SELECT /*+ explicit_conversion */ TO_NUMBER('a') 
FROM
 DUAL

Sometimes it's too easy to ignore these little things. I learned something about exceptions again.
Link:
Oracle Documentation on Predefined PL/SQL Exceptions

Business Rule: Only One Clerk per Department

2008-12-19T19:58:00.001+01:00

During the 7Up workshop, a workshop geared towards experienced developers who want to get up to speed with everything that happened in Oracle since release 7, one of the “tricks” that passes by is how to implement the Business Rule:

Only one Clerk per Department is Allowed

The way we show how to declaratively implement this Business Rule is by using a Unique Function Based Index. Every now and then someone will comment that the implementation is a hack, “'cause an Index is not meant to implement a Business Rule, it’s there to enhance performance.”
I don’t necessarily agree with this, but I do believe that Oracle 11g offers a more elegant solution (be it very similar, but is considered less of a hack).

First let’s take a look at the “hack”, with the Unique Function Based Index. Then we’ll look at the way to do the same in Oracle 11g. Lastly I will show you the similarity between the two.

Unique Function Based Index

First off, the Function Based Index. FBI’s were introduced in Oracle 9i and allow you to create an index using a function.
Especially useful when you have a query like


select *
from emp
where upper (ename) = :ename
;

When the ename column in the example above is indexed (the regular way) you can not use this index because of the UPPER function.
Indexing the ename column with the UPPER function would allow use of the index. Here is the code for this example:

create index upper_name
on emp (upper(ename))

When the index is created this way, it can be used with the first statement.

The way to implement the Business Rule “Only one Clerk per Department”:

create UNIQUE index one_clerk_per_deptno
on emp (case job
        when 'CLERK'
  then deptno
  end
)

Using a simple CASE expression only DEPTNO are used in the index when the JOB is equal to CLERK. Because it is a unique index, there can only be one CLERK in each department.

Virtual Columns

Some might call the implementation of the Business Rule in the previous section a “hack”. Instead of using indexes to increase performance, the index is used to declaratively implement a Business Rule.
Oracle 11g introduced Virtual Columns. I really like Virtual Columns more and more because of the multitude of possibilities. Using Virtual Columns to implement this Business Rule is very straight forward.
Instead of using the simple CASE expression in an index, we will use it in the definition of the Virtual Column:

alter table emp
add one_clerk_per_dept as (case
                          when job='CLERK'
     then deptno
       end
                        )

With the statement above an extra column is added to the EMP table. This Virtual Column, based on the CASE expression, will only show a value when the JOB equals CLERK.

SQL> select job
2       , one_clerk_per_dept
3    from emp
4  /

JOB       ONE_CLERK_PER_DEPT
--------- ------------------
CLERK                     20
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK                     20
CLERK                     30
ANALYST
CLERK                     10

As you can see in the resultset above, the ONE_CLERK_PER_DEPT has a value in its column when the JOB is a CLERK. Because the Business Rule says “Only one Clerk per Department” we need to make this column UNIQUE.
With this data this is not possible, because department has two CLERKS. One has to go…

SQL> update emp
2     set job = 'NOCLERK'
3   where job = 'CLERK'
4     and rownum = 1
5  /

1 row updated.

SQL> alter table emp
2    add constraint one_clerk_uk UNIQUE (one_clerk_per_dept)
3  /

Table altered.

SQL> update emp
2     set job = 'CLERK'
3   where job = 'NOCLERK'
4  /
update emp
*
ERROR at line 1:
ORA-00001: unique constraint (ALEX.ONE_CLERK_UK) violated

And now the Business Rule is implemented in a more “natural” way, without resorting to a “hack”.

The Same Difference…

Both methods implement the same Business Rule. Both implement the Business Rule in a declarative way. To some people the first method feels like a “hack” and find the second method more “natural”. But how different are they really?
Not as much as you might have suspected. When you use the first method (hacking with Function Based Indexes), the USER_IND_COLUMNS datadictionary view reveals the implementation.


SQL> select column_name
2    from user_ind_columns
3   where index_name = 'ONE_CLERK_PER_DEPT'
4  /

COLUMN_NAME
---------------------------------------------------------
SYS_NC00010$

SQL> select job
2       , SYS_NC00010$
3    from emp
4  /

JOB       SYS_NC00010$
--------- ------------
NOCLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK               20
CLERK               30
ANALYST
CLERK               10

A hidden column is added to the table with a very obscure name. Apparently a Function Based Index is pretty similar to a Virtual Column.

Business Rule: Only use Active Records

2008-12-12T15:02:00.004+01:00

The Business Rule that needed to implemented:

Only "active" records can be used in other tables.

Before Oracle 11g, one way to implement this Business Rule was to Materialized View with a Check Constraint defined on it. However In Oracle 11g you can implement this Business Rule a lot simpler by using a Virtual Column and a Foreign Key.

First let set up the tables that we are going to use in this example:

create table lookup
(id number primary key
,code varchar2(10)
,value varchar2(100)
,ind_active varchar2(1)
);

alter table lookup
add constraint lookup_ind_chk check (ind_active in ('Y', 'N'))
/

This table has the ind_active column which dictates whether the record in this table may be used in other tables. "Y" indicates that we can use it in other tables, "N" indicates that you can not use it (anymore).

Next we need another table to reference our Lookup Table:

create table t
(id number primary key
,lu_id number references lookup
);

Together with some data in both tables:

insert into lookup values (1, 'One', 'First Value', 'Y')
/
insert into lookup values (2, 'Two', 'Second Value', 'Y')
/
insert into t values (1, 1)
/
insert into t values (2, 2)
/

Now to implement the Business Rule, using the Materialized View method. Because the rule needs to be checked whenever someone wants to use a record from the Lookup table, we want to validate the rule as soon as possible. To have the Materialized View refresh when a COMMIT is issued, we need Materialized View Logs on both base tables:

create materialized view log on t with sequence, primary key including new values
/
create materialized view log on lookup with sequence, primary key including new values
/

The Materialized View definition will be

create materialized view t_lookup_mv
build immediate
refresh force on commit
as
select l.ind_active
from t
  , lookup l
where l.id = t.lu_id
/

This way we will get a record in the Materialized View whenever the T-table uses a record in our Lookup Table. The final thing we need is a Check Constraint on this Materialized View, because we only want Lookup records with the active_ind is “Y”:

alter materialized view t_lookup_mv
add constraint active_chk check (ind_active = 'Y');

Just to check that the Materialized View does what it is supposed to do:

update lookup
set ind_active = 'N'
where rownum <= 1
/
commit;

ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (ALEX.ACTIVE_CHK) violated

That seems to work. But as you can see, you need quite a lot of code to implement such a simple rule. The same Business Rule can be implemented in Oracle 11g a lot simpler, with less code.

Virtual Columns

This is the way we're going to implement the rule:

Add a Virtual Column to the Lookup table
Create a Foreign Key referencing the Virtual Column

And that's all there is to it. Let's take a look at the code. First of all the Virtual Column.

Virtual Columns are a new feature of the Oracle 11gR1 database. Using this feature you can create an extra column in the table that is based on an expression.

active_fk as (case when ind_active = 'Y' then id end) unique

The values in this Virtual Column are only shown when the IND_ACTIVE column has the value of "Y". The value that is shown is the primary key of the base table. Querying the LOOKUP table, reveals the content of the ACTIVE_FK Virtual Column:


SQL> select *
2    from lookup
3  /

     ID CODE       VALUE                I  ACTIVE_FK
---------- ---------- -------------------- - ----------
      1 One        First Value          Y          1
      2 Two        Second Value         Y          2

Changing the IND_ACTIVE column to "N" will remove the value from the ACTIVE_FK column:


SQL> update lookup
2 set ind_active = 'N'
3 where id = 2
4 /

1 row updated.

SQL> select *
2 from lookup
3 /

     ID CODE       VALUE                I  ACTIVE_FK
---------- ---------- -------------------- - ----------
      1 One        First Value          Y          1
      2 Two        Second Value         N

SQL> rollback;

Because we are going to use the Virtual Column as referenced by a Foreign Key we are going to make it UNIQUE as well.

Now for the table which uses the records from our Lookup Table:

create table t
(id number primary key
,lu_id number
);
alter table t
add constraint t_lookup_fk foreign key (lu_id) references lookup (active_fk)
/

The foreign key is this case is not on the primary key, but on the Virtual Column instead.

Let's add some data to the table and try it out.


insert into t values (1, 1);
insert into t values (2, 2);

update lookup
set ind_active = 'N'
where rownum <= 1
/
update lookup
 * ERROR at line 1: ORA-02292: integrity constraint (ALEX.T_LOOKUP_FK) violated - child record found

And there you have it. It's not possible to use deactivated records anymore.

Check when you COMMIT?

Note the difference though, the Materialized View method is checked when you commit your transaction. The Virtual Column method checks it immediately. If you want the Virtual Column to have similar to the Materialized View method, change the foreign key:

alter table t
add constraint t_lookup_fk foreign key (lu_id) references lookup (active_fk)
deferrable initially deferred
/

Note the last sentence, deferrable initially deferred, this tells Oracle to not validate the constraint immediately but to postpone it until the transaction is ended.