Bash
oracle

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