data_precision and data_scale Missing from Oracle's all_tab_columns

Question

I need to know the data_precision and data_scale values for the fields in a view in Oracle 12c. I am not the owner of the view and I do not have access to the underlying tables.

When I query all_tab_columns for this view, all of the view's columns report null for data_precision and data_scale.

Yet, I can see in the view that many fields are numbers such as number(12,6) or whatever. For example:

Yet we can see that all_tab_columns describes this column as:

(If the picture's are not clear, we have a column with values like 0.0003, yet all_tab_columns is null for data_precision and data_scale.

Basically, all_tab_columns would lead me to believe that the field is a number(22), which is wrong.

How can I find out the actual data-type of the columns in this view? Why are integer types indistinguishable from decimal types in all_tab_columns?

Note: I tried to reproduce this with a trivial example, but instead of reproducing the issue, I got the expected/desired behavior.

e.g., this works fine:

create table foo
(
    bar number(19,6)
)
;

create view baz
as
select bar from foo
;

select
*
from all_tab_columns
where table_name in ('FOO', 'BAZ')
;

OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE_MOD,DATA_TYPE_OWNER,DATA_LENGTH,DATA_PRECISION,DATA_SCALE
LD_PRICING,BAZ,BAR,NUMBER,,,22,19,6
LD_PRICING,FOO,BAR,NUMBER,,,22,19,6

Answer 1

I do not see NUMBER(12,6) anywhere in your output. That column is simply a NUMBER without precision specificed.

SQL> create table t1(c1 number);

Table created.

SQL> insert into t1 values (0.00025);

1 row created.

SQL> select * from t1;

        C1
----------
    .00025

SQL> select data_length, data_precision, data_scale from  all_tab_columns where table_name = 'T1' and column_name = 'C1';

DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
         22

SQL>

Specifying NUMBER is not the same as NUMBER(22,0) or NUMBER(22):

SQL> create table t2(c1 number(22, 0));

Table created.

SQL> select data_length, data_precision, data_scale from  all_tab_columns where table_name = 'T2' and column_name = 'C1';

DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
         22             22          0

SQL> insert into t2 values (0.00025);

1 row created.

SQL> select * from t2;

        C1
----------
         0

SQL>  create table t3(c1 number(22));

Table created.

SQL>  select data_length, data_precision, data_scale from  all_tab_columns where table_name = 'T3' and column_name = 'C1';

DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
         22             22          0

SQL>