Copying LONG to LOB with the TO_CLOB and TO_BLOB functions : to_clob « Large Objects « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Large Objects
3. to_clob

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.