Oracle number precision and scale both zero when case is used in select clause

Question

Given this table,

 CREATE TABLE test (
     c01  INT,
     c02  NUMBER(10,5),
     c03  NUMBER(18,10)
 );

I use OCCI (the C++ Library) to execute the following SELECT query:

 select case(c01) when 10 then c02 else c03 end from test;

In this case, the typecode I get is OCCI_SQLT_NUM but the precision and scale both are 0. What does that (0 precision and scale) mean? Is this a bug?

Without knowing the precision/scale, the type-safety, semantics and the correctness of the program is at the stake, as it is not possible to decide whether to treat it as integer or floating-point.

In other words, what is the type of the CASE(x) WHEN y THEN a ELSE b expression? Can a be INT and b be CHAR[32]? I think no. So how is the type computed?

Answer 1

In a CASE expression, the data types of the expressions in the THEN and ELSE clauses must be compatible. So you can't have a NUMBER in the THEN clause and then a DATE or VARCHAR2 in the ELSE clause.

However, precision and scale are not part of the data type. It is better to think of precision and scale as constraints on the values that are allowed in a table. NUMBER(10,2) is the same data type as NUMBER, but with the constraint that there should be no more than ten total digits, including the two reserved for the decimal part, and no more than two after the decimal point. (Then, inserting 5.333 will still work - but only because Oracle will automatically and without warning round this number to fit into the column... but if you gave it the input 123456123456, there's no "rounding" that will make it fit so the insert will fail). The data type of your CASE expression is simply NUMBER. (Incidentally, in Oracle SQL, even INT is a constraint - Oracle DOES NOT DO integer arithmetic!!)

NUMBER(0,0) does not exist in Oracle; if you try to CREATE TABLE test (col NUMBER(0,0)), it will fail. The error message will tell you that the precision (the first number) must be between 1 and 38. So whatever produced NUMBER(0,0) sounds like a bug.