In SQL you may not always be able to create and use a PL/SQL function, so a pure SQL solution would be nice (and probably faster as well).
Such a solution is shown below, using sys_dburigen - see the view code for ted_p1026_v below.
I found that method at https://forums.oracle.com/ords/apexds/post/how-to-convert-long-to-any-character-string-varchar2-char-8263.

In database 26ai, a new column data_default_vc was introduced for the [user/all/dba]_tab_cols views, so in the view both that one and the sys_dburigen solution are used to show it actually works :-)
You can use the sys_dburigen solution by itself for other Long columns in other tables of course.

Note: I used a with-clause in the view for performance reasons, but also because I couldn't get it to work without one.
I expected this simpler query to work as well: select tcn.table_name ,tcn.column_name ,to_char(sys_dburigen(tcn.table_name, tcn.column_name, tcn.data_default, 'text()').getclob()) as data_default_vc2 from user_tab_cols tcn join user_tables tab on tab.table_name = tcn.table_name -- join needed to only select tables, not views where tcn.table_name like 'TED%' and tcn.data_default is not null However, that one gives an "ORA-19334: invalid column specification for the SYS_DBURIGEN function" error.
Somehow joining the table that is used in sys_dburigen triggers this error, but I can't explain that behavior...

Package

Package
	Name	TED_P1026
	Source	package ted_p1026 is function column_long_to_clob ( p_table_or_view_name in varchar2 ,p_column_name in varchar2 ,p_where_clause in varchar2 -- e.g. 'id = 10', make sure the where clause results in only 1 (or 0) result(s) ) return clob; end ted_p1026;

Package Body

Package Body
	Name	TED_P1026
	Source	package body ted_p1026 is function column_long_to_clob ( p_table_or_view_name in varchar2 ,p_column_name in varchar2 ,p_where_clause in varchar2 -- e.g. 'id = 10', make sure the where clause results in only 1 (or 0) result(s) ) return clob is -- Function returns part (max 32760 bytes) of a long value in a table -- The 32760 bytes limitation is due to dbms_sql.column_value_long v_stmt varchar2(2000); -- Dynamic statement v_cursor integer := dbms_sql.open_cursor; v_value_length pls_integer; -- Number of bytes actually returned in value (v_value) v_fetch_offset pls_integer := 0; -- Offset into the long field for start of fetch v_fetch_bytes pls_integer := 32000; -- Max 32760 - limitation is due to dbms_sql.column_value_long v_fetch_chunk varchar2(32000); -- Same length as v_fetch_bytes v_result clob; begin dbms_lob.createtemporary(lob_loc => v_result, cache => true); -- create dynamic statement v_stmt := 'select ' || p_column_name || chr(10) || 'from ' || p_table_or_view_name || chr(10) || 'where ' || nvl(p_where_clause, '1=1'); -- parse dynamic statement dbms_sql.parse(c => v_cursor -- ID number of the cursor in which to parse the statement ,statement => v_stmt -- SQL statement to be parsed ,language_flag => dbms_sql.native -- Determines how Oracle handles the SQL statement (NATIVE (or 1) specifies normal behavior for the database to which the program is connected) ); -- define the long column dbms_sql.define_column_long(c => v_cursor -- ID number of the cursor for the row being defined to be selected ,position => 1 -- Relative position of the column in the row being defined (The first column in a statement has position 1) ); -- process and fetch (if there is a result to fetch) if dbms_sql.execute_and_fetch(v_cursor) = 1 then loop -- get a chunk of bytes dbms_sql.column_value_long(c => v_cursor -- Cursor ID number of the cursor from which to get the value ,position => 1 -- Position of the column of which to get the value ,length => v_fetch_bytes -- Number of bytes of the long value to fetch ,offset => v_fetch_offset -- Offset into the long field for start of fetch ,value => v_fetch_chunk -- Value of the column as a VARCHAR2 ,value_length => v_value_length -- Number of bytes actually returned in value ); -- exit the loop if no bytes were fetched if v_value_length = 0 then exit; end if; -- append the fetched bytes to v_result dbms_lob.append(dest_lob => v_result, src_lob => v_fetch_chunk); -- update fetch offset v_fetch_offset := v_fetch_offset + v_fetch_bytes; end loop; end if; -- close the open cursor if needed if dbms_sql.is_open(v_cursor) then dbms_sql.close_cursor(v_cursor); end if; return v_result; end column_long_to_clob; end ted_p1026;

Region

Identification
	Sequence	20
	Title	Column Long to Clob (SQL)
	Type	Report
Source
	Location	Local Database
	Query Type	Table
	Table Name	TED_P1026_V
	Where Clause	table_name like 'TED%' and data_default is not null
	Order By Clause	table_name, column_name

View

View
	Name	TED_P1026_V
	DDL	with iv_tcn as (select tcn.* ,to_char(sys_dburigen(tcn.table_name, tcn.column_name, tcn.data_default, 'text()').getclob()) as data_default_vc2 from user_tab_cols tcn where data_default is not null -- conversion is very slow, so this (and the left join) is needed for performance ) select tcn.table_name , tcn.column_name , tcn.data_type , tcn.data_default , tcn.data_default_vc -- this is a new column introduced in database 26ai , iv_tcn.data_default_vc2 from user_tab_cols tcn join user_tables tab on tab.table_name = tcn.table_name -- join needed to only select tables, not views left join iv_tcn on (iv_tcn.table_name = tcn.table_name and iv_tcn.column_id = tcn.column_id)