概要
SELECT文で取得した結果を、HTML/XML/EXCEL/CSV/Markdownの形式で出力する方法を記載する。
環境
DBサーバ
OS : Red Hat Enterprise Linux Server 6.4
Oracle : Database 11g Enterprise Edition Release 11.2.0.4.0 -64bit
クライアント
OS : OS X Yosemite 10.10.3
Oracle : Instant Client for Mac OS X (Intel x86) Client Shared Library 11.2.0.4.0 - 64-bit
内容
1.HTML形式
コマンド
cat << EOF | sqlplus -S -M 'HTML ON' sys/<password>@<接続Netサービス名> as sysdba > output.html
set feedback off
select * from v\$logfile;
EOF
結果
output.html
2.XML形式
※「Oracle XML Database」コンポーネントのインストールが必須。
コマンド
cat << EOF | sqlplus -S <password>@<接続Netサービス名> as sysdba > output.xml
set pages 0
set lines 10000
set long 10000000
set trimspool on
col xml for a10000
select dbms_xmlgen.getxml('select * from v\$logfile') xml from dual;
EOF
結果
output.xml
<?xml version="1.0"?>
<ROWSET>
<ROW>
<GROUP_x0023_>1</GROUP_x0023_>
<TYPE>ONLINE</TYPE>
<MEMBER>/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_1_bn1rckwt_.log</MEMBER>
<IS_RECOVERY_DEST_FILE>NO</IS_RECOVERY_DEST_FILE>
</ROW>
<ROW>
<GROUP_x0023_>1</GROUP_x0023_>
<TYPE>ONLINE</TYPE>
<MEMBER>/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_1_bn1rcl1c_.log</MEMBER>
<IS_RECOVERY_DEST_FILE>YES</IS_RECOVERY_DEST_FILE>
</ROW>
…（省略）…
<ROW>
<GROUP_x0023_>3</GROUP_x0023_>
<TYPE>ONLINE</TYPE>
<MEMBER>/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_3_bn1rcl6l_.log</MEMBER>
<IS_RECOVERY_DEST_FILE>YES</IS_RECOVERY_DEST_FILE>
</ROW>
</ROWSET>
3.EXCEL形式
以下の手順でEXCEL形式に変換する。
- selectの結果をHTML形式で出力する。
- 出力したhtmlファイルをブラウザで表示する。
- 表示されているテーブルを選択し、コピーする。
- コピーした内容をExcelに貼り付ける。
4.CSV形式
以下にいくつか手順を記載しているが、気に入った方法が見つかっていない。
スッキリしたやり方がこれほど見つからないとは思わなかった。
4-1.基本的なやり方(1)
よく見るやり方１。項目が固定長で出力されるのが問題。
コマンド
cat << EOF | sqlplus -S sys/<password>@<接続Netサービス名> as sysdba > output.csv
set heading on
set underline off
set termout off
set feedback off
set colsep ,
set lines 32767
set trimspool on
set trimout on
set tab off
select * from v\$logfile;
EOF
結果
output.csv
GROUP#,STATUS ,TYPE ,MEMBER ,IS_RECOVE
1, ,ONLINE ,/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_1_bn1rckwt_.log ,NO
1, ,ONLINE ,/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_1_bn1rcl1c_.log ,YES
2, ,ONLINE ,/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_2_bn1rcl2d_.log ,NO
2, ,ONLINE ,/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_2_bn1rcl3w_.log ,YES
3, ,ONLINE ,/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_3_bn1rcl52_.log ,NO
3, ,ONLINE ,/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_3_bn1rcl6l_.log ,YES
4-2.基本的なやり方(2)
よく見るやり方２。テーブルの構成に合わせて、SQL文を作成する必要がある。
コマンド
cat << EOF | sqlplus -S sys/<password>@<接続Netサービス名> as sysdba > output.csv
set termout off
set feedback off
set pages 0
set lines 32767
set trimspool on
set trimout on
set tab off
prompt group#,status,type,member,is_recovery_dest_file
select group# || ',' || status || ',' || type || ',' || member || ',' || is_recovery_dest_file from v\$logfile;
EOF
結果
output.csv
group#,status,type,member,is_recovery_dest_file
1,,ONLINE,/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_1_bn1rckwt_.log,NO
1,,ONLINE,/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_1_bn1rcl1c_.log,YES
2,,ONLINE,/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_2_bn1rcl2d_.log,NO
2,,ONLINE,/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_2_bn1rcl3w_.log,YES
3,,ONLINE,/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_3_bn1rcl52_.log,NO
3,,ONLINE,/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_3_bn1rcl6l_.log,YES
4-3.EXCELの機能を使用
以下の手順でCSV形式に変換する。
- selectの結果をHTML形式で出力する。
- 出力したhtmlファイルをブラウザで表示する。
- 表示されているテーブルを選択し、コピーする。
- コピーした内容をExcelに貼り付ける。
- EXCELで保存するときに、CSV形式を指定。
4-4.SQL Developerの機能を使用
以下の手順でCSV形式に変換する。
- SQL Developerでselect文を実行し、結果を表示する。
- 結果をcsvでexportする機能があるので、それを使用する。
4-5.XML,XSLTを使用
以下の手順でCSV形式に変換する。
- xml→csvに変換するxsltを用意する。
- selectの結果をXML形式で出力する。
- 出力したxml,xsltを元に、csv形式に変換する。
4-6.perlを使用
ORACLE_HOME配下にあるperl($ORACLE_HOME/perl/bin/perl)を使って、CSVを出力する。
※↓エラー処理等の考慮はなし。
perlファイル作成
csv.pl
use DBI;
my $sql='select * from v$logfile';
# 接続
my $dbh=DBI->connect("dbi:Oracle:host=<ホスト名>;sid=<SID>", '<ユーザ名>', '<password>') or die "CONNECT ERROR $DBI::errstr";
# SQL文の発行
my $sth=$dbh->prepare($sql);
$sth->execute;
print join(",", @{$sth->{NAME}}), "\n";
while(my $aref = $sth->fetchrow_arrayref) { print join(",", @$aref), "\n"; }
$sth->finish;
# 切断
$dbh->disconnect;
実行
export PATH="$ORACLE_HOME/perl/bin/:$PATH"
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:$ORACLE_HOME/lib"
export PERL5LIB=$ORACLE_HOME/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi
perl csv.pl
結果
group#,status,type,member,is_recovery_dest_file
1,,ONLINE,/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_1_bn1rckwt_.log,NO
1,,ONLINE,/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_1_bn1rcl1c_.log,YES
2,,ONLINE,/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_2_bn1rcl2d_.log,NO
2,,ONLINE,/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_2_bn1rcl3w_.log,YES
3,,ONLINE,/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_3_bn1rcl52_.log,NO
3,,ONLINE,/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_3_bn1rcl6l_.log,YES
5.Markdown形式
PL/SQLを使用した、かなり無理やりな方法。
SQLファイル作成
md.sql
set serverout on size 1000000
set echo off
set lines 32767
set pages 50000
set tab off
set feedback off
set verify off
set termout on
declare
csql clob := '&1';
vCursor integer default dbms_sql.open_cursor;
vValue varchar2(4000);
vStatus integer;
vDescTbl dbms_sql.desc_tab;
vCnt integer;
begin
dbms_sql.parse(vCursor, csql, dbms_sql.native);
dbms_sql.describe_columns(vCursor, vCnt, vDescTbl );
for i in 1 .. vCnt loop
dbms_sql.define_column(vCursor, i, vValue, 4000 );
end loop;
vStatus := dbms_sql.execute(vCursor);
dbms_output.put('|');
for i in 1 .. vCnt loop
dbms_sql.column_value(vCursor, i, vValue );
dbms_output.put(vDescTbl(i).col_name || '|' );
end loop;
dbms_output.new_line;
dbms_output.put('|');
for i in 1 .. vCnt loop
dbms_output.put('----|');
end loop;
dbms_output.new_line;
while ( dbms_sql.fetch_rows(vCursor) > 0 ) loop
dbms_output.put('|');
for i in 1 .. vCnt loop
dbms_sql.column_value(vCursor, i, vValue );
if (vValue is null) then
dbms_output.put ('*null*');
elsif (vValue='') then
dbms_output.put ('*no data*');
else
dbms_output.put (vValue);
end if;
dbms_output.put ('|');
end loop;
dbms_output.new_line;
end loop;
end;
/
コマンド
sqlplusにて以下を実行。
-- SQL文の終わりにセミコロンを含めないこと。
-- http://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_sql.htm#i997676
@md 'select * from v$logfile'
結果
|GROUP#|STATUS|TYPE|MEMBER|IS_RECOVERY_DEST_FILE|
|----|----|----|----|----|
|1|*null*|ONLINE|/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_1_bn1rckwt_.log|NO|
|1|*null*|ONLINE|/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_1_bn1rcl1c_.log|YES|
|2|*null*|ONLINE|/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_2_bn1rcl2d_.log|NO|
|2|*null*|ONLINE|/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_2_bn1rcl3w_.log|YES|
|3|*null*|ONLINE|/u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_3_bn1rcl52_.log|NO|
|3|*null*|ONLINE|/u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_3_bn1rcl6l_.log|YES|
以下のように表示される。
| GROUP# | STATUS | TYPE | MEMBER | IS_RECOVERY_DEST_FILE |
|---|---|---|---|---|
| 1 | null | ONLINE | /u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_1_bn1rckwt_.log | NO |
| 1 | null | ONLINE | /u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_1_bn1rcl1c_.log | YES |
| 2 | null | ONLINE | /u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_2_bn1rcl2d_.log | NO |
| 2 | null | ONLINE | /u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_2_bn1rcl3w_.log | YES |
| 3 | null | ONLINE | /u01/app/oracle/oradata/TEST01/onlinelog/o1_mf_3_bn1rcl52_.log | NO |
| 3 | null | ONLINE | /u01/app/oracle/fast_recovery_area/TEST01/onlinelog/o1_mf_3_bn1rcl6l_.log | YES |