How to copy data with "Long Raw" columns

Published: Sunday, 27 September 2015 08:40 | Written by Stefan | Print | Email | Hits: 44041

From time to time I will be faced the problem to move data from one table to another. Under normal circumstances this is not a big deal, but if the source table contains a long-/raw column this is impossible to manage that with plain SQL. So here is a suitable workaround to solve this problem with a few lines of PL/SQL code

Starting point is a table with a long raw column:

insert into long_table
select 'new id',
       long_column /* LONG-Feld */
  from long_table a
 where a.long_id = 'some old id';

This ends up with "ORA-00997: illegal use of LONG datatype error"

The workaround is:

declare
  l_rec_long_table long_table%rowtype;
begin
  select *
    into l_rec_long_table
    from long_table a
   where a.long_id = 'some old id';
 
  insert into long_table
  values
    ('new id',
     l_rec_long_table.long_column);
end;