Oracle doesn't inherit number scale and precision for columns in a view consumed by Kafka Connect

Question

I want to force a column from my view to have an exact scale and precision. My current preset is as follows:

create or replace view v1
as
select cast(1 as number(1,0))   as some_val,
       'X'                      as join_val
from dual
/
create or replace view v2
as
select v1.some_val
from dual
left join v1 on dual.dummy = v1.join_val
/

In this example I expect v2.some_val column to be NUMBER(1,0). But when querying dictionary I get just NUMBER:

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 12 13:02:59 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Mon Jul 12 2021 12:59:02 +03:00

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> column data_precision format 99;
SQL> column data_scale format 99;
SQL> column table_name format a5;
SQL> column column_name format a10;
SQL> select d.data_precision, d.data_scale, d.table_name, d.column_name
  2  from dba_tab_cols d
  3  where d.owner = 'STAT_INT'
  4  and d.table_name in ('V1', 'V2');

DATA_PRECISION DATA_SCALE TABLE COLUMN_NAM
-------------- ---------- ----- ----------
             1          0 V1    SOME_VAL
                          V1    JOIN_VAL
                          V2    SOME_VAL

SQL>

I've changed join condition in V2 view (replaced join predicate to always true one):

create or replace view stat_int.v2
as
select v1.some_val
from dual
left join v1 on 1=1
/

And output changed:

SQL> select d.data_precision, d.data_scale, d.table_name, d.column_name
  2  from dba_tab_cols d
  3  where d.owner = 'STAT_INT'
  4  and d.table_name in ('V1', 'V2');

DATA_PRECISION DATA_SCALE TABLE COLUMN_NAM
-------------- ---------- ----- ----------
             1          0 V1    SOME_VAL
                          V1    JOIN_VAL
             1          0 V2    SOME_VAL

SQL>

I don't get why Oracle decides not to inherit scale and precision from parent table column because even if left join doesn't work it will be still NULL values that can be of type NUMBER(1,0).

Of course, when I cast some_val to NUMBER(1,0) in V2 view it works fine but in my case I keep this logic inside of V1 view and it is used by a great number of dependent views (V2 is one of them) which I don't want to change in order to just cast to an needed type.

This scale/precision requirement comes from Kafka Connect which transforms plain value 0 or 1 to something like OxdP6jOQnr/o+UfE4q0zr5p7lMvK0Fh9N+fohbLmyY9Nt9u5ZoAAAAAAAAAAAAAAAAAAAAA= if an appropriate scale isn't set.

Any suggestions are appreciated. Thanks!

user_4808122 · Answer

The problem is not that NULL can not be casted as number(1,0).

The problem is that the view can return two possible data types,

number(1,0) in case the join will return a value or

null casted as number in case the join fails.

So here apply the Conversion Rules documented here

When manipulating numeric values, Oracle usually adjusts precision and scale to allow for maximum capacity.

Which leads in end effect in loss of the precision and scale.

You may observe a similar effect in a UNION ALL query, which suffers the same loss.

create or replace view v2
as 
select v1.some_val from v1
union  all
select   null  from dual

So I'd extremely enjoy to be wrong here, but I assume, you'll have to step down to an inner join or additionaly cast the value in the final view.

Oracle doesn't inherit number scale and precision for columns in a view consumed by Kafka Connect

1 Answers