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. |
大功告成！
