Edited at

OracleDBのテーブルデータを綺麗にCSVに一発で出すスクリプト

More than 1 year has passed since last update.

OracleDBのテーブルデータを綺麗にCSVに一発で出すスクリプト

SQLPLUSの実行結果をCSVに出力したい(※可変長は可変長で) etc

参照すると面倒(結局一行なカンマ区切りを出すにはカラムを記載しカンマ文字結合したSELECTをSPOOLしか無い?様)、、、

Unixオンリーライクでやると都度Sqlplusに渡すsqlを加工するのが面倒なので、oracleのテーブル定義をクエリした結果によってSpoolするスクリプトを作成し全てのテーブル、接続可能なDBインスタンスとスキーマから出力する。

スクリプティングの静的、動的な醍醐味である、ヒアドキュメント、パイプを使いメイン処理部をワンライナーで作成した物


スクリプト(bash)


csvoutforora.sh

#!/usr/bin/bash

if [ $# -lt 3 ] ; then
printf "USAGE: csvout.sh FILENAME TABLENAME CONINF(ex 'usera/passa@192.168.1.1:1521/hogeservice' ) WHERECLOSE(ex \"where rownum<10 order by TIMEPARTS asc\"" >&2
exit 9
fi
FILES=$1
TABLES=$2
CONINF=$3
WHERECLOSE=""
if [ $# -eq 4 ] ; then
WHERECLOSE=$4
fi

#データスプール用のSQL作成
perl -pe "s/TABLENAMES/$TABLES/g" <<EOFS |perl -pe "s/FILES/$FILES/g"|perl -pe "s/WHERECLOSE/$WHERECLOSE/g" | sqlplus $CONINF
spool off
set head off
set trimspool on
set pagesize 0
set termout off
set feedback off
set echo off
spool TABLENAMES_1.sql
with tinf as (
select
a.TABLE_NAME,b.COMMENTS tabcomes
, a.COLUMN_ID,a.COLUMN_NAME,c.COMMENTS colcomes
,a.DATA_TYPE,a.DATA_LENGTH,a.NULLABLE
from USER_TAB_COLUMNS a
,USER_TAB_COMMENTS b
,USER_COL_COMMENTS c
where 1=1
and a.TABLE_NAME=b.TABLE_NAME(+)
and a.TABLE_NAME=c.TABLE_NAME(+) and a.COLUMN_NAME=c.COLUMN_NAME(+)
and a.TABLE_NAME=upper('TABLENAMES')
order by a.TABLE_NAME,a.COLUMN_ID
)
select
(case
when a.COLUMN_ID=1 then
'spool off'||chr(10)||
'set head off '||chr(10)||
'set trimspool on '||chr(10)||
'set linesize 32767 '||chr(10)||
'set pagesize 0 '||chr(10)||
'set termout off '||chr(10)||
'set feedback off '||chr(10)||
'set echo off '||chr(10)||
'spool ''FILES'''||chr(10)||
'select '||a.COLUMN_NAME||'||'',''||'
when b.colcnts=a.COLUMN_ID then
' '||a.COLUMN_NAME||chr(10)||
' from '||a.TABLE_NAME||' WHERECLOSE;'||chr(10)||
'spool off'||chr(10)||
'exit'||chr(10)
else
' '||a.COLUMN_NAME||'||'',''||'
end) sqls
from tinf a,
(select count(*) colcnts from tinf ) b
where 1=1
order by a.COLUMN_ID
;
spool off
exit
EOFS

#余計な行を抜く
perl -ne 'print unless /^(SQL\>.*|[ ]+[0-9]+.*|[ ]*)$/' < ${TABLES}_1.sql > ${TABLES}.sql
rm ${TABLES}_1.sql

#データのスプール実行
sqlplus $CONINF @${TABLES}.sql
rm ${TABLES}.sql
exit 0



出してみたテーブル

スキーマ:usera

テーブル名:ACCLOG42

その全カラム名:TIMEPARTS,IPPARTS,MSGPART

(ある期間のアクセスログを格納した物)


実行例

bash$ ./csvoutforora.sh ACCLOG42.csv ACCLOG42 'usera/passa@192.168.1.1:1521/hogeservice' "where rownum<20 order by timeparts desc"

SQL*Plus: Release aaabvbbccc Production on 火 8月 43 17:24:58 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Oracle Database 11g Enterprise Edition Release aaabvbbccc - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
に接続されました。
SQL> 現在はスプールしていません。
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 ... 39 40 spool off
set head off
set trimspool on
set linesize 32767
set pagesize 0
set termout off
set feedback off
set echo off
spool 'ACCLOG42.csv'
select TIMEPARTS||','||

IPPARTS||','||
MSGPART
from ACCLOG42 where rownum<20 order by timeparts desc;
spool off
exit

SQL> SQL> Oracle Database 11g Enterprise Edition Release aaabvbbccc - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management optionsとの接続が切断されました。
SQL*Plus: Release aaabvbbccc Production on 火 8月 43 17:24:58 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Oracle Database 11g Enterprise Edition Release aaabvbbccc - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
に接続されました。
現在はスプールしていません。
Oracle Database 11g Enterprise Edition Release aaabvbbccc - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management optionsとの接続が切断されました。
bash$


結果

bash$ cat ACCLOG42.csv

42/8/2018:09:03:15 +0900,logs/access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:03:05 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:02:54 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:02:44 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:02:33 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:02:23 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:02:12 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:02:02 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:01:51 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:01:41 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:01:31 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:01:20 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:01:10 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:00:59 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:00:49 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:00:38 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:00:28 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:00:17 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
42/8/2018:09:00:07 +0900,access.log01891:192.168.0.1, "HEAD /engbJapanServlet HTTP/1.0" 200 0
bash$