oracle long转换，ORA-00932的错误

前几天就有同事问，long太难控制，想用varchar2，想想应该没问题。无独有偶，今天看论坛有同样的问题，那么做个测试，结果是需要把long借clob中转一下。

完整的测试过程如下：

SQL> select * from a01;

COL1                 COL2 COL3
-------------------- ---- --------------------------------------------------------------------------------
1                    1    wwwww

SQL> desc a01
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
COL1 VARCHAR2(20) Y                         
COL2 VARCHAR2(2)  Y        'Y'              
COL3 LONG         Y                         

SQL> select * from a01;

COL1                 COL2 COL3
-------------------- ---- --------------------------------------------------------------------------------
1                    1    wwwww

SQL> update a01 set col1 = col3;

update a01 set col1 = col3

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

SQL> select dump(col3) from a01;

select dump(col3) from a01

ORA-00997: illegal use of LONG datatype

SQL> insert into a03(dept_code,dept_name,super_dept_code) select * from a01;

insert into a03(dept_code,dept_name,super_dept_code) select * from a01

ORA-00997: illegal use of LONG datatype

SQL> 
SQL> insert into a03(dept_code,dept_name,super_dept_code) select col1,col2,to_lob(col3) from a01;

1 row inserted

SQL> 
SQL> 
SQL> 
SQL> update a01 set col1 = to_lob(col3);

update a01 set col1 = to_lob(col3)

ORA-00932: inconsistent datatypes: expected - got LONG

SQL> 
SQL> 
SQL> 
SQL> 
SQL> desc a01
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
COL1 VARCHAR2(20) Y                         
COL2 VARCHAR2(2)  Y        'Y'              
COL3 LONG         Y                         

SQL> select col3 from a01;

COL3
--------------------------------------------------------------------------------
wwwww

SQL> create table a02(t varchar2(20));

Table created

SQL> insert into a02 (select to_lob(col3) from a01);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from a02;

T
--------------------

SQL> create table a002(t clob);

Table created

SQL> insert into a002 (select to_lob(col3) from a01);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from a002;

T
--------------------------------------------------------------------------------
wwwww

SQL> truncate table a02;

Table truncated

SQL> insert into a02 (select * from a002);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from a02;

T
--------------------
wwwww