節約プログラマー雑記

• estoy
• 2021年3月8日
• • Oracle
  • Tips

前書き

以前、WinMergeを使い、データの新旧比較をしていましたが、その際比較するためのデータを出力する必要があったので、OracleからデータをCSV出力するスクリプトを、Oracleのテーブル定義（USER_TAB_COLUMNS）から動的に作るようにしていました。
Oracle12c以降は、SET MARKUP CSVでCSV出力ができるようなのですが、11g以前にはそのような機能が無く、テストの際に割と便利に使っていたので、自分の備忘を兼ねてソースを書いておこうと思います。

ソース

ソースは以下の通り。大したことはしておらず、USER_TABLEから、テーブルの名称順にテーブルの一覧を取得し、USER_TAB_COLUMNSから列名を取得して、全てデータを文字列に変換して、「,」カンマ区切りでデータを出力するSQLを動的に作成するようになっています。

ファイル名：create_csv.sql

SET SERVEROUTPUT ON;
SET TRIMSPOOL ON;
SET LINESIZE 32767
SET FEEDBACK OFF;
SPOOL result.sql;
DECLARE
   vColumns  VARCHAR2(32767) := '';
   vTempCol  VARCHAR2(256)   := '';
   vSorts    VARCHAR2(32767) := '';
   --ファイル情報
   CURSOR vCur IS
         SELECT
              T1.TABLE_NAME AS TABLE_ID
         FROM
               USER_TABLES T1
         ORDER BY
              T1.TABLE_NAME;
   vRec vCur%ROWTYPE;
--
   --テーブル情報
   CURSOR vCur2(pI_Table VARCHAR2) IS
         SELECT
              *
         FROM
              USER_TAB_COLUMNS T1
         WHERE
              T1.TABLE_NAME = pI_Table
         ORDER BY
              T1.COLUMN_ID;
   vRec2 vCur2%ROWTYPE;
--
BEGIN
   -- 共通パラメータ出力
   DBMS_OUTPUT.PUT_LINE('SET WRAP OFF');
   DBMS_OUTPUT.PUT_LINE('SET COLSEP ''	''');
   DBMS_OUTPUT.PUT_LINE('SET TRIMSPOOL ON');
   DBMS_OUTPUT.PUT_LINE('SET TRIMOUT ON');
   DBMS_OUTPUT.PUT_LINE('SET LINESIZE 32767');
   DBMS_OUTPUT.PUT_LINE('SET PAGESIZE 0');
   DBMS_OUTPUT.PUT_LINE('SET FEEDBACK OFF');
   DBMS_OUTPUT.PUT_LINE('SET SERVEROUTPUT ON');
--
   FOR vRec IN vCur
   LOOP
      FOR vRec2 IN vCur2(vRec.TABLE_ID)
      LOOP--
          -- 動的SQL組み立て
          IF vRec2.DATA_TYPE IN ('CHAR','VARCHAR2') THEN
             vTempCol := vRec2.COLUMN_NAME;
          ELSE
             vTempCol :=  'TO_CHAR('||vRec2.COLUMN_NAME||')';
          END IF;
          
          IF (vRec2.COLUMN_ID = '1') THEN
             vColumns := vTempCol;
             vSorts := vRec2.COLUMN_NAME;
          ELSE
             vColumns := vColumns ||'||'',''||'||vTempCol;
             vSorts := vSorts || ',' || vRec2.COLUMN_NAME;
          END IF;
      END LOOP;
      --各テーブルの出力SQL
      DBMS_OUTPUT.PUT_LINE('spool '||vRec.TABLE_ID|| '.csv');
      DBMS_OUTPUT.PUT_LINE('EXEC DBMS_OUTPUT.PUT_LINE( '''||vSorts ||''');');
      DBMS_OUTPUT.PUT_LINE('SELECT '||vColumns ||' FROM ' || vRec.TABLE_ID ||' ORDER BY '|| vSorts ||';');
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('spool off');
   END LOOP;
END;
/
SPOOL OFF;

実際にソースを実行すると、下記のようなテーブル毎にCSVファイルをspoolで出力するためのsqlが作成されます。
後は、これを実行することで、OracleのデータをCSV形式で出力することができます。（ただ、sqlplusの行幅や列の上限は考慮していないので、あくまでベースと考えて利用の際は適宜修正した方が良いです。）

ファイル名：result.sql

SET WRAP OFF
SET COLSEP '	'
SET TRIMSPOOL ON
SET TRIMOUT ON
SET LINESIZE 32767
SET PAGESIZE 0
SET FEEDBACK OFF
SET SERVEROUTPUT ON
spool TEST_TABLE.csv
EXEC DBMS_OUTPUT.PUT_LINE( 'AAA,BBB,CCC');
SELECT AAA||','||BBB||','||TO_CHAR(CCC) FROM TEST_TABLE ORDER BY AAA,BBB,CCC;
spool off
--・・・以下　テーブル毎にspoolするためのSQLが出力されます。

結果

こんな感じでCSVを出力するためのsqlを作成し、後は使いたいsqlを使い、先日紹介したWinMergeと合わせて利用することで、テストを大分簡略化することができました。（WinMergeについては、下記、関連記事を参考）

一つ一つ自分でSQLを書くよりも、Oracleの定義さえあればどこでも使えて、自動でスクリプトが作ることができ、割と便利に使っているので、ご参考になれば幸いです。