[thesite] problems copying a LONG column in oracle

[rudy]

this is directed to the oracle gurus  (adam/tcm614ce?  dan?  anybody?)

we have a table with a LONG column that we need to migrate to a new table

we've done this once (see below for similar procedure)

here's the current table --

 CONTENT
   CONTENTID   NOT NULL NUMBER(10)
   CONTENTNAME          VARCHAR2(50)
   BODY                 LONG
   USERID               NUMBER(10)
   DATEMOD              DATE
   SIGNOFF              NUMBER(10)
   ADMINID              NUMBER(10)
   CATEGORYID           NUMBER(10)
   SIGNDATE             DATE
   SYNOPSIS             VARCHAR2(500)
   RATING      NOT NULL NUMBER(3,2)
   RATINGS     NOT NULL NUMBER(8)
   REPLIES     NOT NULL NUMBER(8)
   KEYPHRASE            VARCHAR2(100)

and here's the table we want to copy to --

 CONTENT2
   CONTENTID   NOT NULL NUMBER(8)
   LANGUAGEID  NOT NULL NUMBER(8)
   CATEGORYID  NOT NULL NUMBER(8)
   USERID      NOT NULL NUMBER(8)
   DATEMOD     NOT NULL DATE
   SIGNOFF     NOT NULL NUMBER(1)
   SIGNDATE             DATE
   ADMINID              NUMBER(8)
   PRIV        NOT NULL NUMBER(1)
   CONTENTNAME NOT NULL VARCHAR2(50)
   SYNOPSIS             VARCHAR2(255)
   BODY                 CLOB
   RATING      NOT NULL NUMBER(3,2)
   RATINGS     NOT NULL NUMBER(8)
   REPLIES     NOT NULL NUMBER(8)

dan, can you remember why we wanted to go from LONG to CLOB?

during codefest 2 in november i was able to run the following procedure
(there was a second LONG column called BODY2 on the table at that time) --

       exec declare cursor get_content is
            select contentid,body from content where contentid>3800;
       begin for con in get_content
          loop
             update content2 set body2 = con.body
               where contentid = con.contentid;
          end loop;
       end;

     PL/SQL procedure successfully completed.

however, i was unable to get it to work copying into a CLOB

anybody have any input on this?


rudy