oracle SQL_FULLTEXTを分割しながらすべてを表示する

v$sqlやv$sqlareaからSQLを抽出する際SQL_TEXTカラムに納まりきれない場合、SQL_FULLTEXTを参照する事になります。それでもVARCHAR２の範囲内にSQLテキストが収まっていればよいですが、それを超える場合は途中で分割しないと全てを表示する事ができません。そんな時お気軽に出力するのが以下SQLです。

案１　CLOBをVARCHAR2サイズ内に分割しながら表示する

SELECT 
SQL_ID
,DBMS_LOB.SUBSTR(t1.SQL_FULLTEXT, 1000, 1) AS C1
,CASE WHEN DBMS_LOB.GETLENGTH(SQL_FULLTEXT) > 1000
 THEN DBMS_LOB.SUBSTR (t1.SQL_FULLTEXT,1000, 1001) END AS C2
,CASE WHEN DBMS_LOB.GETLENGTH(SQL_FULLTEXT) > 2000
 THEN DBMS_LOB.SUBSTR (t1.SQL_FULLTEXT,1000, 2001) END AS C3
,CASE WHEN DBMS_LOB.GETLENGTH(SQL_FULLTEXT) > 3000
 THEN DBMS_LOB.SUBSTR (t1.SQL_FULLTEXT,1000, 3001) END AS C4
,CASE WHEN DBMS_LOB.GETLENGTH(SQL_FULLTEXT) > 4000
 THEN DBMS_LOB.SUBSTR (t1.SQL_FULLTEXT,1000, 4001) END AS C5
,CASE WHEN DBMS_LOB.GETLENGTH(SQL_FULLTEXT) > 5000
 THEN DBMS_LOB.SUBSTR (t1.SQL_FULLTEXT,1000, 5001) END AS C6
,CASE WHEN DBMS_LOB.GETLENGTH(SQL_FULLTEXT) > 6000
 THEN DBMS_LOB.SUBSTR (t1.SQL_FULLTEXT,1000, 6001) END AS C7
from v$sqlarea t1
where SQL_ID = '11fk9qf0bj9yb'
;

案2　SQL*Plusで表示する

案１では出力結果が「ダブルクォーテーション」で囲まれる事によるエスケープが発生してなかなか使い勝手悪いです。その点、慣れないと中々敷居が高いのですがSQL*PlusからSQLを叩く事で出力する事も可能です。以下リンク参照ください。

案３　無名ブロックプロシージャで表示する

以下のプロシージャを使うと入力のままのSQLTEXTを出力する事できます。DBMS_OUTPUT.PUT_LINEの制限範囲内という条件はつきますが、プロシージャはストアドする必要もない点などお気軽です

V$SQLAREAやV$SQLのSQL_FULLTEXTを利用するケース

declare
sqlfulltext clob := null;
begin
SELECT SQL_TEXT into sqlfulltext FROM V$SQLAREA where  SQL_ID='68xckwf767uv3';
DBMS_OUTPUT.PUT_LINE( sqlfulltext);
end;
/

V$SQLTEXT_WITH_NEWLINES のSQL_FULLTEXTを利用するケース

V$SQLやV$SQLAREAのSQL_FULLTEXTはCLOBですが、V$SQLTEXT_WITH_NEWLINESにはVARCHAR2(64)のサイズに分割して保持してあるので、こちらを参照する事もできます。V$SQLTEXTと異なり改行やタブもそのまま保存されています。よってconcat(sqlfulltext,CUR.SQL_TEXT)の結果はV$SQLAREA等のSQL_FULLTEXTと同じものになります。（今回の目的は１つのSQLテキストを出力するだけなので上記プロシージャで十分だと思いますが、以下も参考までに記載しています）

declare
sqlfulltext clob := null;
sqlid  varchar2(13) :='0pyq96jtkdr31';
ldat varchar2(128);
begin
FOR CUR IN (SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE SQL_ID=sqlid order by PIECE) LOOP
   sqlfulltext := concat(sqlfulltext,CUR.SQL_TEXT);
end loop;
DBMS_OUTPUT.PUT_LINE( sqlfulltext);
end;
/

ただ、やはりDBMS_OUTPUT.PUT_LINE()による出力は決して「取り回しが良い」とは言えません。ほとんどのみなさんは、何かしらGUIのSQL発行ツールを利用していると思いますが、何れのSQL発行ツールもDBMS_OUTPUT.PUT_LINE()で出力した文字列の扱いが不便だと思います。SQL_IDで指定するSQL_TEXTがSELECTの結果セットとして出力されたら便利だと思いませんか。その昔、きっとOracleの機能としてファンクションやプロシージャで提供されているだろうと一生懸命探した事あるのですが、見つかりませんでした。そんな時作ったのが以下です。

案４　テーブルファンクションを使いツール化

上記プロシージャの発展版としてテーブルファンクションで実装すれば便利なツールとなります。

案１ CLOBをVARCHAR2サイズ内に分割しながら表示する

案2 SQL*Plusで表示する

案３ 無名ブロックプロシージャで表示する