There are many ways to read a LONG column - in general one have to use the PL/SQL code. The summary of those ways is provided here (
http://www.oracle-developer.net/display.php?id=430).
I focused only on one of those methods i.e. the one leveraging the XML processing features of the Oracle RDBMS. The main reason here is the convenience while trying to produce a kind of administration script (which a basic task is usually to generate another script, which in turn makes the final effect).
Here an example - the script, which produces the script, which may recreate constraints from a chosen schema:
set pages 0 lin 300 trimspool on trimout on
define CONDITIONS='OWNER=''VPD_BPSS'''
-- check
SELECT 'alter table '
|| xs.owner
||'.'
|| xs.table_name
||' add constraint '
|| xs.constraint_name
||' '
|| ' check ('
|| xs.search_condition
||')'
||
CASE xs.status
WHEN 'ENABLED'
THEN ' enable '
WHEN 'DISABLED '
THEN ' disable '
END
|| ';' SQL1
FROM XMLTABLE('/ROWSET/ROW' PASSING
(SELECT dbms_xmlgen.getxmltype(
q'[SELECT * FROM dba_constraints c WHERE c.constraint_type in ('C') and &&CO
NDITIONS]' )
FROM dual
) COLUMNS
owner VARCHAR2(32) PATH 'OWNER',
constraint_name VARCHAR2(32) path 'CONSTRAINT_NAME',
constraint_type VARCHAR2(1) path 'CONSTRAINT_TYPE',
table_name VARCHAR2(30) path 'TABLE_NAME',
search_condition VARCHAR2(4000) path 'SEARCH_CONDITION',
status VARCHAR2(30) path 'STATUS' ) xs
UNION ALL
-- pk and uniq
SELECT 'alter table '
||c.owner
||'.'
||c.table_name
||' add constraint '
||c.constraint_name
||
CASE c.constraint_type
WHEN 'P'
THEN ' primary key'
WHEN 'U'
THEN ' unique'
END
|| ' ('
||
(SELECT LISTAGG(i.column_name, ',') WITHIN GROUP (
ORDER BY i.position)
FROM dba_cons_columns i
WHERE c.owner = i.owner
AND c.constraint_name = i.constraint_name
)
||') using index '
||c.index_owner
||'.'
||c.index_name
||' '
|| c.deferrable
||' '
|| c.deferred
||' '
||';' DDL1
FROM DBA_CONSTRAINTS C
WHERE c.constraint_type IN ('P','U')
AND
&&CONDITIONS
UNION ALL
-- fk
SELECT 'alter table '
||c.owner
||'.'
||c.table_name
||' add constraint '
||c.constraint_name
||' foreign key('
||
(SELECT LISTAGG(f.column_name, ',') WITHIN GROUP (
ORDER BY f.position)
FROM dba_cons_columns f
WHERE c.owner =f.owner
AND c.constraint_name=f.constraint_name
)
||') '
||' references '
||
(SELECT p.owner
||'.'
||p.table_name
||'('
|| LISTAGG(p.column_name, ',') WITHIN GROUP (
ORDER BY p.position)
|| ')'
FROM dba_cons_columns p
WHERE c.r_OWNER =p.owner
AND c.r_constraint_name = p.constraint_name
GROUP BY p.owner,
p.table_name
)
||' '
||
CASE
WHEN c.delete_rule IS NOT NULL
AND c.delete_rule! ='NO ACTION'
THEN 'ON DELETE '
|| c.delete_rule
||' '
END
|| c.deferrable
||' '
|| c.deferred
||' '
||
CASE status
WHEN 'ENABLED'
THEN 'ENABLE novalidate '
WHEN 'DISABLED '
THEN 'DISABLE '
END
||';' ddl1
FROM DBA_CONSTRAINTS C
WHERE C.CONSTRAINT_TYPE IN ('R')
AND
&&CONDITIONS ;
