Oracle查询所有_tab_列。data_默认值（长型）

Question

我已运行此查询：

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';

但它在列DATA\u DEFAULT中抛出一个错误：

ORA-00932：不一致的数据类型：预期字符长
00932。00000-“不一致的数据类型：预期%s得到%s”

我怎样才能解决这个问题？

谢谢

Answer 1

这里有一些代码如何打印你的长。你可以随心所欲地改变它。

set serveroutput on
declare
  cursor c1 is
  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
  from all_Tab_columns where table_name='ENTITIES' and default_length is not null;
v_long long;
v_varchar2 varchar2(4000);
begin
  for loop1 in c1
  loop
    v_long := loop1.DATA_DEFAULT;
    v_varchar2 := substr(v_long,1,4000);
    dbms_output.put_line(v_varchar2); 
  end loop;
end;

Answer 2

您不能对LONG做任何事情。Oracle仍然在数据字典中使用它们的PITA。

您可以使用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';

从12.1开始，您可以内联编写自己的查找函数：

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%'
/

当然，也可以制作一个独立的函数或包函数来做同样的事情。