Oracle 12c – Missing data_precision and data_scale in all_tab_columns

oracleoracle-12c

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

Best Answer

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>

Related Solutions

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range. Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.

Understanding Oracle’s ALL_TAB_COLUMNS

Most probably there are three schemas (users) that contain that table. You need to include OWNER = 'FOOBAR' in your query to all_tab_columns (or use user_tab_columns). You can also include the OWNER column in your select list to verify this.

For VARCHAR (and other character columns) the size is stored in CHAR_LENGTH as documented in the manual. Note that you also need to check CHAR_USED to find out if the definition is in bytes or characters (VARCHAR(10 Bytes) vs. VARCHAR(10 Char)).

For numeric columns the definition is stored in DATA_SCALE and DATA_PRECISION.

This is all described in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_2103.htm#I1020277

Are you aware that you can retrieve the full definition of a table using `DBMS_METADATA using a SELECT statement:

SELECT dbms_metadata.get_ddl('TABLE', 'EMPLOYEE', 'SCOTT')
FROM dual;

Best Answer

Related Solutions

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

Understanding Oracle’s ALL_TAB_COLUMNS

Related Question