Edited at

Oracle SELECT結果の出力形式を変換する。

More than 3 years have passed since last update.


概要

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

HTML形式.png


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形式に変換する。


  1. selectの結果をHTML形式で出力する。

  2. 出力したhtmlファイルをブラウザで表示する。

  3. 表示されているテーブルを選択し、コピーする。

  4. コピーした内容をExcelに貼り付ける。


4.CSV形式

以下にいくつか手順を記載しているが、気に入った方法が見つかっていない。

スッキリしたやり方がこれほど見つからないとは思わなかった。


4-1.基本的なやり方(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)

よく見るやり方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形式に変換する。


  1. selectの結果をHTML形式で出力する。

  2. 出力したhtmlファイルをブラウザで表示する。

  3. 表示されているテーブルを選択し、コピーする。

  4. コピーした内容をExcelに貼り付ける。

  5. EXCELで保存するときに、CSV形式を指定。


4-4.SQL Developerの機能を使用

以下の手順でCSV形式に変換する。


  1. SQL Developerでselect文を実行し、結果を表示する。

  2. 結果をcsvでexportする機能があるので、それを使用する。


4-5.XML,XSLTを使用

以下の手順でCSV形式に変換する。


  1. xml→csvに変換するxsltを用意する。

  2. selectの結果をXML形式で出力する。

  3. 出力した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