關于LONG類型的討論
?
?
??? 昨天想通過建立dblink把遠程數(shù)據(jù)庫的數(shù)據(jù)都導入到本地，寫了兩個簡單的存儲過程，發(fā)現(xiàn)view的數(shù)據(jù)字典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類型的問題：
?
??? 建立含有l(wèi)ong類型的表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函數(shù)將long類型轉換為clob類型，然后創(chuàng)建在其他的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函數(shù)無法直接使用在查詢中：
?
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.
?
??? 可見第一無法直接放到臨時表中進行查詢，必須要創(chuàng)建一個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
??? 發(fā)生了一個錯誤，在于synonyms的循環(huán)引用，不知道是為什么原因，不知道哪里重名了，于是修改了一下，創(chuàng)建成功：
??
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數(shù)據(jù)。
?
??? 注意：
??? 1、append表示如果table不存在則create，存在則insert，所以執(zhí)行2次會有兩倍的記錄條數(shù)。
??? 2、另外append可以換成create、insert、replace，replace也有not exists則create的功能，應該以使用這個為主。
??? 3、copy 屬于sqlplus命令，無法在PLSQL中使用，另外在PLSQL Dev的command window中無法使用該功能。
?
?
三、遠程傳輸long、lob類型數(shù)據(jù)：
?
??? 創(chuàng)建一個dblink：
?
SQL> create public database link DBLINK
? 2? connect to (username) IDENTIFIED by (password)
? 3? using 'sid';
Database link created
?
??? 查詢數(shù)據(jù)：
?
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
?
??? 發(fā)現(xiàn)long類型可以使用遠程傳輸，而lob類型不可以。
?
??? 傳輸數(shù)據(jù)：
?
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
?
??? 發(fā)現(xiàn)使用兩種形式，剛好都因為有各自的顯示，都無法進行遠程創(chuàng)建。
?
???
??? 最終發(fā)現(xiàn)只能通過copy from來做，對應最初遇到的問題，先用copy from將user_views導入到本地，然后再通過PLSQL批量處理各個View的執(zhí)行。
?
??? 另注：無論是long還是clob，均可以直接在PLSQL中給varchar2賦值，并使用execute immediate執(zhí)行。
?
?
?
?