转：LONG类型转换为clob_doudou

http://blog.sina.com.cn/u/1575824987

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

转：LONG类型转换为clob

(2013-06-08 11:14:20)

标签：

时尚

分类：数据库

//clob直接和普通字段一样，可以insert等等。

昨天想通过建立dblink把远程数据库的数据都导入到本地，写了两个简单的存储过程，发现view的数据字典user_view中的text字段是使用long类型的字段而无法导入，所以研究了一下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类型：

SQL> copy from userid1/password1@SID to userid2 /password2@SID append t3 using select * from t1;

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的循环引用，不知道是为什么原因，不知道哪里重名了，于是修改了一下，创建成功：

idle> copy from userid1/password1@SID to userid2/password2@SID append t3 using select id a,text b from t1;

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执行。

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：windows 如何查看端口占用情况?

后一篇：过多文件不能删除问题

新浪BLOG意见反馈留言板　欢迎批评指正