SQL Developerのインポート機能を使用してファイルをテーブルにインポートする場合、その方法をいくつかの選択肢の中から選ぶことが出来る。どれも最終的にはテーブルが作られて*1、そのテーブルにSELECT文などのSQLでアクセス可能になる。ただし、どの方法も違いがあるので、インポートの手順と違いなどを書いていきたい。
共通な手順
新しいテーブルを作ってそこにデータを入れる場合、下記画像のように「表（フィルタ対象）」のところで右クリック -> データのインポートを選ぶ。
逆に、既存のテーブルにインポートしたい場合は、そのテーブルにフォーカス当たっている状態で右クリック -> データのインポートを選ぶ。
ファイル（KEN_ALL.CSV）を選ぶ。
ヘッダーにデフォルトでチェックが入っているが、KEN_ALL.CSVにヘッダー行は無いのでチェックを外す。エンコーディングはクライアントマシンがWindowsであればShift_JISになっていると思うので、そのまま。前やったとき（SQL Developerからテーブルにデータを入れる - kagamihogeのblog）はココでエンコーディング選べなかった気がするけど、今このエントリを書くために使用しているバージョンではここで選べるようになっている。
行の終了文字・左囲み・右囲みもデフォルトのままでOK
挿入
インポート方法に「挿入」を選んだ場合。
デフォルトで全列が選択状態になっているので、そのまま次へ。
CSVの各列と新規作成するテーブルの列名の対応付けを入力する。
インポート前にパラメータを検証のボタンを押して、ぜんぶにSUCCESSがついたところ。
終了押すとデータ登録処理が行われる。
なお、CSVファイルの一行目がヘッダー行でそこに列名に当たるものが記載されていて、ウィザードの最初のステップで「ヘッダー」にチェックを入れている場合、下記画像のようにヘッダー列がインポートされるテーブルの列名として使われる。もちろん、ステップ4で列名を任意のものに変更できる。
実際には何をしているのか？
マニュアルには「Insert: For new tables, the table will be created and data will be inserted.」書かれている。実際には、ウィザードで入力したテーブル定義と入力ファイルを基にCREATE TABLE -> INSERTのSQL文を発行している。
インポートに失敗した場合、SQL Develper上のワークシートに発行しようとしたSQL文が表示される。（ユーザのドキュメントフォルダ）\Local Settings\Temp にSQLファイルが一時ファイルとして作られている。原因が分かったらその箇所を修正したSQLをSQL Developer上から流すなり、SQLファイルを実行するなりすれば良い。
このウィザードは「ヘッダー」にデフォルトでチェックが入っている事から見るに、そういう形式のcsvファイルを前提としているように思われる。列定義のオプションは簡素なものなので、csvファイルがテーブルになりさえすればよい、かつ、SQLを手書きする手間をなるべく省きたい、という用途を想定しているのではなかろうか。
挿入スクリプト
挿入とアンマリ変わらない気がする。
外部表
前述の通り、インポートウィザードを使う場合はcsvファイルの先頭にヘッダー行があったほうが実験がやりやすい。よって、ここからはken_all.csvは下記を一行目に加えたものとして扱う。これは、ただ単にインポート先テーブルの列名を想定したものでしかないので、無いとインポートできないとかそういうことでは無い。
JIS_CODE,POSTAL_CODE_OLD,POSTAL_CODE,PREFECTURE_KANA,CITY_KANA,TOWN_KANA,PREFECTURE_KANJI,CITY_KANJI,TOWN_KANJI,TOWN_DIVIDE_FLAG,KOAZA_BANCHI_FLAG,TYHOUME_FLAG,HAS_SOME_TOWN_FLAG,UPDATE_STATE_FLAG,UPDATE_REASON
ウィザードの最初のステップはこうする。
インポート方法に外部表を選択する。
サイズとかデータ型とかを適当に編集する。
外部表作るときのORGANIZATION EXTERNAL句にあたる部分の入力画面。「サーバー側パスからファイル」という項目だけがかなり謎なのを除いて、他は大体見たとおり。謎項目については後述。
検証画面になるので検証ボタン押してSUCCESS確認して終了押す。
そうすると、SQL Developerのワークシートが新しく開かれて、インポート用のSQLが表示＆編集可能状態になる。下記が上記までのウィザードによって生成したSQL。あとはこれを「スクプリトの実行（F5）」するだけ。
SET DEFINE OFF --CREATE OR REPLACE DIRECTORY EXTERNAL_TABLE_DIR AS 'KEN_ALL_test.CSV'; --GRANT READ ON DIRECTORY EXTERNAL_TABLE_DIR TO USER; --GRANT WRITE ON DIRECTORY EXTERNAL_TABLE_DIR TO USER; --drop table POSTAL_CODE_EXT; CREATE TABLE POSTAL_CODE_EXT ( JIS_CODE CHAR(5), POSTAL_CODE_OLD CHAR(5), POSTAL_CODE CHAR(7), PREFECTURE_KANA VARCHAR2(100), CITY_KANA VARCHAR2(400), TOWN_KANA VARCHAR2(400), PREFECTURE_KANJI VARCHAR2(100), CITY_KANJI VARCHAR2(300), TOWN_KANJI VARCHAR2(300), TOWN_DIVIDE_FLAG CHAR(1), KOAZA_BANCHI_FLAG CHAR(1), TYHOUME_FLAG CHAR(1), HAS_SOME_TOWN_FLAG CHAR(1), UPDATE_STATE_FLAG CHAR(1), UPDATE_REASON CHAR(1)) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY EXTERNAL_TABLE_DIR ACCESS PARAMETERS (records delimited BY '\r\n' CHARACTERSET JA16SJISTILDE BADFILE EXTERNAL_TABLE_LOG:'KEN_ALL.bad' DISCARDFILE EXTERNAL_TABLE_LOG:'KEN_ALL.discard' LOGFILE EXTERNAL_TABLE_LOG:'KEN_ALL.log' skip 1 fields terminated BY ',' OPTIONALLY ENCLOSED BY '"' AND '"' lrtrim missing field VALUES are NULL ( JIS_CODE CHAR(4000), POSTAL_CODE_OLD CHAR(4000), POSTAL_CODE CHAR(4000), PREFECTURE_KANA CHAR(4000), CITY_KANA CHAR(4000), TOWN_KANA CHAR(4000), PREFECTURE_KANJI CHAR(4000), CITY_KANJI CHAR(4000), TOWN_KANJI CHAR(4000), TOWN_DIVIDE_FLAG CHAR(4000), KOAZA_BANCHI_FLAG CHAR(4000), TYHOUME_FLAG CHAR(4000), HAS_SOME_TOWN_FLAG CHAR(4000), UPDATE_STATE_FLAG CHAR(4000), UPDATE_REASON CHAR(4000) ) ) LOCATION ('KEN_ALL.CSV') ) REJECT LIMIT UNLIMITED; select * from POSTAL_CODE_EXT WHERE ROWNUM <= 100;
この自動生成されるスクリプトについてのメモ
ディレクトリオブジェクトの作成と権限付与はコメントアウトされている。これはおそらく、ディレクトリオブジェクトの作成とかは事前にやってある事が多いからでしょう。必要だったら実行してね、という意味合いかと。
「サーバ側パスからファイル」という謎入力項目は、DEFAULT DIRECTORYで指定されるディレクトリオブジェクトの実際のファイルパスになるみたい。で、LOCATIONで指定される外部表の元となるファイル名はウィザードの最初に選んだcsvファイルのファイル名になる（らしい） 大変に紛らわしい気がする。
よって、このウィザードで外部表を作る場合、生成されたSQLを自分の好きなパラメータに編集してから実行するのが良いと思う。テンプレートを作ってくれると考えれば、ソレナリに便利な機能でしょう。
これは俺の私見だけど、他の種類のインポートと外部表は別モンなので、一緒のウィザードで扱うってのはどうなんだろうねぇ。外部表も他のインポートもファイルデータをテーブルとして扱えるようにするという点では同じである。しかし、クライアントマシンにあるデータを挿入するのと、サーバマシン上にあるファイルを外部表として見えるようにするのとでは、結果は同じでも過程はマッタク別モンなので。
ステージング外部表
外部表（このウィザードでは「ステージング表名」のこと）を作ったあと、その外部表から実テーブルにINSERT...SELECTする。ウィザード上の画面操作的には外部表とほとんど変わらない。
この画面で、インポートしたい実テーブル名を入力する。外部表名は、実テーブル_STAGEという補間がされるが、任意のものを入力可能。「ステージング表のコミットと削除」のチェックの有無による変化は後述。
生成されるSQLはこんな感じ。CREATE TABLE 実テーブル → CREATE TABLE ステージング外部表 → INSERT INTO 実テーブル SELECT ステージング外部表 → DROP TABLE ステージング外部表、といった流れ。
SET DEFINE OFF CREATE TABLE POSTAL_CODE_EXT ( JIS_CODE CHAR(5), POSTAL_CODE_OLD CHAR(5), POSTAL_CODE CHAR(7), PREFECTURE_KANA VARCHAR2(100), CITY_KANA VARCHAR2(400), TOWN_KANA VARCHAR2(400), PREFECTURE_KANJI VARCHAR2(100), CITY_KANJI VARCHAR2(300), TOWN_KANJI VARCHAR2(300), TOWN_DIVIDE_FLAG CHAR(1), KOAZA_BANCHI_FLAG CHAR(1), TYHOUME_FLAG CHAR(1), HAS_SOME_TOWN_FLAG CHAR(1), UPDATE_STATE_FLAG CHAR(1), UPDATE_REASON CHAR(1)); --CREATE OR REPLACE DIRECTORY EXTERNAL_TABLE_DIR AS 'KEN_ALL.CSV'; --GRANT READ ON DIRECTORY EXTERNAL_TABLE_DIR TO USER; --GRANT WRITE ON DIRECTORY EXTERNAL_TABLE_DIR TO USER; --drop table POSTAL_CODE_EXT_STAGE; CREATE TABLE POSTAL_CODE_EXT_STAGE (JIS_CODE CHAR(5), POSTAL_CODE_OLD CHAR(5), POSTAL_CODE CHAR(7), PREFECTURE_KANA VARCHAR2(100), CITY_KANA VARCHAR2(400), TOWN_KANA VARCHAR2(400), PREFECTURE_KANJI VARCHAR2(100), CITY_KANJI VARCHAR2(300), TOWN_KANJI VARCHAR2(300), TOWN_DIVIDE_FLAG CHAR(1), KOAZA_BANCHI_FLAG CHAR(1), TYHOUME_FLAG CHAR(1), HAS_SOME_TOWN_FLAG CHAR(1), UPDATE_STATE_FLAG CHAR(1), UPDATE_REASON CHAR(1)) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY EXTERNAL_TABLE_DIR ACCESS PARAMETERS (records delimited BY '\r\n' CHARACTERSET JA16SJISTILDE BADFILE EXTERNAL_TABLE_LOG:'KKK_KEN_ALL.bad' NODISCARDFILE LOGFILE EXTERNAL_TABLE_LOG:'KKK_KEN_ALL.log' skip 1 fields terminated BY ',' OPTIONALLY ENCLOSED BY '"' AND '"' lrtrim missing field VALUES are NULL ( JIS_CODE CHAR(4000), POSTAL_CODE_OLD CHAR(4000), POSTAL_CODE CHAR(4000), PREFECTURE_KANA CHAR(4000), CITY_KANA CHAR(4000), TOWN_KANA CHAR(4000), PREFECTURE_KANJI CHAR(4000), CITY_KANJI CHAR(4000), TOWN_KANJI CHAR(4000), TOWN_DIVIDE_FLAG CHAR(4000), KOAZA_BANCHI_FLAG CHAR(4000), TYHOUME_FLAG CHAR(4000), HAS_SOME_TOWN_FLAG CHAR(4000), UPDATE_STATE_FLAG CHAR(4000), UPDATE_REASON CHAR(4000) ) ) LOCATION ('KEN_ALL.CSV') ) REJECT LIMIT UNLIMITED; select * from POSTAL_CODE_EXT_STAGE WHERE ROWNUM <= 100; whenever sqlerror exit rollback; begin INSERT INTO POSTAL_CODE_EXT (JIS_CODE, POSTAL_CODE_OLD, POSTAL_CODE, PREFECTURE_KANA, CITY_KANA, TOWN_KANA, PREFECTURE_KANJI, CITY_KANJI, TOWN_KANJI, TOWN_DIVIDE_FLAG, KOAZA_BANCHI_FLAG, TYHOUME_FLAG, HAS_SOME_TOWN_FLAG, UPDATE_STATE_FLAG, UPDATE_REASON) SELECT JIS_CODE, POSTAL_CODE_OLD, POSTAL_CODE, PREFECTURE_KANA, CITY_KANA, TOWN_KANA, PREFECTURE_KANJI, CITY_KANJI, TOWN_KANJI, TOWN_DIVIDE_FLAG, KOAZA_BANCHI_FLAG, TYHOUME_FLAG, HAS_SOME_TOWN_FLAG, UPDATE_STATE_FLAG, UPDATE_REASON FROM POSTAL_CODE_EXT_STAGE ; COMMIT; EXECUTE IMMEDIATE 'DROP TABLE POSTAL_CODE_EXT_STAGE'; end; /
「ステージング表のコミットと削除」のチェックを外すと、最後のINSERT..SELECTの部分がこんな感じになる。
INSERT INTO POSTAL_CODE_EXT （中略） SELECT （中略） FROM POSTAL_CODE_EXT_STAGE ;
この自動生成されるスクリプトについてのメモ。
そもそもの外部表の使い方として、次のようなものが考えられる。外部システムなどから提供されるファイルを外部表として取り込み、そこから更に他のテーブルとジョインしたりとかして諸々の加工を施してから、実テーブルにデータを取り込みたい、などの場合にこのウィザードで作成したSQLを雛形として使う、のだと思われる。外部ファイルを、何らかの処理において一時的にテーブルとして扱いたい、という場合のスケルトンといったとこでしょうか。
SQL*Loaderユーティリティ
この画面で、インポート先の新規作成する実テーブル名を入力する。「SQLワークシートへの作成スクリプトの送信」のチェックの有無による変化は後述。
終了ボタンを押すとテーブルが作成される。「SQLワークシートへの作成スクリプトの送信」にチェックが入っていると、下記のようなSQLが表示される。チェックしてないと表示されない。
SET DEFINE OFF CREATE TABLE POSTAL_CODE_IMP ( JIS_CODE CHAR(5), POSTAL_CODE_OLD CHAR(5), POSTAL_CODE CHAR(7), PREFECTURE_KANA VARCHAR2(100), CITY_KANA VARCHAR2(400), TOWN_KANA VARCHAR2(400), PREFECTURE_KANJI VARCHAR2(100), CITY_KANJI VARCHAR2(300), TOWN_KANJI VARCHAR2(300), TOWN_DIVIDE_FLAG CHAR(1), KOAZA_BANCHI_FLAG CHAR(1), TYHOUME_FLAG CHAR(1), HAS_SOME_TOWN_FLAG CHAR(1), UPDATE_STATE_FLAG CHAR(1), UPDATE_REASON CHAR(1)); /* C:\sqlloadertest（文字化けのため後略）*/
なんか最後の行が文字化けするんだけど、「生成されるファイルのディレクトリ」で指定した場所に生成された、SQL*Loaderの起動スクリプトである*.bat or *.shを実行してください、と書かれているのだと思う。
自動生成されるSQL*Loaderを起動するバッチファイルはこんな感じ。
sqlldr CONTROL=KKK_KEN_ALL.ctl LOG=C:\sqlloadertest\KKK_KEN_ALL.log BAD=C:\sqlloadertest\KKK_KEN_ALL.bad skip=1
環境に合わせてバッチファイルを書き換える。俺の環境ではローカルマシンにOracle XEがインストールしてあるのでsqlldrへのパスをフルパスにしてしまう。それと、ユーザとパスワードとSIDをOracleを動かしてるマシンに合致するものにしておく。
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlldr kagamihoge/xxxx@XE2 CONTROL=KKK_KEN_ALL.ctl LOG=C:\sqlloadertest\KKK_KEN_ALL.log BAD=C:\sqlloadertest\KKK_KEN_ALL.bad skip=1
自動生成されるコントロールファイルはこんな感じ。
load data CHARACTERSET JA16SJISTILDE infile 'KKK_KEN_ALL.CSV' "str '\r\n'" append into table POSTAL_CODE_IMP fields terminated by ',' OPTIONALLY ENCLOSED BY '"' AND '"' trailing nullcols ( JIS_CODE CHAR(4000), POSTAL_CODE_OLD CHAR(4000), POSTAL_CODE CHAR(4000), PREFECTURE_KANA CHAR(4000), CITY_KANA CHAR(4000), TOWN_KANA CHAR(4000), PREFECTURE_KANJI CHAR(4000), CITY_KANJI CHAR(4000), TOWN_KANJI CHAR(4000), TOWN_DIVIDE_FLAG CHAR(4000), KOAZA_BANCHI_FLAG CHAR(4000), TYHOUME_FLAG CHAR(4000), HAS_SOME_TOWN_FLAG CHAR(4000), UPDATE_STATE_FLAG CHAR(4000), UPDATE_REASON CHAR(4000) )
これも自動生成されたものを雛形として、必要なパラメータとかを追加して使うことになるんじゃないか、と思う。
まとめ
SQL Developerのインポート機能を使用する時に選べる方法ごとの大まかな違いのまとめは下記の通り。
| 種類 | 実際に行われること |
| 挿入 | CREATE TABLE → INSERT |
| 挿入スクリプト | CREATE TABLE → INSERT |
| 外部表 | CREATE TABLE 外部表 |
| ステージング外部表 | CREATE TABLE 実テーブル → CREATE TABLE ステージング外部表 → INSERT INTO 実テーブル SELECT ステージング外部表 → DROP TABLE ステージング外部表 |
| SQL*Loaderユーティリティ | CREATE TABLE → SQL*Loader |
SQL Developerが何か独自のインポート機能を持ってるわけではなく、実態は何らかのSQLや他ツール用のパラメータを生成して実行しているだけです。なので、インポートできないエラーとかになった場合、生成されたSQLなどがその環境のOracleでちゃんと使えるものかどうか調べる必要がある。
*1:既存テーブルが対象であればそこにデータが入れられて
