Oracle SQL Developer 日期匯出格式問題
Oracle SQL Developer 預設的匯出日期格式會因為作業系統的語系而有所不同,
因此在輸出的SQL若使用SQLPLUS*執行,會發生日期格式錯誤。
若此時貿然使用Shell Script執行大量SQL匯入時,鐵定會吞下一堆ERROR!
(SQL Developer執行正常,因為有自動轉換的機制,但SQLPLUS*不會提供這麼好的服務)
環境描述:
- 資料庫環境:Oracle Database Express Edition 11g Release 2 (CentOS 6.4 繁體中文語系)
- Client端環境:Oracle SQL Developer 3.2 / putty (Windows 7 繁體中文版)
模擬案發經過
以一個測試用的Table為例,長這樣:
先不要管人物名稱,如果要使用Oracle SQL Developer將這張Table匯出,
要先選擇 選單>工具>資料庫匯出…,然後彈出下圖,將語系設定為UTF-8,一路下一步到底。
結果匯出的SQL檔,資料插入的部份長這樣:
1 |
Insert into TESTDB.DEMO_ORDERS_CHT (ORDER_ID,CUSTOMER_ID,ORDER_TOTAL,ORDER_TIMESTAMP,USER_NAME) values (9,2,730,to_date('05-8月 -13','DD-MON-RR'),'甄黑心'); |
這時候登入Oracle,執行以上SQL敘述句,結果…
1 2 3 4 5 6 7 8 9 10 11 |
SQL> connect Enter user-name: system Enter password: Connected. SQL> Insert into TESTDB.DEMO_ORDERS_CHT (ORDER_ID,CUSTOMER_ID,ORDER_TOTAL,ORDER_TIMESTAMP,USER_NAME) values (9,2,730,to_date('05-8月 -13','DD-MON-RR'),'甄黑心'); Insert into TESTDB.DEMO_ORDERS_CHT (ORDER_ID,CUSTOMER_ID,ORDER_TOTAL,ORDER_TIMESTAMP,USER_NAME) values (9,2,730,to_date('05-8月 -13','DD-MON-RR'),'甄黑心') * ERROR at line 1: ORA-01843: not a valid month SQL> |
日期格式不對,但相同的敘述句貼回 Oracle SQL Developer 卻可以正常執行,真是黑心啊!
解決方法
研判應該是因為SQLPLUS無法辨識中文的日期格式,
此時最簡單的解決方法,就是用正確的設定重新匯製SQL。
首先,到 選單>工具>偏好設定,開啟偏好設定視窗。
然後到 資料庫>NLS,可以看到日期的格式與剛剛匯出的SQL日期格式相同。
將 DD-MON-RR HH.MI.SSXFF AM 改成通用格式 YYYY-MM-DD HH24:MI:SS.XFF AM
按確定,重複剛剛的SQL匯出程序。
變更設定後的匯出結果中,產生的INSERT語法如下:
1 |
Insert into TESTDB.DEMO_ORDERS_CHT (ORDER_ID,CUSTOMER_ID,ORDER_TOTAL,ORDER_TIMESTAMP,USER_NAME) values (9,2,730,to_date('2013-08-05','YYYY-MM-DD'),'甄黑心'); |
用putty登入SQLPLUS測試:
1 2 3 |
SQL> Insert into TESTDB.DEMO_ORDERS_CHT (ORDER_ID,CUSTOMER_ID,ORDER_TOTAL,ORDER_TIMESTAMP,USER_NAME) values (9,2,730,to_date('2013-08-05','YYYY-MM-DD'),'甄黑心'); 1 row created. |
大功告成!