今回は、OracleのテーブルデータをCSVファイルに出力してみたので、共有する。sqlplusで接続し、spoolコマンドを利用すると、CSVファイル出力できることがわかった。また、sqlplusで接続した際の文字化けを回避する方法についても共有する。
前提条件
Windows端末上に、Oracleをインストール済であること。また、salesテーブルを作成済であること。Oracleのインストール手順は下記記事を参照のこと。
また、salesテーブルには、以下のデータを格納しておくものとする。
やってみたこと
sqlplusの文字化けをなくす対応
sqlplusで文字化けをなくすためには、システムロケールを日本に変更することでコマンドプロンプトの文字コードをShift_JISに変更する対応と、Oracle ClientのNLS_LANGを「JAPANESE_JAPAN.JA16SJISTILDE」に変更する対応が必要であることが判明した。その手順を以下に示す。
1) コントロールパネルを起動し、「時計と言語と地域(Clock, Language, and Region)」を選択
2) 「地域(Region)」メニューを選択
3) 「管理(Administrative)」タブを選択後、「システムロケールの変更(Change system locale)」ボタンを押下
4) 「現在のシステムロケール(Current system locale)」プルダウンで「日本語(日本)(Japanese(Japan))」を選択後、「OK」ボタンを押下
5) 下記ダイアログで再起動を促されるので、「今すぐ再起動(Restart now)」ボタンを押下
6) 再起動後、コマンドプロンプトを起動し「chcp」コマンドにより文字コードを確認すると、以下のように「932(Shift_JIS)」が設定されていることが確認できる
7) 続いて、NLS_LANGの変更を行うため、「regedit」で検索しレジストリエディタを起動
8) HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE_KEY_XE を順に検索し、「NLS_LANG」を探す
9) NLS_LANG を選択し右クリックし、「編集(Modify)」メニューを選択
10) 文字列の編集ダイアログが表示されるので、値(Value data)にShift_JISを意味する「JAPANESE_JAPAN.JA16SJISTILDE」を指定し、「OK」ボタンを押下
11) 以下のように、「NLS_LANG」の値が変更されていることが確認できる
12) sqlplusで接続し、select文を実行すると、以下のように、日本語の文字化けが起こらないことが確認できる
spoolコマンドによるCSVファイル出力
テーブルのデータをCSVファイルに出力するためには、sqlplusコマンドでOracleに接続し、spool (CSVファイル名)でCSVファイルを出力しながら、SELECT文の実行を行う。
1) 以下のプログラムを作成し、拡張子sqlでファイル保存
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | ---- sqlplusの表示設定 -- コンソールメッセージを非表示 set echo off -- 1行の桁数を1000桁に設定 set linesize 1000 -- 1ページの行数を設定 -- ページヘッダー,列ヘッダー,ページブレーク,タイトル,初期空白行などをすべて非表示に設定 set pagesize 0 -- 各行の右端のスペースを削除 set trimspool on -- 「～行が選択されました。」の表示を削除 set feedback off -- salesテーブルのデータをCSVに出力 spool c:\work\sales.csv -- csvファイルの列を出力 select 'SALE_DATE,PRODUCT_NAME,PLACE_NAME,SALES_AMOUNT' from dual; -- csvファイルの各行を出力 select '"' || TO_CHAR(SALE_DATE, 'yyyy-mm-dd hh24:mi:ss') ||'","' || PRODUCT_NAME ||'","' || PLACE_NAME ||'","' || SALES_AMOUNT || '"' from sales; -- CSV出力の終了 spool off -- sqlplus接続をログアウト exit |
なお、今回は「C:\work」フォルダ内に、「output_csv.sql」というファイル名で作成している。
2) コマンドプロンプトを起動し、「sqlplus (ユーザー)/(パスワード)@(接続識別子) @(実行するSQLファイルパス)」というコマンドを実行することで、1)で作成したプログラムを実行
3) 2)で出力されたCSVファイルの内容は以下の通り
なお、1) で作成したプログラム内のselect句で各カラムを記載し、「 ||'”,”‘ ||」で結合しているのは、各データの囲い文字を”(ダブルクォート)、区切り文字を,(カンマ)に設定したいためである。
以下のように、select句を*(アスタリスク)にすると、固定長で右に空白を含むデータになってしまう。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ---- sqlplusの表示設定 -- コンソールメッセージを非表示 set echo off -- 1行の桁数を1000桁に設定 set linesize 1000 -- 1ページの行数を設定 -- ページヘッダー,列ヘッダー,ページブレーク,タイトル,初期空白行などをすべて非表示に設定 set pagesize 0 -- 各行の右端のスペースを削除 set trimspool on -- 「～行が選択されました。」の表示を削除 set feedback off -- 区切り文字をカンマに設定 set colsep ',' -- salesテーブルのデータをCSVに出力 spool c:\work\sales2.csv -- csvファイルの各行を出力 select * from sales; -- CSV出力の終了 spool off -- sqlplus接続をログアウト exit |
＜sqlplusコマンドで、プログラム(output_csv2.sql)を指定＞
＜上記sqlplusコマンドで出力されたCSVファイルの内容＞
