Oracle LONG 資料型態完整解析：特性、限制與最佳替代方案

LONG 型態基本認知

• **儲存變長字串**，理論上限 **2 GB**。
• 資料字典大量使用（如 USER_VIEWS.TEXT, USER_TRIGGERS.TRIGGER_BODY）。
• **不支援字串搜尋函數** (SUBSTR, INSTR, LIKE) —— 這是最大痛點。
• 工具支援差：SQL*Plus 顯示截斷、匯入匯出困難、無法直接 INSERT ... SELECT 複製。

硬性限制清單 (10 大禁忌)

1. 單表**僅能有一個 LONG 欄位**。
2. **不可建立索引**。
3. **不可為叢集表 (Cluster) 欄位**。
4. **不可用於 INSERT ... SELECT / CREATE TABLE ... AS SELECT**。
5. **不可加約束** (PK, FK, Unique, Check)，僅允許 NULL/NOT NULL/DEFAULT。
6. **不可用於 WHERE / GROUP BY / ORDER BY / DISTINCT** 子句。
7. **不可用於分散式查詢 (DB Link)**。
8. **PL/SQL 變數不可宣告為 LONG** (僅 LONG RAW 同樣限制)。
9. **SQL 函數無法操作** (SUBSTR, INSTR, LENGTH 等皆報錯)。
10. 受限於磁碟空間與 LONG_CHUNK_SIZE 參數。

僅支援的操作

• SELECT (但工具常截斷)
• UPDATE ... SET long_col = '...'
• INSERT ... VALUES ('...')

現代替代方案：CLOB / NCLOB (Oracle 8i+)

遷移實戰：LONG → CLOB

-- 1. 新增 CLOB 欄位
ALTER TABLE old_table ADD new_clob_col CLOB;

-- 2. 使用 TO_LOB 轉換 (僅限 CREATE TABLE AS SELECT 或 INSERT SELECT)
CREATE TABLE new_table AS
SELECT id, TO_LOB(long_col) AS clob_col, other_cols
FROM old_table;

-- 或 PL/SQL 逐筆搬移 (大表分批 Commit)
DECLARE
    CURSOR c IS SELECT rowid, long_col FROM old_table FOR UPDATE;
BEGIN
    FOR r IN c LOOP
        UPDATE old_table SET new_clob_col = TO_LOB(long_col) WHERE ROWID = r.ROWID;
        IF MOD(c%ROWCOUNT, 1000) = 0 THEN COMMIT; END IF;
    END LOOP;
    COMMIT;
END;
/

-- 3. 驗證後 DROP 原 LONG 欄位
ALTER TABLE old_table DROP COLUMN long_col;
ALTER TABLE old_table RENAME COLUMN new_clob_col TO long_col;

結語

**LONG 是歷史包袱**，Oracle 官方早已標記為 Deprecated。所有新開發**嚴禁使用 LONG**，既有系統應規劃遷移至 CLOB。若必須暫時共存，請封裝 DBMS_LOB 操作介面隔離差異，並建立監控腳本偵測新增 LONG 欄位違規。