My Remarks!

the Life, the Universe and almost Everything what's Left

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/templates/stefans_blog_05/functions.php on line 182

How to copy data with "Long Raw" columns

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;

Visits

Today 9 | Week 83 | Month 1935 | All 1108754

Challenges Completed

Vätternsee Club des Cinglés du Mont-Ventoux Styrkeproven 24hVelo Belchen³ Guitar Rehearsal

StackOverflow

profile for PT_STAR at Stack Overflow, Q&A for professional and enthusiast programmers