Oracle PL/SQL之令人不解的提示（nls_date_format）

剛剛測試了一段程式，報錯了，說是資料沒有找到：

l_date1 != l_date2
l_date1=26-MAY-11, l_date2=26-MAY-11

……

查來查去原來是nls_date_format惹得禍。

Oracle在使用dbms_output.put_line或fnd_file.put_line等內建程式輸出日期型參數時，會自動套用nls_date_format定義的日期格式，恰巧當前資料庫中nls_date_format定義的日期格式為DD-MON-RR，沒有時分秒，而參與比較的這兩個日期卻是帶時分秒的，並且差異就在時分秒上：

DECLARE<br /> l_date1 DATE := to_date('2011/05/26', 'YYYY/MM/DD');<br /> l_date2 DATE := to_date('2011/05/26 16:58:00', 'YYYY/MM/DD HH24:MI:SS');<br /> l_val nls_session_parameters.VALUE%TYPE;<br />BEGIN<br /> SELECT VALUE<br /> INTO l_val<br /> FROM nls_session_parameters<br /> WHERE parameter = upper('nls_date_format');<br /> dbms_output.put_line('Original: nls_date_format=' || l_val);</p><p> dbms_output.put_line('===test if two dates equal with confused info===');<br /> EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''DD-MON-RR''';<br /> IF (l_date1 != l_date2)<br /> THEN<br /> dbms_output.put_line('l_date1 != l_date2');<br /> dbms_output.put_line('l_date1=' || l_date1 || ', l_date2=' || l_date2);<br /> END IF;</p><p> dbms_output.put_line('===test if two dates equal with clear info===');<br /> EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''DD-MON-YYYY HH24:MI:SS''';<br /> IF (l_date1 != l_date2)<br /> THEN<br /> dbms_output.put_line('l_date1 != l_date2');<br /> dbms_output.put_line('l_date1=' || l_date1 || ', l_date2=' || l_date2);<br /> END IF;</p><p> --revert<br /> EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''DD-MON-RR''';<br />END;<br />

 

輸出：

Original: nls_date_format=DD-MON-RR<br />===test if two dates equal with confused info===<br />l_date1 != l_date2<br />l_date1=26-MAY-11, l_date2=26-MAY-11<br />===test if two dates equal with clear info===<br />l_date1 != l_date2<br />l_date1=26-MAY-2011 00:00:00, l_date2=26-MAY-2011 16:58:00<br />