Oracle 使用 sqlplus 匯入 SQL 檔案

小蛙上星期遇到一個棘手的問題,也才知道原來 Oracle 沒有了 GUI 介面之後是那麼樣的難操作 … 這篇文章紀錄一下關於如何把資料透過 Oracle start file (@ file) 的方式匯入沒有 GUI 介面的 Oracle。

聽起來好像不是什麼很大的問題,對大部分的資料庫老手來說,但對小蛙真的是個大問題,一直以來都是透過 GUI 操作 Oracle,真正遇到文字介面的時候還慌了一大下,問題是這樣的,某個 Tomcat + Oracle 的環境下,Tomcat 跟 Oracle 各為兩台不同機器,Tomcat 有對外 IP,而 Oracle 只能透過 Tomcat SSH (虛擬IP) 進去操作,由於環境內部幾乎所有 port 都被擋掉,連請同事打 tunnel 都沒辦法的情況下,只好使出將要匯入的資料先整理成 sql 檔案再匯入。

原本使用 PL/SQL 的 Export data 匯出 SQL Inserts,卻出現

Table xxx contains one or more CLOB columns.
Cannot export in SQL format, user PL/SQL Developer format instead.

如果使用 Oracle Export 會沒辦法達成小蛙要的需求(兩邊資料庫是不同的,加上還要額外另外匯入 Excel 的資料),只好乖乖寫程式把要匯入的資料整理成 SQL Inserts format。怎麼把資料或 Excel 匯出成 SQL format 就不贅述了,有幾點要注意:

  • 原本程式裡的 PreparedStatement 要全部寫成 Statement
  • 從 Excel 撈出的資料要檢查其正確性(單雙引號等特殊字元)。
  • JXL 對於 Date Format 的 Cell 撈出來後會有問題(這個在另一篇詳述)。

資料全部匯出之後,在 Oracle 機器下進入 sqlplus,首先小蛙第一次忘了把結果紀錄下來(spool filename),到最後根本不知道哪些有問題哪些沒問題,只好乖乖的把資料刪掉重匯(好死不死竟然輸入了commit;),這該死的資料庫,一個主表帶著一堆子表,並擁有 FK 關係,更令人噴飯的是通通沒有設定 on delete cascade … 只好,先一個一個子表清光光後,再清主表(這邊其實可以把既有的 FK 刪除,再新增有 on delete cascade 屬性的 FK,而且很怪的是 PL/SQL 可以使用 delete from table cascade 的語法,而在 Oracle sqlplus 中小蛙輸入卻發生錯誤),好,清完之後第二次謹慎點!

SQL> spool import_result.log
SQL> @ import_file_name.sql
新增一筆資料
新增一筆資料
...
...
新增一筆資料
SQL> commit;
SQL> spool off

終於,大功告成。如果有更好的做法,還請各位高手不吝留言給小蛙,感恩萬分!

參考資料

  1. Oracle Database – SQLPLUS常用指令 @ 資訊理想化的延伸

Oracle 系列文章:

Excel 相關文章:

    發佈留言

    發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

    這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料