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

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.

Splitting a comma delimited string the RegExp way, Part Two

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

ODTUG KScope 2011: looking back

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.

Oracle 11g Express Edition bug?

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> 

ODTUG KScope Preview bij AMIS

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

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?

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;