Re: How do I copy LONG columns between tables?

"Stefan Larsson" <d95stela+news_at_dtek.chalmers.se> wrote in message news:slrn91t2u2.cm9.d95stela+news_at_licia.dtek.chalmers.se...
> PL/SQL will still not let me declare or use LONG variables and columns
>

I've done this before with no problems. I've done this on Oracle 7.3.4 and 8.0.5. I declare LONG variables, read a long column into them, then use the variable in my update:

--
============================================================================
-- PL/SQL block to consolidate old long_description records
-- into plt_activity_codes.extended_task_description
--
============================================================================

DECLARE
     CURSOR c1 is
  SELECT code, description
  FROM &&from_user..long_description
  WHERE name_of_form = 'ACTIV_CD'
  order by code, sequence;

 num_recs        number;
 v_code         varchar2(6);
 v_description   LONG;
        v_description_2 LONG;
 v_length number;
 v_num_code number;

BEGIN
        select count(code)
 into num_recs
 from &&from_user..long_description
 where name_of_form = 'ACTIV_CD';

    OPEN c1;

 FOR i in 1..num_recs LOOP

 FETCH c1 INTO v_code, v_description;
 EXIT WHEN c1%NOTFOUND;


        SELECT extended_task_description
        INTO v_description_2
        FROM plt_activity_codes
        WHERE activity_code = v_code;

                IF v_description_2 is NULL THEN
                v_description_2 := v_description;
                else
                v_description_2 := v_description_2 || chr(10) ||
v_description;

                END if;

 UPDATE plt_activity_codes
 SET extended_task_description = v_description_2
 WHERE activity_code = v_code;

        -- There will be (num_recs) update statements issued. Num_recs
represents the number of
        -- records in the long_description table that apply to
activity_codes.

        COMMIT;

    END LOOP;
   CLOSE c1;

END;
/