LONG类型字段很原始使用时限制也很多，与LONG类型相对应的类型有CLOB，LONG类型虽然已经不建议使用但还是可以发现在Oracle数据字典相关的视图中还是有LONG类型的身影。
--建表
CREATE TABLE TEST_LONGTYPE_TABLE(
ID VARCHAR2(20) NOT NULL,
TEXT_LONG LONG,
TEXT_CLOB CLOB
)
--直接显示更改类型，注意此种办法是不可逆的（变LONG为CLOB后不可以再改回来）
ALTER TABLE TEST_LONGTYPE_TABLE MODIFY TEXT_LONG CLOB;
--向LONG的列插入数据
INSERT INTO TEST_LONGTYPE_TABLE(ID, TEXT_LONG) VALUES('001', 'LONG类型');
--用TO_LOB转换后数据放到CLOB中
INSERT INTO TEST_LONGTYPE_TABLE(ID, TEXT_CLOB) SELECT ID, TO_LOB(TEXT_LONG) FROM TEST_LONGTYPE_TABLE
SELECT * FROM TEST_LONGTYPE_TABLE UNION ALL SELECT * FROM TEST_LONGTYPE_TABLE
非法
SELECT * FROM TEST_LONGTYPE_TABLE UNION SELECT * FROM TEST_LONGTYPE_TABLE
A1_LONG LONG;
A2_LONG LONG;
BEGIN
FOR C IN (SELECT A_DATADF, B_DATADF
FROM (SELECT A.TABLE_NAME,
A.COLUMN_NAME,
A.DATA_TYPE A_DT,
B.DATA_TYPE B_DT,
A.DATA_LENGTH A_DLEN,
B.DATA_LENGTH B_DLEN,
A.DATA_PRECISION A_DPREC,
B.DATA_PRECISION B_DPREC,
A.DATA_SCALE A_DSCA,
B.DATA_SCALE B_DSCA,
A.NULLABLE A_NULLA,
B.NULLABLE B_NULLA,
A.DEFAULT_LENGTH A_DEFLEN,
B.DEFAULT_LENGTH B_DEFLEN,
A.DATA_DEFAULT A_DATADF,
B.DATA_DEFAULT B_DATADF
FROM (SELECT TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
DEFAULT_LENGTH,
DATA_DEFAULT
FROM ALL_TAB_COLS ACT1
WHERE OWNER = 'USER_A'
AND EXISTS
(SELECT 1
FROM ALL_TAB_COLS ATC2
WHERE ATC2.OWNER = 'USER_B'
AND ATC2.TABLE_NAME = ACT1.TABLE_NAME)) A,
(SELECT TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
DEFAULT_LENGTH,
DATA_DEFAULT
FROM ALL_TAB_COLS ACT1
WHERE OWNER = 'USER_B'
AND EXISTS
(SELECT 1
FROM ALL_TAB_COLS ATC2
WHERE ATC2.OWNER = 'USER_A'
AND ATC2.TABLE_NAME = ACT1.TABLE_NAME)) B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME) C
WHERE C.A_DEFLEN C.B_DEFLEN) LOOP
A1_LONG := C.A_DATADF;
A2_LONG := C.B_DATADF;
DBMS_OUTPUT.put_line('Step_1_A1_LONG/A2_LONG 长度：' || LENGTH(A1_LONG) || '/' || LENGTH(A2_LONG));
A1_LONG := TRIM(A1_LONG);
A2_LONG := TRIM(A2_LONG);
DBMS_OUTPUT.put_line('Step_2_A1_LONG/A2_LONG 长度：' || LENGTH(A1_LONG) || '/' || LENGTH(A2_LONG));
DBMS_OUTPUT.put_line('Step_3_A1_LONG/A2_LONG 内容：' || TO_CHAR(A1_LONG) || '/' || TO_CHAR(A2_LONG));
IF (C.A_DATADF = C.B_DATADF) THEN
DBMS_OUTPUT.put_line('相等性');
ELSE
DBMS_OUTPUT.put_line('不相等');
END IF;
END LOOP;
END WYC_LONGTYPE_TEST;
/