As described in the documentation, long column cannot be part of any expression in a standalone select statement.
One of possible ways to overcome this restriction in SQL statement is to wrap an access to such table with dbms_xmlgen functions, which will serialize long column values into XML that may be consumed by XMLTable function.
Setup:

create table t (
  id int generated always as identity,
  val int default 0
)

Any expression with long fails:

select
  cols.table_name, 
  cols.column_name, 
  'default: ' || cols.data_default AS data_by_default
from user_tab_cols cols

ORA-00932: inconsistent datatypes: expected CHAR got LONG

But it may be serialized in XML:

select
  cols.table_name, 
  cols.column_name, 
  'default: ' || cols.data_default AS data_by_default
from xmltable(
  '/ROWSET/ROW'
  passing dbms_xmlgen.getxmltype(
    'select
       table_name,
       column_name,
       data_default
     from user_tab_cols'
  )
  columns
    table_name varchar2(128),
    column_name varchar2(128),
    data_default clob
) cols

fiddle

你不能像这样处理LONG......它们不会以这种方式转换成CLOB。你必须在PL/SQL中给它们赋值。如果值的长度小于32 K，这真的很容易：

DECLARE
  var_default varchar2(32767);
BEGIN
  FOR rec_default IN (SELECT data_default
                        FROM user_tab_columns)
  LOOP
    var_default := rec_default.data_default;
    -- you now have a varchar2 version of your long value.
  END LOOP;
END;

我已经为我们的数据库编写了一组丰富的字典视图，其中一项功能就是消除LONG。下面是我的dba_tab_columns扩展：

CREATE OR REPLACE FUNCTION f_dba_tab_columns_ext(in_owner IN varchar2, in_table_name IN varchar2, in_column_Name IN varchar2)
  RETURN varchar2
AS
  var_my_feature_name feature.feature_name%TYPE := 'Enhanced Dictionary Views';
  not_enabled exception;
  var_data_default varchar2(4000);
BEGIN
  IF NOT f_feature_enabled(var_my_feature_name)
  THEN
    RAISE not_enabled;
  END IF;
  
  FOR rec_expr IN (SELECT *
                     FROM dba_tab_columns
                    WHERE owner = in_owner
                      AND table_name = in_table_name
                      AND column_name = in_column_name)
  LOOP
    var_data_default := rec_expr.data_default;
  END LOOP;
  
  RETURN var_data_default;
EXCEPTION 
  WHEN not_enabled THEN
    RAISE_APPLICATION_ERROR(-20001,'The Enhanced Dictionary Views feature is disabled');
  WHEN OTHERS THEN
    p_log_unified_error(var_my_feature_name,'in_owner = '||in_owner||', in_table_name = '||in_table_name||', in_column_Name = '||in_column_Name);
    RAISE;
END;  
/

CREATE OR REPLACE VIEW dba_tab_columns_ext
AS
SELECT owner,
        table_name,
        column_name,
        data_type,
        data_type_mod,
        data_type_owner,
        data_length,
        data_precision,
        data_scale,
        nullable,
        column_id,
        default_length,
        f_dba_tab_columns_ext(owner,table_name,column_name) AS data_default,
        num_distinct,
        low_value,
        high_value,
        cast_raw_to_display_string(low_value,data_type) low_value_display,
        cast_raw_to_display_string(high_value,data_type) high_value_display,
        density,
        num_nulls,
        num_buckets,
        last_analyzed,
        sample_size,
        character_set_name,
        char_col_decl_length,
        global_stats,
        user_stats,
        avg_col_len,
        char_length,
        char_used,
        v80_fmt_image,
        data_upgraded,
        histogram,
        default_on_null,
        identity_column,
        sensitive_column,
        evaluation_edition,
        unusable_before,
        unusable_beginning,
        collation
  FROM dba_tab_columns
 ORDER BY owner,table_name,column_id

您明白了，但是，如果您必须使用dba_views.text或dba_triggers.trigger_body或其他超过4000个字符的LONG来执行此操作（如果在SQL中需要）或32767字符（如果您只在PL/SQL中需要它），那么您将不幸地不得不使用dbms_sql.column_value_long过程，这将需要对dbms_sql游标进行相当冗长和复杂的配置，并在dbms_sql.column_value_long上进行迭代，直到提取完整的值，并在执行时附加到临时CLOB。但是，对于dba_columns.data_default、dba_tab_partitions.high_value等较短的LONG，不需要这样做。不要尝试将这些值放入CLOB，只需使用varchar 2（4000）输出即可。

您还可以使用sys_dburigen并决定要从CLOB中提取的大小：

SELECT
  cols.table_name, 
  cols.column_name, 
  cols.data_default,
  MAX(CASE WHEN cols.nullable = 'N' OR cons.constraint_type = 'P' THEN 'Y' ELSE 'N' END) AS is_required,
  MAX(CASE WHEN cons.constraint_type = 'P' THEN 'Y' ELSE 'N' END) AS is_primary_key,
  MAX(CASE WHEN cons.constraint_type = 'R' THEN 'Y' ELSE 'N' END) AS is_foreign_key,
  comm.comments
FROM (select table_name, column_name, nullable, data_length,
    dbms_lob.substr(sys_dburigen(table_name, column_name, column_id, data_default, 'text()').getclob(),1,256) AS data_default
    from user_tab_columns) cols
LEFT JOIN user_cons_columns cons_cols
  ON cols.table_name = cons_cols.table_name
  AND cols.column_name = cons_cols.column_name
LEFT JOIN user_constraints cons
  ON cons_cols.constraint_name = cons.constraint_name
LEFT JOIN user_col_comments comm
  ON cols.table_name = comm.table_name
  AND cols.column_name = comm.column_name
GROUP BY cols.table_name, cols.column_name, 
    cols.data_default, cols.data_length, cols.nullable, comm.comments
ORDER BY cols.table_name;