Oracle PL/SQLブロックでデータをCSV出力する

2018年7月14日2018年7月16日Oracle,開発

おはようございます。

今日も昨日に引き続き Oracle のツールです。

今回は指定したテーブルの内容をCSVに出力するバッチを作りました。

スポンサーリンク

CSV出力する

昨日と同じように、PL/SQLブロックを活用して
取得したデータをCSV形式に編集してスプールログに出力するといったものになります。

実行するSQL

OUTPUT_CSV.SQL

SPOOL &1
SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 1000
SET ECHO ON
SET PAGESIZE 9999
SET colsep ','


DECLARE
    STRCOLUMN      VARCHAR(32767);
    STRCOLUMNNAME  VARCHAR(32767);
    TYPE CUTYPE IS REF CURSOR;
    CV CUTYPE;
    ITEM VARCHAR(1000);

BEGIN

  STRCOLUMN := '';

  FOR CUR_REC IN (
    SELECT
        C.COLUMN_NAME
    FROM
        USER_TAB_COLUMNS C ,
        USER_TABLES T
    WHERE
            C.TABLE_NAME = T.TABLE_NAME
        AND T.TABLE_NAME = '&2')
  LOOP
    BEGIN
      IF STRCOLUMN IS NULL THEN
        STRCOLUMN := STRCOLUMN || CUR_REC.COLUMN_NAME;
        STRCOLUMNNAME := STRCOLUMNNAME || CUR_REC.COLUMN_NAME;
      ELSE
        STRCOLUMN := STRCOLUMN || ' || '','' || ' || CUR_REC.COLUMN_NAME;
        STRCOLUMNNAME := STRCOLUMNNAME || ',' || CUR_REC.COLUMN_NAME;
      END IF;

    END;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('---------------------------------------- 結果 ----------------------------------------');
  DBMS_OUTPUT.PUT_LINE(' ');
  DBMS_OUTPUT.PUT_LINE(STRCOLUMNNAME);

  BEGIN

    OPEN CV FOR 'SELECT ' || STRCOLUMN || ' AS STR FROM ' || '&2';

      LOOP FETCH CV INTO ITEM;
        EXIT WHEN CV%NOTFOUND;

        BEGIN
          DBMS_OUTPUT.PUT_LINE(ITEM);
        END;

      END LOOP;

    CLOSE CV;

  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);

  END;

END;
/

SPOOL OFF
EXIT 0

SQL呼び出しバッチ

OUTPUT.bat

@echo off

REM 実行するSQLファイル
SET SQLFILE=OUTPUT_CSV.SQL

REM ログファイル（スプール）
SET LOGFILE=OUTPUT_CSV.LOG

REM 対象ユーザー
SET TARGET=USER01

REM パスワード
SET PASS=USER01

REM TNSサービス名
SET SERVICE_NAME=ORCL

REM テーブル名
SET TABLE_NAME=TBLCAT

REM 実行
sqlplus %TARGET%/%PASS%@%SERVICE_NAME% @%SQLFILE% "%LOGFILE%" "%TABLE_NAME%"

pause

起動してみる

今回は、データの出力のみなので、
以前作成したサンプルプログラム用のテーブルからデータをCSV出力してみました。

無事に出力されました。

ただ、スプールの仕様で、各行の後ろに空白データが大分ついてしまっているので、
エディタなんかで最終的に編集する必要があります。

追記

@ken_tokuda さんに Twitter で、
下記の設定で後ろの空白を消せるというのを教えていただきました。

set trimspool on

まとめ

場合によっては便利なクライアント用ソフトが使えない場合もあるので、
こういったツールが活躍する場面もあるかと思います。

また、客先のシステムなんかで、リモートで調査が出来ない場合なんかに
データを取得してもらうのにバッチの形式は便利ですよね。

昨日、今日とサクッとした記事になりましたが、
何かのお役に立てれば。

ではでは。