需要往一个表里面append一些数据，而表内有long 字段。处理还真麻烦，记录一下。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table t (id number,pic long);
Table created.
SQL> insert into t values (1,'aaaaaaaaaaaaaaaa');
1 row created.
SQL> create table t1 as select * from t;
create table t1 as select * from t
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
SQL> create table t1 (id number,pic long);
Table created.
SQL> insert into t1 select * from t;
insert into t1 select * from t
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
--看来含有LONG的字段处理起来很麻烦。 当然方法很多，看了一些blog,发现最简单的是使用sqlplus带的copy命令，
--可以实现。记录一下。俺以前经常用，现在差不多忘记了!!
--参考链接如下：http://www.dbaroad.me/archives/2008/11/long-type-usage.html
SQL> help copy
COPY
----
Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query
where database has the following syntax:
username[/password]@connect_identifier
SQL> copy from scott/xyzxyz@192.168.200.56/test.com to scott/xyzxyz@192.168.200.56/test.com append t1 using select * from t;
Array fetch/bind size is 200. (arraysize is 200)
Will commit when done. (copycommit is 0)
Maximum long size is 20000000. (long is 20000000)
1 rows selected from scott@192.168.200.56/test.com.
1 rows inserted into T1.
1 rows committed into T1 at scott@192.168.200.56/test.com.
SQL> set long 100
SQL> select * from t1;
ID PIC
---------- -----------------
1 aaaaaaaaaaaaaaaa
SQL> copy from scott/xyzxyz@192.168.200.56/test.com to scott/xyzxyz@192.168.200.56/test.com create t2 using select * from t;
Array fetch/bind size is 200. (arraysize is 200)
Will commit when done. (copycommit is 0)
Maximum long size is 100. (long is 100)
Table T2 created.
1 rows selected from scott@192.168.200.56/test.com.
1 rows inserted into T2.
1 rows committed into T2 at scott@192.168.200.56/test.com.
SQL> desc t2;
Name Null? Type
------ -------- ------------
ID NUMBER(38)
PIC LONG
SQL> select * from t2;
ID PIC
---------- ----------------
1 aaaaaaaaaaaaaaaa
--另外不要设置long参数太小，否则会被截断！
SQL> set long 5
SQL> copy from scott/xyzxyz@192.168.200.56/test.com to scott/xyzxyz@192.168.200.56/test.com insert t2 using select * from t;
Array fetch/bind size is 200. (arraysize is 200)
Will commit when done. (copycommit is 0)
Maximum long size is 5. (long is 5)
1 rows selected from scott@192.168.200.56/test.com.
1 rows inserted into T2.
1 rows committed into T2 at scott@192.168.200.56/test.com.
SQL> set long 50
SQL> select * from t2;
ID PIC
---------- --------------------------------------------------
1 aaaaaaaaaaaaaaaa
1 aaaaa
