[20120910]建立包含long类型的表.txt

需要往一个表里面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.htmlSQL> 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