解決ora-01861文字與格式字符串不匹配
2024-07-21 02:32:38
供稿：網(wǎng)友
癥狀：c/s程序本來運行正常，一次重裝系統(tǒng)，重裝Oracle客戶端后，insert日期格式數(shù)據(jù)，出現(xiàn)題目中錯誤。 解決辦法： Oracle時間格式討論 在網(wǎng)上看見不少oracle新學(xué)者對oracle的時間格式很煩，經(jīng)常是設(shè)計了一個帶時間字段的表之后，向表插入數(shù)據(jù)失敗。回想起自己剛開始學(xué)習(xí)oracle時，也對這個時間格式摸不著頭腦，雖然是小小的問題，高手們不屑于討論，但對初學(xué)者來講，這也是一個不大不小的坎，沒找到門路的話， 可能幾天都過不去，其實就是一層窗戶紙，沒什么難的。這里做一個簡單的小結(jié)，因水平有限，請高手補充指正。 1 oracle對時間格式的數(shù)據(jù)存貯 oracle數(shù)據(jù)庫中存放時間格式的數(shù)據(jù)，是以oracle特定的格式存貯的，占7個字節(jié)，與查詢時顯示的時間格式無關(guān)，具體哪個字節(jié)表示什么，我不太清楚，請高手補充。存貯的時間包括年月日時分秒，最小精度為秒，不存貯秒以下的時間單位。因此在一些前臺支持毫秒級的程序（如PB客戶端程序）連接到oracle數(shù)據(jù)庫時應(yīng)注重這點。查詢時顯示的時間格式，由會話環(huán)境決定，或者由用戶定義，與數(shù)據(jù)庫無關(guān)。 2 oracle時間顯示形式 通常，客戶端與數(shù)據(jù)庫建立起連接后，oracle就會給一個缺省的時間格式數(shù)據(jù)的顯示形式，與所使用的字符集有關(guān)。一般顯示年月日，而不顯示時分秒。例如，使用us7ascii字符集（或者是其他的英語字符集）時，缺省的時間格式顯示為：28-Jan-2003，而使用zhs16gbk字符集（或其他中文字符集）時時間格式缺省顯示為：2003-1月-28.向表中插入數(shù)據(jù)時，假如不使用轉(zhuǎn)換函數(shù)，則時間字段的格式必須遵從會話環(huán)境的時間格式，否則不能插入。查看當(dāng)前會話的時間格式，可以使用以下的SQL語句：SQL> select sysdate from dual；（只能按照此語句的結(jié)果，才能插入數(shù)據(jù)） 3 向oracle表中插入時間格式的數(shù)據(jù) 向oracle表插入時間格式的數(shù)據(jù)是一個相對來講比較麻煩的事情，要么你嚴格遵從當(dāng)前會話的時間格式書寫你的時間值，要么使用轉(zhuǎn)換函數(shù)自定義時間數(shù)據(jù)的格式。有兩個與時間相關(guān)的轉(zhuǎn)換函數(shù)：to_char 和 to_date .to_char（時間值，時間格式）函數(shù)將時間值轉(zhuǎn)換為字符串形式，通常在查詢中使用；to_date（字符串，時間格式）則把格式字符串轉(zhuǎn)換時間值，通常在insert 語句中使用。（呵呵，程序中沒這樣做，所以出錯）在日期格式中，各種日期格式分量這里不作具體的討論，請查oracle幫助文檔中這兩個函數(shù)的用法，或者查一查PL/SQL編程的書，講得很具體的。 4 如何修改會話中的日期格式 修改當(dāng)前會話中的日期格式據(jù)說有三種辦法，我只做過前兩種辦法的試驗，修改glogin.sql文件的辦法我沒有試過，不知道是不是真的能行。下面一一解說。 （1） 在sql*plus中修改當(dāng)前會話的日期格式SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24：mi：ss'；將當(dāng)前會話的時間格式修改為這種格式： 2003-01-28 15：23：38，即四位年-兩位月-兩位日，空格，24小時的制的小時：分鐘：秒。 這種修改方法，只對當(dāng)前會話有效。注重，是對當(dāng)前會話，而不是當(dāng)前的sql*plus窗口。即假如你這樣修改之后，又使用connect命令以其他用戶連接到數(shù)據(jù)庫或者是連接到其他的數(shù)據(jù)庫，則這個日期格式就失效了，又恢復(fù)到缺省的日期格式。 （2） 修改注冊表（只對windows系統(tǒng)） 在注冊表hkey_local_machinesoftwareoraclehome0主鍵中增加一個字串（8i版本，9i也可以），字串名為nls_date_format，字串的值為你希望定義的時間格式，如： yyyy-mm-dd hh24：mi：ss ，然后重新啟動sql*plus.這種修改方法，對sql*plus窗口有效，即不論你打開多少個sql*plus窗口，缺省的都是這種時間格式。修改服務(wù)器端的注冊表無效，只有修改客戶端的注冊表才有效。 unix系統(tǒng)下有沒有這類似的方法，我不知道，我不會用unix或者是linux系統(tǒng)。 （3） 修改$oracle_homesqlplusadmin目錄中g(shù)login.sql文件這種方法我沒有試過，不敢亂說，以免誤導(dǎo)大家。請了解這種方法的高手來指教。
5 日期格式的優(yōu)先級 假如在一個具體的環(huán)境中，既修改了注冊表，又使用alter session命令修改了當(dāng)前會話，那么哪一種修改方法有效呢？alter session命令有效，它的優(yōu)先級是最高的。即不管當(dāng)前的客戶端環(huán)境是什么樣的，注冊表修改成什么樣子了，只要你使用了alter session命令來修改了時間格式，那么就以你修改后的時間格式為準。 因此，假如直接使用sql*plus交互式查詢或插入時間格式的數(shù)據(jù)，兩種方法都可以用。假如使用sql文件來進行批量的插入或者是定時執(zhí)行，最好在使用時間數(shù)據(jù)前使用alter session命令來修改會話，以保證這個sql語句的正常運行與環(huán)境無關(guān)。同樣的，假如是前臺的客戶端程序中需要使用到時間格式的數(shù)據(jù)，最好也在使用之前修改會話，或者使用to_char或to_date函數(shù)進行轉(zhuǎn)換（強烈推薦使用轉(zhuǎn)換函數(shù)），以保證程序運行與環(huán)境無關(guān)。 小結(jié)： oracle數(shù)據(jù)庫中有關(guān)時間類型的數(shù)據(jù)，定義了很多函數(shù)，非凡是關(guān)于時間數(shù)據(jù)的計算，非常方便，當(dāng)然，方便的前提是你對oracle的時間函數(shù)比較熟悉。在這里我有時候看到有些人存貯時間類型的數(shù)據(jù)時使用字符串類型的字段，我強烈的反對這種做法，不論是從系統(tǒng)的開銷，還是從時間數(shù)據(jù)的計算來講，或者是取這個數(shù)據(jù)中的某一個子數(shù)據(jù)，比如取時間中的月，取時間中的星期等等，都不如直接存貯為時間類型的數(shù)據(jù)來得方便。只要熟悉了oracle關(guān)于時間的函數(shù)，我相信大家會喜歡上oracle時間類型的數(shù)據(jù)的，非常靈活，功能強大，幾乎可以做你所能想到的關(guān)于時間的一切事情——就看你對oracle的時間函數(shù)把握程度了以上在windows 2000 + oracle 805版本測試通過，不對之處，請高手指正。 Trackback： http://tb.blog.csdn.net/TrackBack.aspx？PostId=1497362
