Oracle PL/SQL Tutorial/Large Objects/to clob

Copying LONG to LOB with the TO_CLOB and TO_BLOB functions

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.

fill a varchar2 type data with rpad

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>