Oracle Long to varchar2 conversion and substr of long columns

=======================================================================
-- Create table
=======================================================================

create table DBA_CONSTRAINTS_DUMMY
(
  CONSTRAINT_NAME  VARCHAR2(4000),
  TABLE_NAME       VARCHAR2(4000),
  CONSTRAINT_TYPE  VARCHAR2(4000),
  SEARCH_CONDITION VARCHAR2(4000)
)
/
=======================================================================
method 1 -- for all table values
=======================================================================
declare
long_var LONG;
var_var  VARCHAR2(2000);
begin

select search_condition into long_var
from dba_CONSTRAINTS where rownum<=1;
   
   var_var := substr(long_var,1,4000);
   
insert into dba_CONSTRAINTS_dummy values ( long_var,long_var,long_var,long_var);

end;
/

select * from dba_CONSTRAINTS_dummy ;

=======================================================================
method2 : if its a direct select from a single table 
=======================================================================
create or replace function convert_long_to_varchar2
(
p_rowid in rowid,
column_name varchar2,
table_name  varchar2)
return varchar2 is
long_var long;
v_sql varchar2(3000);
begin
v_sql := 'select '||column_name||' from '||table_name||' where rowid='||p_rowid;
execute immediate v_sql return into long_var;
  return substr( long_var, 1, 4000 );
end;

/

select  convert_long_to_varchar2(rowid) form table;

=======================================================================
Method 3 : using xmltype
=======================================================================
select *
from
xmltable('/ROWSET/ROW'
passing dbms_xmlgen.getXMLType('
select
table_name
,column_name
,data_type
,data_default
from user_tab_columns
where data_default is not null
'
)
columns
table_name varchar2(30)
,column_name varchar2(30)
,data_type varchar2(106)
,data_default varchar2(4000)
)
=======================================================================
Method 4 :TO_LOB
=======================================================================
select SUBSTR(CONSTRAINT_NAME, 1, 30) AS CONSTNAME,
SUBSTR(TABLE_NAME, 1, 30) AS TABNAME, constraint_type,
 TO_LOB(SEARCH_CONDITION   ) SEARCH_CONDITION
FROM  dba_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'C'

ORA-00932: inconsistent datatypes: expected - got LONG

 CREATE TABLE nullability_snapshot
   AS
   SELECT table_name
    ,      column_name
     ,      constraint_name
     ,      TO_LOB(search_condition) AS search_condition
     FROM   nullability_view;
=======================================================================
Method 5 :WITH EXTERNAL TABLE :
=======================================================================
  CREATE TABLE all_objects_Long
  ORGANIZATION EXTERNAL
  (
     TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY DATA_PUMP_DIR
     LOCATION ( 'long.dmp' )
  )
    PARALLEL 4
   AS
   select  CONSTRAINT_NAME  , TABLE_NAME , constraint_type
    , to_lob(SEARCH_CONDITION) SEARCH_CONDITION FROM dba_CONSTRAINTS x;

=======================================================================
Method 6 :Pipeline function
=======================================================================
CREATE TYPE nullability_ot AS OBJECT
    ( CONSTNAME       VARCHAR2(1000)
     , TABNAME      VARCHAR2(100)
     ,constraint_type VARCHAR2(100)
    , search_condition CLOB
   );
  
CREATE TYPE nullability_ntt AS TABLE OF nullability_ot;
 
CREATE FUNCTION nullability_pipelined RETURN nullability_ntt PIPELINED AS
     BEGIN
      FOR r IN (select SUBSTR(CONSTRAINT_NAME, 1, 30) AS CONSTNAME,
SUBSTR(TABLE_NAME, 1, 30) AS TABNAME, constraint_type,
  SEARCH_CONDITION   
FROM  user_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'C') LOOP
         PIPE ROW ( nullability_ot(r.CONSTNAME,
                                   r.TABNAME,
                                    r.constraint_type,
                                    r.SEARCH_CONDITION) );
       END LOOP;
       RETURN;
END;
SELECT 
CONSTNAME ,     
 TABNAME    ,
constraint_type,
substr(search_condition,1,100)
    FROM   TABLE(nullability_pipelined)
    WHERE  UPPER(search_condition) LIKE '%IS NOT NULL%';