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
終於，大功告成。如果有更好的做法，還請各位高手不吝留言給小蛙，感恩萬分！
參考資料
Oracle 系列文章：
- 修改 Oracle Sequence
- Oracle 使用 sqlplus 匯入 SQL 檔案
- 使用 Oracle sqlplus 將查詢結果匯出到檔案
- 官方 Client – Oracle SQL Developer 安裝教學
- Oracle 匯入/匯出 Excel (透過 SQL Developer)
Excel 相關文章：