DECOMPOSE this: when what you see is not exactly what you think you see

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I was trying to call function DECOMPOSE while investigating a strange problem I had with some Unicode strings that could not be properly indexed by Oracle Text and, amazingly, I found out that most pages where usage examples of such DECOMPOSE function are given, most likely never attempted to go beyond its default form as given by SQL Reference and I'd dare to say that they didn't even try to execute this function once.

Even more amazing is the fact that the syntax diagrams of DECOMPOSE in *all* versions up until 11.2 are wrong.

But let's start from the beginning.
As per SQL reference example, if you call this

select DECOMPOSE('Crónicas') d from dual;

you should get this result:

D

--------- 

Cro´nicas

Unfortunately what you see in SQL Developer  (or Apex SQL Workshop) is instead:

D

-------- 
Crónicas

So, first of all, apparently either this function doesn't work as advertised (my db is AL32UTF8) or the functional description lacks some important detail.
After various attempts I realized that the "client" software must be playing a hidden role in this messy situation.
If, instead of displaying the resulting string, I get the length of the original string and its decomposed equivalent, I see that the latter is one character longer.

select length('Crónicas') l0

     , length(DECOMPOSE('Crónicas')) l1

  from dual; 

        L0         L1
---------- ----------
         8          9 

Indeed, if I get a hex dump, I can spot the difference:

select rawtohex('Crónicas') s0
     , rawtohex(DECOMPOSE('Crónicas')) s1

  from dual;

S0                 S1                 
------------------ --------------------
4372C3B36E69636173 43726FCC816E69636173 

The decomposed string contains two characters:

1. 6F is "o"
2. CC81 is a special Unicode character called "Combining acute accent"

So, it turns out that the result displayed in the Oracle SQL Reference is fictitious because the operating system may kick in and recombine the two distinct characters in order to display them according to the Unicode standard and you can't visually spot the difference.

Unfortunately, Oracle Text in 10.2 doesn't seem to cope with these combined accented characters, so you might be looking at two apparently identical strings on your monitor that are the result of distinct unicode character combinations which makes somewhat difficult to understand why one was found by a search query, while the latter wasn't.

But this was just the first part of the intriguing story...

As I already said, DECOMPOSE's syntax diagrams are dead wrong.
According to them, you should be able to call this functions also in the following forms:

select DECOMPOSE('Crónicas' CANONICAL) d from dual;

but all you get is the infamous ORA-00907:

ORA-00907: missing right parenthesis

According to the book "PLSQL Programming", you should be able to call DECOMPOSE as follows:

select DECOMPOSE('Crónicas', CANONICAL) from dual; -- added the comma

but even in this case you hit another error:

ORA-00904: "CANONICAL": invalid identifier

It turns out that the correct form is instead:

select DECOMPOSE('Crónicas','CANONICAL') from dual;

select DECOMPOSE('Crónicas','COMPATIBILITY') from dual;

I was unable to quickly find an example returning different results for the two modes, may be I'll find them later.

As a final note, if you specify a wrong literal as the second parameter, you'll get the following error:

ORA-12702: invalid NLS parameter string used in SQL function

ORA-00907: missing right parenthesis

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

There are at least a couple of situations where you may come across this syntax error message (see update at the bottom):

1. a trivial mistake, something that you could easily avoid by using SQLDeveloper's editor, that comes with a cool matching parentheses visual checking feature;
2. as a result of an elusive forbidden syntax form that is not clearly documented in the official books (through version 11.1 at time of writing).

Let's forget the former case and go straight to the latter case.

Don't get my example wrong, i know this is not the best way of doing this, but I'll talk about that later on:

select object_name, object_type
from user_objects
where object_type in (
select column_value
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1);

ORA-00907: missing right parenthesis

Clearly when one gets a message like this, the first reaction is probably to verify what parenthesis has been left out, but unfortunately there are no missing parentheses at all in this statement.

To cut it short, the untold syntax quirk is summarized as follows: don't use ORDER BY inside an IN subquery.

Now, one may object that indeed it doesn't make sense to use the ORDER BY inside an IN clause, which is true, because Oracle doesn't care about the row order inside an IN clause:

select object_name, object_type
from user_objects
where object_type in ('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE');

is perfectly equivalent to:

select object_name, object_type
from user_objects
where object_type in ('FUNCTION,TABLE,SYNONYM,PROCEDURE,VIEW');

Oracle may or may not process the rows in the same order, probably it will depend on which blocks it finds in the buffer cache, which can vary between the first execution and the second execution, so don't rely on swapping items in the IN clause if you want to change the order in which they are processed.

Let's go back to the original query: in the example above i used the custom function csv_to_table to convey the idea of a query with a parametric IN clause, that is a clause that is not made up of literal values but that could accept a string parameter (a comma separated string list) that could be set somewhere else.

So, if the purpose of this query was to process the rows in the USER_OBJECTS view in the specified object type order, then we would have to rewrite the query completely:

select a.object_name, a.object_type
from user_objects a, (
select column_value object_type, rownum as n
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by n) b
where a.object_type = b.object_type
order by b.n;

Note also that there are at least two ways of solving the syntax problem without touching the ORDER BY clause:

creating a view as follows:

create or replace my_object_types_v as
select column_value as object_type
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1;

and then issue:

SELECT object_name, object_type
from user_objects
where object_type in (select object_type from my_object_types_v);

or alternatively use the WITH clause:

WITH my_object_types_v as (select column_value as object_type
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1)
SELECT object_name, object_type
from user_objects
where object_type in (select object_type from my_object_types_v);

but as already remarked, neither of the two will force Oracle to process the rows of USER_OBJECTS in the given order.

Updated february 29, 2008:
This error is also returned when calling a user-defined (PL/SQL) function with named parameters inside a SQL statement:

SELECT my_function(p_input_value => 0) AS my_fn
FROM DUAL;

ORA-00907: missing right parenthesis

Named parameters are only allowed in PL/SQL programs (but not in SQL statements inside PL/SQL programs), therefore the solution is to pass parameters in positional form.

Read more about the different parameter passing options in the Oracle 10G documentation.

See message translations for ORA-00907 and search additional resources.