解決 PL/SQL 存儲過程「trailing null missing from STR bind value」錯誤：長字串參數正確處理

錯誤現象

呼叫 PL/SQL 存儲過程時，若輸入參數為 VARCHAR2 型別且字串長度超過約 1333 個英文字元 (或對應的中文/換行字元)，拋出錯誤：

ORA-06502: PL/SQL: numeric or value error: trailing null missing from STR bind value

根本原因

• Oracle 傳統 VARCHAR2 參數在 PL/SQL 呼叫介面 (OCI/OCCI/JDBC) 中有 32767 位元組上限，但某些驅動程式/版本在綁定長字串時，內部緩衝區處理存在缺陷，導致結束空字元 (null terminator) 遺失
• 具體觸發閾值因驅動版本、字元集 (單位字元位元組數)、平台而異，常見於 10g/11g 早期版本搭配舊版 JDBC/ODP.NET 驅動

解決方案

方案一：參數型別改為 CLOB (推薦，現代最佳實踐)

-- 修改前
CREATE OR REPLACE PROCEDURE process_text (p_input IN VARCHAR2) AS ...

-- 修改後
CREATE OR REPLACE PROCEDURE process_text (p_input IN CLOB) AS
  v_text VARCHAR2(32767);
BEGIN
  -- 若後續邏輯需 VARCHAR2，可轉換 (注意長度限制)
  IF LENGTH(p_input) <= 32767 THEN
    v_text := p_input;
    -- 原有 VARCHAR2 邏輯
  ELSE
    -- 處理超長字串邏輯 (分塊、暫存表、DBMS_LOB)
    NULL;
  END IF;
END;
/

• CLOB 支援高達 4GB，無綁定長度限制
• JDBC/ODP.NET 等驅動對 LOB 參數有成熟處理機制
• 相容性最好，Oracle 10g+ 完整支援

方案二：使用 LONG 型別 (不推薦，僅相容舊代碼)

CREATE OR REPLACE PROCEDURE process_text (p_input IN LONG) AS ...

• LONG 為舊型別，Oracle 8i 後已棄用，功能受限 (如無法在 SQL 中使用、不可作為函數參數、不支援 LOB API)
• 僅在無法修改為 CLOB 的極舊系統考慮

方案三：分割長字串為多參數/集合型別

-- 定義集合型別
CREATE OR REPLACE TYPE varchar2_table AS TABLE OF VARCHAR2(32767);

-- 存儲過程接收集合
CREATE OR REPLACE PROCEDURE process_long_text (p_chunks IN varchar2_table) AS
  v_full_text CLOB;
BEGIN
  FOR i IN 1..p_chunks.COUNT LOOP
    v_full_text := v_full_text || p_chunks(i);
  END LOOP;
  -- 處理 v_full_text
END;
/

• 應用層將長字串切分為 ≤32767 字元塊，以集合傳遞
• 避開單一 VARCHAR2 綁定限制，但增加應用層複雜度

方案四：升級驅動程式與資料庫版本

• JDBC: 升級至 ojdbc8.jar (12.2+) 或 ojdbc11.jar (19c+)
• ODP.NET: 升級至 Oracle.ManagedDataAccess NuGet 套件 (19.3+)
• 資料庫: 升級至 12.2+ (長字串支援增強，MAX_STRING_SIZE = EXTENDED 可將 VARCHAR2 擴展至 32767 位元組)

驗證修復

-- 測試腳本
DECLARE
  v_long_str VARCHAR2(32767) := RPAD('A', 5000, 'A');  -- 5000 字元
BEGIN
  process_text(v_long_str);  -- 應不再報錯
  DBMS_OUTPUT.PUT_LINE('測試通過');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('錯誤: ' || SQLERRM);
END;
/

最佳實踐建議

1. 新開發統一使用 CLOB 處理可能超過 4000 字元的文字參數
2. 表結構同步調整：對應欄位同步改為 CLOB 或 VARCHAR2(32767) (需啟用 MAX_STRING_SIZE=EXTENDED)
3. 文件化長度限制：在 API 文件、介面規格明確標註參數最大長度與型別
4. 單元測試覆蓋邊界值：測試 32767、32768、100000 字元等關鍵長度
5. 避免隱式轉換：明確使用 TO_CLOB()、DBMS_LOB.SUBSTR() 處理 LOB 與 VARCHAR2 互轉

相關設定：啟用擴展字串長度 (12.1+)

-- 需以 SYSDBA 執行，資料庫啟動 UPGRADE 模式
ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
@?/rdbms/admin/utl32k.sql;
SHUTDOWN IMMEDIATE;
STARTUP;
-- 之後 VARCHAR2/NVARCHAR2/RAW 最大長度變為 32767 位元組

⚠ 注意：啟用後不可逆，可能影響相容性，生產環境需充分測試。