Best Wishes

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.

ODTUG Kaleidoscope 2010: Accepted Abstract

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.

Revenge of the Multibyte Characters

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.

(Non Oracle): Shocking Truth

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.

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

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

Create Users with DBMS_METADATA

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.

Importing an Excel into an Oracle database

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.

Oracle PL/SQL Programming Preview

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

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

UPDATE July 10, 2013 (especially for Erhan Sarigul): Remove the Schema from the DDL-script

create or replace function remap_schema return clob is
   -- Define local variables.
   h   number; --handle returned by OPEN
   th  number; -- handle returned by ADD_TRANSFORM
   doc clob;
begin
   -- Specify the object type.
   h := dbms_metadata.open('TABLE');
   -- Use filters to specify the particular object desired.
   dbms_metadata.set_filter(h
                           ,'SCHEMA'
                           ,'ALEX');
   dbms_metadata.set_filter(h
                           ,'NAME'
                           ,'EMP');
   -- Request that the schema name be modified.
   th := dbms_metadata.add_transform(h
                                    ,'MODIFY');
   dbms_metadata.set_remap_param(th
                                ,'REMAP_SCHEMA'
                                ,'ALEX'
                                ,null);
   -- Request that the metadata be transformed into creation DDL.
   th := dbms_metadata.add_transform(h
                                    ,'DDL');
   -- Specify that segment attributes are not to be returned.
   dbms_metadata.set_transform_param(th
                                    ,'SEGMENT_ATTRIBUTES'
                                    ,false);
   -- Fetch the object.
   doc := dbms_metadata.fetch_clob(h);
   -- Release resources.
   dbms_metadata.close(h);
   return doc;
end remap_schema;
/

select remap_schema -- dbms_metadata.get_ddl ('TABLE','EMP')
  from dual
/

Using the DBMS_METADATA API

OPP 5: The Big Fat Book on PL/SQL

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

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

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

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

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:

1. keep your own lastname: Jansen


2. use the lastname of your partner: de Vries


3. your own lastname hyphen partner lastname: Jansen - de Vries


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

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

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

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

1. compare the current start date with the previous end date


2. if the difference between these dates is one day, we have a contiguous span


3. if the difference is anything else (greater than one day or NULL) then we start a new group of spans


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


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

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

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

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

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

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

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.

1. Set a Breakpoint in the procedure (this can only be done when you are in edit mode)
   
2. Compile the Package in Debug (with the black compile button
3. Choose Run --> Debug from the menu

4. Run the procedure from the Debug window
5. 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

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

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