Carlos Sierra's Tools and Tips

Working on the new eDB360 repository I came across this “ORA-00997: illegal use of LONG datatype” while trying to CTAS on the following DBA views:

dba_constraints
dba_ind_partitions
dba_ind_subpartitions
dba_tab_cols
dba_tab_columns
dba_tab_partitions
dba_tab_subpartitions
dba_triggers
dba_views

All these views above include at least a LONG column, which raises the ORA-00997 while trying to do something like: CREATE TABLE edb360.dba#constraints AS SELECT * FROM dba_constraints.

I found several blogs explaining reason and some providing some hints, like using the TO_LOB function. Based on that I created a new stand-alone script that inputs 4 parameters and performs the CTAS I need. The 4 parameters are:

1: owner of source table/view
2: source table/view name
3: owner of target table
4: target table name

I am making this free script available for others to use at will.

-- How to solve ORA-00997: illegal use of LONG datatype while copying tables
-- paramaters
-- 1: owner of source table/view
-- 2: source table/view name
-- 3: owner of target table
-- 4: target table name
-- sample: @repo_edb360_create_one sys dba_constraints edb360 dba#constraints

DEF owner_source = '&1.';
DEF table_source = '&2.';
DEF owner_target = '&3.';
DEF table_target = '&4.';

DECLARE
l_list_ddl VARCHAR2(32767);
l_list_sel VARCHAR2(32767);
l_list_ins VARCHAR2(32767);
BEGIN
FOR i IN (SELECT column_name, data_type, data_length FROM dba_tab_columns WHERE owner = UPPER(TRIM('&&owner_source.')) and table_name = UPPER(TRIM('&&table_source.')) ORDER BY column_id)
LOOP
l_list_ddl := l_list_ddl||','||i.column_name||' '||REPLACE(i.data_type,'LONG','CLOB');
l_list_ins := l_list_ins||','||i.column_name;
IF i.data_type IN ('VARCHAR2', 'CHAR', 'RAW') THEN
l_list_ddl := l_list_ddl||'('||i.data_length||')';
END IF;
IF i.data_type = 'LONG' THEN
l_list_sel := l_list_sel||',TO_LOB('||i.column_name||')';
ELSE
l_list_sel := l_list_sel||','||i.column_name;
END IF;
END LOOP;
EXECUTE IMMEDIATE 'CREATE TABLE &&owner_target..&&table_target. ('||TRIM(',' FROM l_list_ddl)||') COMPRESS';
EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO &&owner_target..&&table_target. ('||TRIM(',' FROM l_list_ins)||') SELECT '||TRIM(',' FROM l_list_sel)||' FROM &&owner_source..&&table_source.';
EXECUTE IMMEDIATE 'COMMIT';
END;
/