[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命令，

--可以實現。記錄一下。俺以前經常用，現在差不多忘記了!!

--參考連結如下：
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