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


## 概要
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形式
**コマンド**

```bash:
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](https://qiita-image-store.s3.amazonaws.com/0/63331/f58bfd81-8c06-b2b0-b412-148fdc0ba3b6.png "HTML形式.png")















### 2.XML形式
※「Oracle XML Database」コンポーネントのインストールが必須。

**コマンド**

```bash:
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
```

**結果**

```xml: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形式で出力する。
1. 出力したhtmlファイルをブラウザで表示する。
1. 表示されているテーブルを選択し、コピーする。
1. コピーした内容をExcelに貼り付ける。

### 4.CSV形式
以下にいくつか手順を記載しているが、気に入った方法が見つかっていない。
スッキリしたやり方がこれほど見つからないとは思わなかった。

#### 4-1.基本的なやり方(1)
よく見るやり方１。項目が固定長で出力されるのが問題。

**コマンド**

``` bash:
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
```

**結果**

```sql: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文を作成する必要がある。
**コマンド**

```bash:
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
```

**結果**

```sql: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形式で出力する。
1. 出力したhtmlファイルをブラウザで表示する。
1. 表示されているテーブルを選択し、コピーする。
1. コピーした内容をExcelに貼り付ける。
1. EXCELで保存するときに、CSV形式を指定。


#### 4-4.SQL Developerの機能を使用
以下の手順でCSV形式に変換する。

1. SQL Developerでselect文を実行し、結果を表示する。
2. 結果をcsvでexportする機能があるので、それを使用する。

#### 4-5.XML,XSLTを使用
以下の手順でCSV形式に変換する。

1. xml→csvに変換するxsltを用意する。
1. selectの結果をXML形式で出力する。
1. 出力したxml,xsltを元に、csv形式に変換する。

#### 4-6.perlを使用
ORACLE_HOME配下にあるperl($ORACLE_HOME/perl/bin/perl)を使って、CSVを出力する。
※↓エラー処理等の考慮はなし。

**perlファイル作成**

```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;

```

**実行**

```bash
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
```



**結果**

```sql:
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ファイル作成**

```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:
-- 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|