CSVファイル出力バッチ

-- Index --

・Top

・Softwares

▼親父の独り言集

▼Oracleデータベースに対する独り言集

・ご利用の前に
→CSVデータ出力バッチ作成
・SQLLoader実行バッチ作成(制御ファイルもね)
・主キー作成・削除コマンド取得
・実行中セッションKILLコマンド取得
・PL/SQLソース反映コマンド取得
・テーブル生成コマンド取得
・ビュー生成コマンド取得
・マテリアライズド・ビュー生成コマンド取得
・マテリアライズド・ビューログ生成コマンド取得
・順序（シーケンス）生成コマンド取得
・データベース・リンク生成コマンド取得
・シノニム生成コマンド取得
・サブクエリーのアクセス順指定
・グループ単位の通し番号とグループ内の連番

・VB.NETやC#に対する独り言集

・我が家の家電事情について

いきさつ

SQLDeveloperでも出力できるんですけど、なぜが無性にバッチで出力してみたくなりました。

準備するもの

まず、以下のスクリプトをコピーして、"csvexport.sql"というファイルを作成します。 このとき、以下の部分をメンテしてください。

メンテ箇所
user/password@servicename ユーザー名/パスワード@Oracle接続子(ネット・サービス名)
in ('TABLE1','TABLE2'..) 出力対象テーブル
REPLACE(..., CHR(13), ''^`r'') CSVの項目値としての改行コードの変換コードを指定します(\r\n等)。 CSVレコードの終端コードではありません。改行コードをそのまま出力したい場合、省略してください。ただし、その場合は SORTコマンドをCOPYコマンドに置き換えて下さい。改行された値が別レコードとして認識されてソートされてしまいます。
REPLACE(..., CHR(10), ''^`n'') 

------------------------------------------------------------------
-- Copyright(c) 2015 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2015.09.04 作成
-- 2015.09.11 処理手順を簡潔化
-- 2015.09.14 ソート、ダブルクォーテーション、ヘッダー対応
-- 2015.09.16 フォルダ構成をCSV_DATA,CSV_TEMP,CSV_READCMDに
-- 2015.09.24 CRLFを^`r^`nに置換して出力
-- 
-- [実行方法]
-- 1)このテキストを、"csvexport.sql"として作成します。
-- 2)作成したフォルダ上で、コマンドプロンプトを起動し、以下のコマンドを実行します。
--   @echo off
--   sqlplus -s user/password@servicename @csvexport.sql
--   csvexport.bat
--
-- [注意事項]
-- LOB系、LONG型には対応していません。
-- 並列度は4で設定しています。(DOS画面が4つ最小化状態で立ち上がります。)
-- Windows端末、SJIS出力を想定しています。
-- 改行コードは^`r^`nに置き換えています。置換を止めたい(改行コードをそのまま出力したい)場合はソートコマンドを廃止し、コピーコマンドに置き換えて下さい。
------------------------------------------------------------------
set escape    ?
set linesize  300
set pagesize  0
set term      off
set head      off
set feed      off
set trim      on
set trimspool on
set verify    off
------------------------------------------------------------------
-- __csvexport1.sql
------------------------------------------------------------------
spool  __csvexport1.sql
prompt spool  CSV_READCMD/?&?&1..sql
prompt prompt SET PAGESIZE     0
prompt prompt SET ARRAY        100
prompt prompt SET TERM         OFF
prompt prompt SET HEAD         OFF
prompt prompt SET FEED         OFF
prompt prompt SET TRIM         ON
prompt prompt SET TRIMSPOOL    ON
prompt prompt SET VERIFY       OFF
prompt prompt COLUMN C_REC_SZ NEW_VALUE D_REC_SZ NOPRINT
prompt prompt SELECT NVL(MAX(V_LEN.C_REC_SZ_SUB),0) AS C_REC_SZ
prompt prompt  FROM (
prompt prompt SELECT LENGTHB(CONVERT(
prompt   SELECT CASE WHEN COL.COLUMN_ID  = 1 THEN '       ''"'
prompt               WHEN COL.COLUMN_ID <> 1 THEN '   || '',"'
prompt          END
prompt       || REPLACE(REPLACE(NVL(CMN.COMMENTS,COL.COLUMN_NAME), CHR(13), '^`r'), CHR(10), '^`n') || '"'''
prompt     FROM USER_TAB_COLUMNS  COL
prompt        , USER_COL_COMMENTS CMN
prompt    WHERE COL.TABLE_NAME  = '?&?&1'
prompt      AND COL.TABLE_NAME  = CMN.TABLE_NAME(+)
prompt      AND COL.COLUMN_NAME = CMN.COLUMN_NAME(+)
prompt ORDER BY COL.COLUMN_ID ASC
prompt /
prompt prompt      , 'JA16SJISTILDE')) AS C_REC_SZ_SUB
prompt prompt   FROM DUAL
prompt prompt  WHERE !?&1 = 1
prompt prompt  UNION ALL
prompt prompt SELECT LENGTHB(CONVERT(
prompt   SELECT RPAD(
prompt          CASE WHEN COL.COLUMN_ID  = 1 AND COL.DATA_TYPE  = 'VARCHAR2' THEN '       ''"'' || REPLACE(REPLACE(REPLACE('
prompt               WHEN COL.COLUMN_ID  = 1 AND COL.DATA_TYPE  = 'CHAR'     THEN '       ''"'' || REPLACE(REPLACE(REPLACE('
prompt               WHEN COL.COLUMN_ID  = 1 AND COL.DATA_TYPE <> 'NUMBER'   THEN '       ''"'' || '
prompt               WHEN COL.COLUMN_ID  = 1 AND COL.DATA_TYPE  = 'NUMBER'   THEN '       '
prompt               WHEN COL.COLUMN_ID <> 1 AND COL.DATA_TYPE  = 'VARCHAR2' THEN '   || '',"'' || REPLACE(REPLACE(REPLACE('
prompt               WHEN COL.COLUMN_ID <> 1 AND COL.DATA_TYPE  = 'CHAR'     THEN '   || '',"'' || REPLACE(REPLACE(REPLACE('
prompt               WHEN COL.COLUMN_ID <> 1 AND COL.DATA_TYPE <> 'NUMBER'   THEN '   || '',"'' || '
prompt               WHEN COL.COLUMN_ID <> 1 AND COL.DATA_TYPE  = 'NUMBER'   THEN '   || '',''  || '
prompt          END
prompt       || CASE WHEN COL.DATA_TYPE    = 'DATE'         THEN 'TO_CHAR('
prompt               WHEN COL.DATA_TYPE LIKE 'TIMESTAMP(%)' THEN 'TO_CHAR('
prompt               ELSE NULL END
prompt       || RPAD(COL.COLUMN_NAME,30)
prompt       || CASE WHEN COL.DATA_TYPE    = 'DATE'         THEN ', ''YYYY/MM/DD HH24:MI:SS'')'
prompt               WHEN COL.DATA_TYPE LIKE 'TIMESTAMP(%)' THEN ', ''YYYY/MM/DD HH24:MI:SS.FF' || COL.DATA_SCALE || ''')'
prompt               ELSE NULL END
prompt       || CASE WHEN COL.DATA_TYPE  = 'VARCHAR2' THEN ',''"'',''""''), CHR(13), ''^`r''), CHR(10), ''^`n'') || ''"'''
prompt               WHEN COL.DATA_TYPE  = 'CHAR'     THEN ',''"'',''""''), CHR(13), ''^`r''), CHR(10), ''^`n'') || ''"'''
prompt               WHEN COL.DATA_TYPE <> 'NUMBER'   THEN ' || ''"'''
prompt               ELSE NULL END,120)
prompt       || NVL2(CMN.COMMENTS,'-- ' || CMN.COMMENTS,' ')
prompt     FROM USER_TAB_COLUMNS  COL
prompt        , USER_COL_COMMENTS CMN
prompt    WHERE COL.TABLE_NAME  = '?&?&1'
prompt      AND COL.TABLE_NAME  = CMN.TABLE_NAME(+)
prompt      AND COL.COLUMN_NAME = CMN.COLUMN_NAME(+)
prompt ORDER BY COL.COLUMN_ID ASC
prompt /
prompt prompt      , 'JA16SJISTILDE')) AS C_REC_SZ_SUB
prompt prompt   FROM ?&?&1
prompt prompt  WHERE ORA_HASH(ROWIDTOCHAR(ROWID),!?&2,1)=!?&!?&1 - 1
prompt prompt      ) V_LEN
prompt prompt /
prompt prompt SET LINESIZE !?&D_REC_SZ
prompt prompt SPOOL CSV_TEMP/?&?&1..csv!?&!?&1.h
prompt   SELECT CASE WHEN COL.COLUMN_ID  = 1 THEN 'SELECT ''"'
prompt               WHEN COL.COLUMN_ID <> 1 THEN '   || '',"'
prompt          END
prompt       || REPLACE(REPLACE(NVL(CMN.COMMENTS,COL.COLUMN_NAME), CHR(13), '^`r'), CHR(10), '^`n') || '"'''
prompt     FROM USER_TAB_COLUMNS  COL
prompt        , USER_COL_COMMENTS CMN
prompt    WHERE COL.TABLE_NAME  = '?&?&1'
prompt      AND COL.TABLE_NAME  = CMN.TABLE_NAME(+)
prompt      AND COL.COLUMN_NAME = CMN.COLUMN_NAME(+)
prompt ORDER BY COL.COLUMN_ID ASC
prompt /
prompt prompt   FROM DUAL
prompt prompt  WHERE !?&!?&1 = 1
prompt prompt /
prompt prompt SPOOL CSV_TEMP/?&?&1..csv!?&!?&1.d
prompt   SELECT RPAD(
prompt          CASE WHEN COL.COLUMN_ID  = 1 AND COL.DATA_TYPE  = 'VARCHAR2' THEN 'SELECT ''"'' || REPLACE(REPLACE(REPLACE('
prompt               WHEN COL.COLUMN_ID  = 1 AND COL.DATA_TYPE  = 'CHAR'     THEN 'SELECT ''"'' || REPLACE(REPLACE(REPLACE('
prompt               WHEN COL.COLUMN_ID  = 1 AND COL.DATA_TYPE <> 'NUMBER'   THEN 'SELECT ''"'' || '
prompt               WHEN COL.COLUMN_ID  = 1 AND COL.DATA_TYPE  = 'NUMBER'   THEN 'SELECT '
prompt               WHEN COL.COLUMN_ID <> 1 AND COL.DATA_TYPE  = 'VARCHAR2' THEN '   || '',"'' || REPLACE(REPLACE(REPLACE('
prompt               WHEN COL.COLUMN_ID <> 1 AND COL.DATA_TYPE  = 'CHAR'     THEN '   || '',"'' || REPLACE(REPLACE(REPLACE('
prompt               WHEN COL.COLUMN_ID <> 1 AND COL.DATA_TYPE <> 'NUMBER'   THEN '   || '',"'' || '
prompt               WHEN COL.COLUMN_ID <> 1 AND COL.DATA_TYPE  = 'NUMBER'   THEN '   || '',''  || '
prompt          END
prompt       || CASE WHEN COL.DATA_TYPE    = 'DATE'         THEN 'TO_CHAR('
prompt               WHEN COL.DATA_TYPE LIKE 'TIMESTAMP(%)' THEN 'TO_CHAR('
prompt               ELSE NULL END
prompt       || RPAD(COL.COLUMN_NAME,30)
prompt       || CASE WHEN COL.DATA_TYPE    = 'DATE'         THEN ', ''YYYY/MM/DD HH24:MI:SS'')'
prompt               WHEN COL.DATA_TYPE LIKE 'TIMESTAMP(%)' THEN ', ''YYYY/MM/DD HH24:MI:SS.FF' || COL.DATA_SCALE || ''')'
prompt               ELSE NULL END
prompt       || CASE WHEN COL.DATA_TYPE  = 'VARCHAR2' THEN ',''"'',''""''), CHR(13), ''^`r''), CHR(10), ''^`n'') || ''"'''
prompt               WHEN COL.DATA_TYPE  = 'CHAR'     THEN ',''"'',''""''), CHR(13), ''^`r''), CHR(10), ''^`n'') || ''"'''
prompt               WHEN COL.DATA_TYPE <> 'NUMBER'   THEN ' || ''"'''
prompt               ELSE NULL END,120)
prompt       || NVL2(CMN.COMMENTS,'-- ' || CMN.COMMENTS,' ')
prompt     FROM USER_TAB_COLUMNS  COL
prompt        , USER_COL_COMMENTS CMN
prompt    WHERE COL.TABLE_NAME  = '?&?&1'
prompt      AND COL.TABLE_NAME  = CMN.TABLE_NAME(+)
prompt      AND COL.COLUMN_NAME = CMN.COLUMN_NAME(+)
prompt ORDER BY COL.COLUMN_ID ASC
prompt /
prompt prompt AS CSV_DATA FROM ?&?&1
prompt prompt  WHERE ORA_HASH(ROWIDTOCHAR(ROWID),!?&!?&2,1)=!?&!?&1 - 1
prompt prompt /
prompt prompt SPOOL OFF
prompt prompt EXIT
prompt spool  off
spool  off
------------------------------------------------------------------
-- バッチファイル初期化
------------------------------------------------------------------
spool  __csvexport1.add
prompt @ECHO OFF
prompt IF "%1" NEQ "" GOTO THREAD_PROC
prompt DEL /Q  CSV_TEMP\*.* >> __csvexport1.log 2>> __csvexport2.log
prompt START /MIN csvexport.bat 1
prompt START /MIN csvexport.bat 2
prompt START /MIN csvexport.bat 3
prompt START /MIN csvexport.bat 4
spool  off
spool  __csvexport2.add
spool  off
------------------------------------------------------------------
-- __csvexport2.sql
------------------------------------------------------------------
spool __csvexport2.sql
select '@__csvexport1.sql ' || tbl.table_name || '
spool  __csvexport1.add APPEND
prompt :WAIT_' || tbl.table_name || '
prompt if exist CSV_TEMP\' || tbl.table_name || '.end1 ' ||
      'if exist CSV_TEMP\' || tbl.table_name || '.end2 ' ||
      'if exist CSV_TEMP\' || tbl.table_name || '.end3 ' ||
      'if exist CSV_TEMP\' || tbl.table_name || '.end4 GOTO END_' || tbl.table_name || '
prompt PING localhost -n 1 > nul
prompt GOTO WAIT_' || tbl.table_name || '
prompt :END_' || tbl.table_name || '
prompt COPY /Y /B CSV_TEMP\' || tbl.table_name || '.csv1h  CSV_DATA\' || tbl.table_name || '.csv > nul
prompt COPY /Y /B CSV_TEMP\' || tbl.table_name || '.csv*d  CSV_TEMP\' || tbl.table_name || '.csv > nul
prompt SORT       CSV_TEMP\' || tbl.table_name || '.csv >> CSV_DATA\' || tbl.table_name || '.csv
prompt SET  /P  X=CSV出力対象テーブル : ' || rpad(tbl.table_name,30) || '  出力行数 : <nul
prompt TYPE       CSV_TEMP\' || tbl.table_name || '.csv | FIND /C /V ""
prompt DEL        CSV_TEMP\' || tbl.table_name || '.*   >> __csvexport1.log 2>> __csvexport2.log
spool  OFF
spool  __csvexport2.add APPEND
prompt SQLPLUS -s user/password@servicename @CSV_READCMD/' || tbl.table_name || '.sql %1 3
prompt ECHO 1 > CSV_TEMP/' || tbl.table_name || '.end%1
prompt ECHO ' || tbl.table_name || '.csv
spool  OFF
' as cmdline
from user_tables tbl
where tbl.table_name in ('TABLE1','TABLE2'..)
order by table_name asc
/
spool off
------------------------------------------------------------------
-- バッチファイル終了処理組み込み＋連結＋一時ファイルクリア
------------------------------------------------------------------
$MKDIR   CSV_DATA     >> __csvexport1.log 2>> __csvexport2.log
$MKDIR   CSV_READCMD  >> __csvexport1.log 2>> __csvexport2.log
$MKDIR   CSV_TEMP     >> __csvexport1.log 2>> __csvexport2.log
set escape off
set escape !
@__csvexport2.sql
spool  __csvexport1.add APPEND
prompt DEL __csvexport*.*
prompt ECHO CSVファイルを出力しました。"EXIT"コマンドで終了して下さい。
prompt GOTO LAST_PROC
prompt :THREAD_PROC
spool  off
spool  __csvexport2.add APPEND
prompt EXIT
prompt EXIT /B
prompt :LAST_PROC
spool  off
$copy /B __csvexport*.add csvexport.bat >> __csvexport1.log 2>> __csvexport2.log
$del __csvexport*.*
$echo スクリプトを生成しました。
exit

実行

スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。

@echo off

sqlplus -s user/password@servicename @csvexport.sql

csvexport.bat

実行時に生成されるサブフォルダ
CSV_READCMD CSV形式でデータ出力するスクリプトを生成
CSV_TEMP CSV出力用の作業領域
CSV_DATA CSV形式の実データ

実行結果

[csvexport.bat]

[EXPコマンド]

[EXPDPコマンド]



[1GB程度のデータの出力にかかった時間]

あくまでも私個人での環境での実測結果です。

Copyright(c) 2014-2022 pakkin. All Rights Reserved.