oracle文字與格式字符串不匹配的解決

症狀：c/s程序本來運行正常，一次重裝系統，重裝oracle客戶端後，insert日期格式數據，出現題目中錯誤。 解決辦法：

Oracle時間格式討論

在網上看見不少oracle新學者對oracle的時間格式很煩，經常是設計了一個帶時間字段的表之後，向表插入數據失敗。回想起自己剛開始學習oracle時，也對這個時間格式摸不著頭腦，雖然是小小的問題，高手們不屑於討論，但對初學者來講，這也是一個不大不小的坎，沒找到門路的話， 可能幾天都過不去，其實就是一層窗戶紙，沒什麼難的。這裡做一個簡單的小結，因水平有限，請高手補充指正。

1 oracle對時間格式的數據存貯

oracle數據庫中存放時間格式的數據，是以oracle特定的格式存貯的，占7個字節，與查詢時顯示的時間格式無關，具體哪個字節表示什麼，我不太清楚，請高手補充。存貯的時間包括年月日時分秒，最小精度為秒，不存貯秒以下的時間單位。因此在一些前台支持毫秒級的程序（如PB客戶端程序）連接到oracle數據庫時應注意這點。查詢時顯示的時間格式，由會話環境決定，或者由用戶定義，與數據庫無關。

2 oracle時間顯示形式

通常，客戶端與數據庫建立起連接後，oracle就會給一個缺省的時間格式數據的顯示形式，與所使用的字符集有關。一般顯示年月日，而不顯示時分秒。例如，使用us7ascii字符集（或者是其他的英語字符集）時，缺省的時間格式顯示為：28-Jan-2003，而使用zhs16gbk字符集（或其他中文字符集）時時間格式缺省顯示為：2003-1月-28.向表中插入數據時，如果不使用轉換函數，則時間字段的格式必須遵從會話環境的時間格式，否則不能插入。查看當前會話的時間格式，可以使用以下的SQL語句：SQL> select sysdate from dual；（只能按照此語句的結果，才能插入數據）

3 向oracle表中插入時間格式的數據

向oracle表插入時間格式的數據是一個相對來講比較麻煩的事情，要麼你嚴格遵從當前會話的時間格式書寫你的時間值，要麼使用轉換函數自定義時間數據的格式。有兩個與時間相關的轉換函數：to_char 和 to_date .to_char（時間值，時間格式）函數將時間值轉換為字符串形式，通常在查詢中使用；to_date（字符串，時間格式）則把格式字符串轉換時間值，通常在insert 語句中使用。（呵呵，程序中沒這樣做，所以出錯）在日期格式中，各種日期格式分量這裡不作詳細的討論，請查oracle幫助文檔中這兩個函數的用法，或者查一查PL/SQL編程的書，講得很詳細的。

4 如何修改會話中的日期格式

修改當前會話中的日期格式據說有三種辦法，我只做過前兩種辦法的試驗，修改glogin.sql文件的辦法我沒有試過，不知道是不是真的能行。下面一一解說。

（1） 在sql*plus中修改當前會話的日期格式SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24：mi：ss'；將當前會話的時間格式修改為這種格式： 2003-01-28 15：23：38，即四位年-兩位月-兩位日，空格，24小時的制的小時：分鐘：秒。

這種修改方法，只對當前會話有效。注意，是對當前會話，而不是當前的sql*plus窗口。即如果你這樣修改之後，又使用connect命令以其他用戶連接到數據庫或者是連接到其他的數據庫，則這個日期格式就失效了，又恢復到缺省的日期格式。

（2） 修改注冊表（只對windows系統）

在注冊表hkey_local_machinesoftwareoraclehome0主鍵中增加一個字串（8i版本，9i也可以），字串名為nls_date_format，字串的值為你希望定義的時間格式，如： yyyy-mm-dd hh24：mi：ss ，然後重新啟動sql*plus.這種修改方法，對sql*plus窗口有效，即不論你打開多少個sql*plus窗口，缺省的都是這種時間格式。修改服務器端的注冊表無效，只有修改客戶端的注冊表才有效。

unix系統下有沒有這類似的方法，我不知道，我不會用unix或者是linux系統。

（3） 修改$oracle_homesqlplusadmin目錄中glogin.sql文件這種方法我沒有試過，不敢亂說，以免誤導大家。請了解這種方法的高手來指教。

5 日期格式的優先級

如果在一個具體的環境中，既修改了注冊表，又使用alter session命令修改了當前會話，那麼哪一種修改方法有效呢？alter session命令有效，它的優先級是最高的。即不管當前的客戶端環境是什麼樣的，注冊表修改成什麼樣子了，只要你使用了alter session命令來修改了時間格式，那麼就以你修改後的時間格式為准。

因此，如果直接使用sql*plus交互式查詢或插入時間格式的數據，兩種方法都可以用。如果使用sql文件來進行批量的插入或者是定時執行，最好在使用時間數據前使用alter session命令來修改會話，以保證這個sql語句的正常運行與環境無關。同樣的，如果是前台的客戶端程序中需要使用到時間格式的數據，最好也在使用之前修改會話，或者使用to_char或to_date函數進行轉換（強烈推薦使用轉換函數），以保證程序運行與環境無關。

小結：

oracle數據庫中有關時間類型的數據，定義了很多函數，特別是關於時間數據的計算，非常方便，當然，方便的前提是你對oracle的時間函數比較熟悉。在這裡我有時候看到有些人存貯時間類型的數據時使用字符串類型的字段，我強烈的反對這種做法，不論是從系統的開銷，還是從時間數據的計算來講，或者是取這個數據中的某一個子數據，比如取時間中的月，取時間中的星期等等，都不如直接存貯為時間類型的數據來得方便。只要熟悉了oracle關於時間的函數，我相信大家會喜歡上oracle時間類型的數據的，非常靈活，功能強大，幾乎可以做你所能想到的關於時間的一切事情——就看你對oracle的時間函數掌握程度了以上在windows 2000 + oracle 805版本測試通過，不對之處，請高手指正。

Trackback： http://tb.blog.csdn.net/TrackBack.aspx？PostId=1497362