Oracle query all_tab_columns.data_default (type LONG)

I have run this query:

SELECT
    OWNER,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    (CASE
        WHEN DATA_PRECISION IS NULL THEN 0
        ELSE DATA_PRECISION
     END) DATA_PRECISION,
    (CASE
        WHEN DATA_SCALE IS NULL THEN 0
        ELSE DATA_SCALE
     END) DATA_SCALE,
    NULLABLE,
    COLUMN_ID
    DEFAULT_LENGTH,
    DATA_DEFAULT,
    (CASE
        WHEN DATA_DEFAULT IS NULL THEN '0'
        ELSE DATA_DEFAULT
     END) DATA_DEFAULT1
FROM 
    all_tab_columns
WHERE 
    table_name LIKE 'TABLE1';

But it throws an error at column DATA_DEFAULT:

ORA-00932: inconsistent datatypes: expected CHAR got LONG
00932. 00000 – “inconsistent datatypes: expected %s got %s”

How can I fix that?

Thanks!

Answer

You can’t do anything with a LONG. It’s a PITA that Oracle still use them in the data dictionary.

You can use XML:

select owner
     , table_name
     , column_name
     , data_type
     , data_length
     , case
           when data_precision is null then 0
           else data_precision
       end data_precision
     , case
           when data_scale is null then 0
           else data_scale
       end data_scale
     , nullable
     , column_id
     , default_length
     , case
           when default_length is null then '0'
           else
               extractvalue
               ( dbms_xmlgen.getxmltype
                 ( 'select data_default from user_tab_columns where table_name = ''' || c.table_name || ''' and column_name = ''' || c.column_name || '''' )
               , '//text()' )
       end as data_default
from   all_tab_columns c
where  table_name like 'TABLE1';

From 12.1 you can write your own lookup function inline:

with
     function get_default(tab varchar2, col varchar2) return varchar2
     as
         dflt varchar2(4000);
     begin
         select c.data_default into dflt
         from   user_tab_columns c
         where  c.table_name = upper(tab)
         and    c.column_name = upper(col);
         
         return dflt;
     end get_default;
select owner
     , table_name
     , column_name
     , data_type
     , data_length
     , case
           when data_precision is null then 0
           else data_precision
       end data_precision
     , case
           when data_scale is null then 0
           else data_scale
       end data_scale
     , nullable
     , column_id
     , default_length
     , get_default(c.table_name, c.column_name) as data_default
from   all_tab_columns c
where  table_name like 'TABLE1%'
/

Or of course make a standalone function or package function that does the same thing.