操作LONG类型

ORACLE已经不推荐用LONG类型了，改为CLOB，对于LONG操作有些限制，比如对于有LONG类型是不能用insert into xxxx select * from xxxx。

SQL> desc test1;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
A VARCHAR2(10) Y
B LONG Y

SQL> desc test2;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
A VARCHAR2(10) Y
B LONG Y

CREATE OR REPLACE PROCEDURE test3
IS
CURSOR c1 IS SELECT a,b FROM test1;
BEGIN
FOR r1 IN c1 LOOP
INSERT INTO test2 (a,b) VALUES(r1.a,r1.b);
END LOOP;
COMMIT;
END;

SQL> execute test3;

PL/SQL procedure successfully completed

在9i以后，可以直接将LONG转为CLOB类型

SQL> alter table test1 modify b clob;

Table altered

SQL> alter table test2 modify b clob;

Table altered

SQL> insert into test2 select * from test1;

1 row inserted

SQL> commit;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8118583/viewspace-64856/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8118583/viewspace-64856/