一、long类型的特性
昨天想通过建立dblink把远程数据库的数据都导入到本地，写了两个简单的存储过程，发现view的数据字典user_view中的text字段是使用long类型的字段，所以研究了一下long类型属性：
SQL> desc user_views
Name Type Nullable Default Comments
---------------- -------------- -------- ------- ----------------------------------------------------------
VIEW_NAME VARCHAR2(30) Name of the view
TEXT_LENGTH NUMBER Y Length of the view text
TEXT LONG Y View text
TYPE_TEXT_LENGTH NUMBER Y Length of the type clause of the object view
TYPE_TEXT VARCHAR2(4000) Y Type clause of the object view
OID_TEXT_LENGTH NUMBER Y Length of the WITH OBJECT OID clause of the object view
OID_TEXT VARCHAR2(4000) Y WITH OBJECT OID clause of the object view
VIEW_TYPE_OWNER VARCHAR2(30) Y Owner of the type of the view if the view is a object view
VIEW_TYPE VARCHAR2(30) Y Type of the view if the view is a object view
SUPERVIEW_NAME VARCHAR2(30) Y Name of the superview, if view is a subview
其实long类型是oracle的一个过去式的类型，只是为了向下兼容才保存在10g里的。这是《SQL Reference》里的建议：“Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead. LONG columns are supported only for backward compatibility.”
另外oracle在long类型的使用上有比较多的限制，总而言之就是让你不要再使用long类型，而用lob类型替代：
You can reference LONG columns in SQL statements in these places:
■ SELECT lists
■ SET clauses of UPDATE statements
■ VALUES clauses of INSERT statements
The use of LONG values is subject to these restrictions:
■ A table can contain only one LONG column.
■ You cannot create an object type with a LONG attribute.
■ LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
■ LONG columns cannot be indexed.
■ LONG data cannot be specified in regular expressions.
■ A stored function cannot return a LONG value.
■ You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.
■ Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
■ LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
■ If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
In addition, LONG columns cannot appear in these parts of SQL statements:
■ GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
■ The UNIQUE operator of a SELECT statement
■ The column list of a CREATE CLUSTER statement
■ The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
■ SQL built-in functions, expressions, or conditions
■ SELECT lists of queries containing GROUP BY clauses
■ SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
■ SELECT lists of CREATE TABLE ... AS SELECT statements
■ ALTER TABLE ... MOVE statements
■ SELECT lists in subqueries in INSERT statements
Triggers can use the LONG datatype in the following manner:
■ A SQL statement within a trigger can insert data into a LONG column.
■ If data from a LONG column can be converted to a constrained datatype (such asCHAR and VARCHAR2), then a LONG column can be referenced in a SQL statementwithin a trigger.
■ Variables in triggers cannot be declared using the LONG datatype.
■ :NEW and :OLD cannot be used with LONG columns.You can use Oracle Call Interface functions to retrieve a portion of a LONG value fromthe database.
二、解决建立新表引用long类型的问题：
建立含有long类型的表t1，在使用create as建立t2时报错
SQL> create table t1 (id int,text long);
Table created
SQL> insert into t1 values(1,'asdf');
1 row inserted
SQL> commit;
Commit complete
SQL> create table t2 as select * from t1;
create table t2 as select * from t1
ORA-00997: illegal use of LONG datatype
方法一：
使用to_lob函数将long类型转换为clob类型，然后创建在其他的table中
SQL> desc t1
Name Type Nullable Default Comments
---- ------- -------- ------- --------
ID INTEGER Y
TEXT LONG Y
SQL> create table t2 as select id,to_lob(text) text from t1;
Table created
SQL> desc t2
Name Type Nullable Default Comments
---- ------- -------- ------- --------
ID INTEGER Y
TEXT CLOB Y
但是to_lob函数无法直接使用在查询中：
SQL> select id,to_lob(text) text from t1;
select id,to_lob(text) text from t1
ORA-00932: inconsistent datatypes: expected - got LONG
这是《SQL Reference》上的说明：
Before using this function, you must create a LOB column to receive the converted LONG values. To convert LONG values, create a CLOB column. To convert LONG RAW values, create a BLOB column.
You cannot use the TO_LOB function to convert a LONG column to a LOB column in the subquery of a CREATE TABLE ... AS SELECT statement if you are creating an index-organized table. Instead, create the index-organized table without the LONG column, and then use the TO_LOB function in an INSERT ... AS SELECT statement.
可见第一无法直接放到临时表中进行查询，必须要创建一个lob字段后才可以select。其次无法使用lob类型作为index。
方法二：
使用copy from来传递table中的long类型：
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 5000. (long is 5000)
SQLRCN in cpytbl failed: -1075
SQLRCN in cpyyerr failed: -1075
ERROR:
ORA-01775: looping chain of synonyms
发生了一个错误，在意synonyms的循环引用，不知道是为什么原因，不知道哪里重名了，于是修改了一下，创建成功：
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 5000. (long is 5000)
SQLRCN in cpytbl failed: -1075
1 rows selected from
userid1/password1@SID
.
1 rows inserted into T3.
1 rows committed into T3 at
userid2/password2@SID
.
这样即可以在同一个database中传递，也可以不建立dblink传输table数据。
注意：
1、append表示如果table不存在则create，存在则insert，所以执行2次会有两倍的记录条数。
2、另外append可以换成create、insert、replace，replace也有not exists则create的功能，应该以使用这个为主。
3、copy 属于sqlplus命令，无法在PLSQL中使用，另外在PLSQL Dev的command window中无法使用该功能。
三、远程传输long、lob类型数据：
创建一个dblink：
SQL> create public database link DBLINK
2 connect to (username) IDENTIFIED by (password)
3 using 'sid';
Database link created
查询数据：
SQL> select * from
t1@mislink
;
ID TEXT
--------------------------------------- --------------------------------------------------------------------------------
1 create view t22 as select * from lmriskapp
SQL> select * from
t2@mislink
;
select * from
t2@mislink
ORA-22992: cannot use LOB locators selected from remote tables
发现long类型可以使用远程传输，而lob类型不可以。
传输数据：
SQL> create table t2 as select id,text a from
t1@mislink
;
create table t2 as select id,text a from
t1@mislink
ORA-00997: illegal use of LONG datatype
SQL> create table t2 as select id,to_lob(text) a from
t1@mislink
;
create table t2 as select id,to_lob(text) a from
t1@mislink
ORA-00997: illegal use of LONG datatype
发现使用两种形式，刚好都因为有各自的显示，都无法进行远程创建。
最终发现只能通过copy from来做，对应最初遇到的问题，先用copy from将user_views导入到本地，然后再通过PLSQL批量处理各个View的执行。
另注：无论是long还是clob，均可以直接在PLSQL中给varchar2赋值，并使用execute immediate执行。
-The End-