How to replace LONG columns in Data Dictionary views in Oracle with varchar

Question

This oracle forum posts in 2015 says that Long data type is deprecated for 20 years. Right now, in 2023, it is almost 30 years this data type is deprecated but it is commonly used in data dictionary views. Even though for some of the long data type columns has duplicated varchar2 columns after 12c, there are a still a lot of them for example data_default column in ALL_TAB_COLS.

select 
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH, 
NULLABLE,
DATA_DEFAULT,
VIRTUAL_COLUMN,
IDENTITY_COLUMN

from ALL_TAB_COLS  C 
   WHERE
        C.table_name = 'TABLE_NAME'
         AND C.OWNER = 'SCHEMA_NAME'
         AND C.COLUMN_NAME =  'COLUMN_NAME';

How to convert these long columns to varchar2?

Answer 1

Solution is taken from ask tom How to fetch part of a string for LONG datatype.

WITH FUNCTION GET_VARCHAR_DATA_DEFAULT(p_schema_name IN varchar2
,p_table_name IN varchar2
,p_column_name IN varchar2) RETURN varchar2 IS s_data_default varchar2(1000);
BEGIN
        select C.data_default into s_data_default
        FROM ALL_TAB_COLS C
        WHERE 
        C.table_name = p_table_name
         AND C.OWNER = p_schema_name
         AND C.COLUMN_NAME =  p_column_name;
        
        return s_data_default;
END;

select 
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH, 
NULLABLE,
GET_VARCHAR_DATA_DEFAULT(C.OWNER,C.TABLE_NAME,C.COLUMN_NAME)  DATA_DEFAULT,
VIRTUAL_COLUMN,
IDENTITY_COLUMN

from ALL_TAB_COLS  C 
   WHERE
        C.table_name = 'TABLE_NAME'
         AND C.OWNER = 'SCHEMA_NAME'
         AND C.COLUMN_NAME =  'COLUMN_NAME';