0

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:

enter image description here

Yet we can see that all_tab_columns describes this column as:

enter image description here

(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
  • Just because the numbers look like they were created with a precision does not mean that they actually were. You can still store 0.0000025 in a number(18). A quick google suggests that Tom Kate agrees that this is entirely normal – Philᵀᴹ Apr 26 at 6:57
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>
  • Thank you, this is helpful. I was misunderstanding Oracle’s number type. – Matthew James Briggs Apr 26 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.