同事報案,某段 Oracle Package 更新 Script 檔 (.pkb) 送交 DBA 執行有錯,回報訊息如下:

SQL> @test.pkb
SP2-0734: unknown command beginning "CREATE ..." - rest of line ignored.
SP2-0734: unknown command beginning "function f..." - rest of line ignored.
SP2-0734: unknown command beginning "v_porpI..." - rest of line ignored.
SP2-0734: unknown command beginning "av_propCod..." - rest of line ignored.
SP2-0734: unknown command beginning "flag varch..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
SP2-0734: unknown command beginning ") return v..." - rest of line ignored.
SP2-0042: unknown command "is" - rest of line ignored.
SP2-0734: unknown command beginning "lv_step   ..." - rest of line ignored.
SP2-0734: unknown command beginning "lv_ccou..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
SP2-0734: unknown command beginning "lv_testm..." - rest of line ignored.
SP2-0734: unknown command beginning "int_len   ..." - rest of line ignored.
7622  /
ERROR:
ORA-00972: identifier is too long 

該 .pkb 檔為 PLSQL Developer 軟體由測試驗證好的 Oracle 資料庫匯出,程式內容不應有錯,而錯誤為大量無法識別的指令,老骨頭心中警鈴大作 - 是編碼問題?

由訊息來看,DBA 似乎是使用 SQLPlus 載入 .pkb 執行,在開發機用 SQLPlus 成功重現相同錯誤並有重大發現:

第一行錯誤訊息出現"嚜澧"兩個怪字,是 UTF-8 BOM!

使用 Notepad++ 開啟 test.pkb,果不其然編碼為 UTF-8 with BOM,轉換為 ANSI/BIG5 存檔用 SQLPlus 再測一次,錯誤消失無蹤。

【後記心得】

  1. 測試將檔案轉換為無 BOM UTF-8 (上圖藍底選項)亦可順利執行,但 Package 裡的中文變成亂碼,需轉為 ANSI(BIG5) 結果才正確
  2. Oracle 工具軟體轉出之 Script 檔供自己使用沒問題,轉交 SQLPlus 執行需留意編碼問題
  3. 實測 SQLPlus 至 12.1 版仍無法識別檔案 BOM 記號,至於移除 BOM 的 UTF-8 檔案可執行但中文變亂碼可能與 Oracle 資料庫語系設定有關,但我缺乏進行相關測試的設備與動機(笑),有緣再查


Comments

# by Nick

SQL PLUS 會做這一段設定 SET NLS_LANG=AMERICAN_AMERICAN.ZHT16BIG5

Post a comment


55 - 38 =