Oracle PL/SQL Tutorial/Large Objects/to clob

Copying LONG to LOB with the TO_CLOB and TO_BLOB functions

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE myClobTable (id NUMBER,clob_data CLOB); Table created. SQL> SQL> CREATE TABLE myLongTable (id NUMBER,long_data LONG); Table created. SQL> SQL> INSERT INTO myLongTable VALUES (100,RPAD("A",200000,"A")); 1 row created. SQL> SQL> declare

 2    x long;
 3    y clob;
 4  begin
 5    select long_data
 6    into x
 7    from myLongTable
 8    where id =100;
 9    y :=to_clob(x);
10    insert into myClobTable values (200,y);
11  end;
12  /

PL/SQL procedure successfully completed. SQL> SQL> drop table myLongTable; Table dropped. SQL> SQL> SQL> drop table myClobTable; Table dropped.</source>

fill a varchar2 type data with rpad

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE myClobTable (id NUMBER,clob_data CLOB); Table created. SQL> SQL> declare

 2    x varchar2(32767):=rpad("A",32767,"B");
 3    y clob;
 4  begin
 5    y :=to_clob(x);
 6    insert into myClobTable values (200,y);
 7  end;
 8  /

PL/SQL procedure successfully completed. SQL> SQL> drop table myClobTable; Table dropped. SQL> SQL> SQL></source>