DBMS_OUTPUTの出力をJavaから取得する。

PLSQLつかってますか?
あまり使っていませんが私は結構好きな言語です。
でもこの言語ってテストしにくいんですよね。
最近SQLDeveloperがだいぶ良くなってデバッグとかも快適になりましたけどまだまだ自動テストとかは未整備です。
そのため私はJunitからPLSQLを呼び出してテストしているんですが、業務仕様がいけてなくてDBMS_OUTPUTを使われていたりすると自動テストするのが一気にめんどくさくなります。
そこでDBMS_OUTPUTの出力内容をJavaの自動テストから取得してアサートできるようにしてみました。


DBMS_OUTPUT.PUT_LINEの出力は通常だとSQLPLUSのコンソールに出力されますが、DBMS_OUTPUT.ENABLEを呼んであげると内部のバッファに出力されます。そしてDBMS_OUTPUT.GET_LINEでそこにPLSQLでアクセス可能となります。
これを使ってテスト対象のPLを呼ぶ前に有効にし、処理後にバッファからデータを吸い出して比較してあげればいいわけです。


ネットをみていてもこのDBMS_OUTPUT.PUT_LINEの出力を取得するサンプルが余り無かったので載せておきます。

        CallableStatement cstmt = null;
        cstmt = con.prepareCall("BEGIN DBMS_OUTPUT.ENABLE(1000000); END;");
        cstmt.execute();
        
        // テスト対象の呼び出し
        cstmt = con.prepareCall("{ ? = call GARAPON_TEST.TARGETA() }");
        cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
        cstmt.execute();

        //出力の取得
        System.out.println("DBMS_OUTPUT内容の取得");
        cstmt = con.prepareCall("BEGIN DBMS_OUTPUT.GET_LINE(?,?); END;");
        boolean hasMore = true;
        cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
        cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
        while (hasMore) {
            cstmt.execute();
            hasMore = (cstmt.getInt(2) == 0);
            if (hasMore) {
                System.out.println(cstmt.getString(1));
            }
        }
        cstmt.close();
        System.out.println("DBMS_OUTPUT内容の取得終了");

呼び出されるSQLはこんな感じ

CREATE OR REPLACE PACKAGE BODY GARAPON_TEST IS
FUNCTION TARGETA RETURN NUMBER IS
  ret NUMBER := 0;

  BEGIN 
  SELECT count(*) INTO ret FROM TEST ;
  DBMS_OUTPUT.put_line('I AM GARAPON');
  RETURN ret;
END TARGETA;
END GARAPON_TEST;

じっこうすると

DBMS_OUTPUT内容の取得
I AM GARAPON
DBMS_OUTPUT内容の取得終了

うまくいきましたね。
これでレガシーなPLSQLもがんがん自動テストできます。