萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

ORA-01861 字面值與格式字串不匹配:NLS_DATE_FORMAT 完整解決方案

重裝系統/客戶端後,INSERT 日期欄位報 ORA-01861: literal does not match format string。

根本原因

  • Oracle 儲存 DATE 型態為內部 7 位元組格式 (年月日時分秒),與顯示格式無關。
  • 顯示格式由 Session 環境 決定,預設取決於 NLS_TERRITORY/NLS_LANGUAGE (如 US7ASCII → DD-MON-YYYY,ZHS16GBK → YYYY-MM-DD)。
  • 若輸入字串不符合目前 Session 的 NLS_DATE_FORMAT,直接插入會失敗。

三種修改 Session 日期格式的方法 (優先序高→低)

  1. ALTER SESSION (最高優先權,僅影響當前 Session)
    ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
  2. Windows 註冊表 (影響所有新 SQL*Plus 視窗)
    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
    新增字串值: NLS_DATE_FORMAT = YYYY-MM-DD HH24:MI:SS
    修改伺服器端註冊表無效,必須在客戶端機器修改。
  3. 修改 $ORACLE_HOME/sqlplus/admin/glogin.sql (全域預設)
    -- glogin.sql 內容
    ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
    未親測,理論上每次 SQL*Plus 啟動時執行。

最佳實務:程式碼中強制轉換

-- 插入
INSERT INTO t (dt_col) VALUES (TO_DATE('2024-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'));

-- 查詢
SELECT TO_CHAR(dt_col, 'YYYY-MM-DD HH24:MI:SS') FROM t;
  • 不依賴環境設定,跨平台/跨客戶端穩定。
  • 避免隱性轉換導致執行計畫劣化。

查看目前 Session 格式

SELECT SYSDATE FROM DUAL;  -- 觀察輸出格式即為當前 NLS_DATE_FORMAT
copyright © 萬盛學電腦網 all rights reserved