title: Oracle CLOB to varchar2 over DBLINK
body: <p>I have to migrate a database to another. I have a dblink to make it easyier.
  Know I try to copy the colum comments and description from a table. The Problem is that the type is a CLOB. In the new database I use VARCHAR2.
  I used a workaround and created a global temporary table.</p>
  
  <pre><code>create global temporary table Comments
   (id number,description clob, comment clob) ON COMMIT PRESERVE ROWS; -
  
    insert into  Comments Select id,description,comment from database.test@DBLINK;
  </code></pre>
  
  <p>Now i have <strong>52K rows in the table</strong>.</p>
  
  <p>Now I want to create the new table test in the new database.</p>
  
  <pre><code>CREATE TABLE "TEST"
  (
  "ID"             NUMBER(9,0) NOT NULL ENABLE,
  "NAME"             VARCHAR2(255) ,
  "DESCRIPTION"             VARCHAR2(4000) ,
  "COMMENT"             VARCHAR2(4000) ,
  CONSTRAINT "TEST_PK" PRIMARY KEY ("ID")
  );
  </code></pre>
  
  <p>And Now I try to insert the values:</p>
  
  <pre><code>insert into TEST(ID, NAME,DESCRIPTION, COMMENT)
   select Test_seq.nextval,Name,
    (select dbms_lob.substr(DESCRIPTION, 4000, 1) from  Comments b where b.id =a.id),
  (select dbms_lob.substr(COMMENT, 4000, 1) from  Comments b where b.id =a.id),
  from database.test@DBLINK a;
  </code></pre>
  
  <p>The problem is that it takes tooooooooooooo long to insert the values. I started it and it didn't finish after 30 minutes. </p>
  
  <p>Is there a fast way to get convert clobs in varchar2 and insert them into a table in an other Oracle database?</p>
  
  <p>Thanks for help!</p>