過去実行したSQLはV$SQLやV$SQLAREAのSQL_TEXTから取得する事できますが、1000バイトを超えるSQLはSQL_FULLTEXTを参照する必要があります。ただしSQL_TEXTと異なり改行コードが含まれている事や、SQL発行ツール自体が VARCHAR2(4000)を 超える文字列を表示できない制限があって期待通りの表示ができません。これら課題をクリアするため以下テーブルファンクション（ストアドパッケージ）を作成しました。
SQL_IDをパラメータにSQL_TEXTを表示するテーブルファンクション
以下実行イメージです。
select * from table(devtool.PICKUP_SQL('0pyq96jtkdr31'))
;
実行SQLテキスト
select '伊集院' as "'--姓--'"
/* '20/06/01 訂正
,'光' as "名"
*
*/
,'ヒカル' as "/* 名 */"
,'45' "年齢" -- 2020/07/01 追加
-- '男' as "性別"
,'１行目
２行目
-- ３行目 ここは有効
/*
４行目 ここも有効
*/
５行目' as "備考"
from dual実行結果
テーブルファンクションの登録
事前作業としての権限付与
今回作成するプロシージャは内部でSYS.GV_$SQLAREAへアクセスするため作成するスキーマ（今回の例ではSOONIとします）には SYS.GV_$SQLAREA へのSELECT権限付与が必要です。注意点としてスキーマへ直接権限付与する必要があります。ロール経由での付与ではプロシージャのコンパイルエラーが発生します。ストアドプロシージャのコンパイルはスキーマが直接保持している権限しか確認しないためです。
-- 案１ オブジェクト権限付与 sysスキーマから以下実行
SQL> grant select on GV_$SQLAREA to SOONI;
権限付与が成功しました。
SQL>
-- 案2 システム権限付与 sysもしくはsystemスキーマから実行
SQL> grant SELECT ANY DICTIONARY to SOONI
2 ;
権限付与が成功しました。
SQL>ストアドパッケージの登録
以下ストアドパッケージ（ヘッダー/ボディー）を適切なスキーマ（今回はSOONI）へ登録してください。
ストアドパッケージ（HEADER）
create or replace PACKAGE DEVTOOL
IS
/*
* レコード型の宣言 (テーブル・ファンクションの行の型)
*/
TYPE LIST_SQL_TYPE IS RECORD
(
SQL_ID VARCHAR2(13)
,RID NUMBER
,LSQL VARCHAR2(4000)
);
-- コレクション型の宣言 (テーブル・ファンクションの戻り値)
TYPE LIST_SQL_RETURN IS TABLE OF LIST_SQL_TYPE;
-- SQL_IDで指定するSQLをVARCHAR型レコードで返す（テーブルファンクション）
FUNCTION PICKUP_SQL(P_SQL_ID IN VARCHAR2) RETURN LIST_SQL_RETURN PIPELINED;
END;
ストアドパッケージ（BODY）
CREATE OR REPLACE PACKAGE BODY DEVTOOL AS
FUNCTION get_sql_text(P_SQL_ID IN VARCHAR2) RETURN CLOB;
/* ---------------------------------------------------------------------
* (ファンクション名）: PICKUP_SQL
*
* (機能概要）： SQL_IDで指定するSQLをVARCHAR型レコードで返す
*
* 1. 空行の削除
* 2. 「,」の後ろに２つ以上の半角スペースが続いた場合は１つにする
* 3. １行あたりの最大文字数を超える場合に限り、対象行をカンマで改行する
*
* --------------------------------------------------------------------- */
FUNCTION PICKUP_SQL
(
P_SQL_ID IN VARCHAR2
) RETURN LIST_SQL_RETURN PIPELINED
IS
-- 1行あたり最大文字数（越えた場合カンマで改行）
SEPSIZE constant number := 200;
vrec LIST_SQL_TYPE; -- 1行のレコード変数
qlno number; -- 行番号
vsql clob;
xbuff clob;
mbuff clob; -- バッファ
msize number; -- 1行サイズ
mpos number; -- 処理対象ポイント
sbuff VARCHAR2(4000); -- 最大文字数越時バッファ
ssize number; -- 最大文字数越時1行サイズ
spos number; -- 最大文字数越時処理対象ポイント
asize number;
BEGIN
vsql := get_sql_text(P_SQL_ID);
if ( vsql is null ) then
RETURN ;
end if;
-- 「,」の後ろに2つ以上の半角スペースが続いた場合1つにする
vsql := REGEXP_REPLACE(vsql, ', {2,}',', ',1,0,'i');
qlno := 0; -- 行番号
mpos := 0; -- 処理対象ポイント
msize := 0; -- 1行のサイズ
asize := DBMS_LOB.GETLENGTH(vsql); -- 全体のサイズ
loop
qlno := qlno + 1;
mpos := mpos + msize + 1;
mbuff := REGEXP_SUBSTR(vsql,'.*',mpos,1,'i'); -- 1行読み込み
msize := DBMS_LOB.GETLENGTH(mbuff); -- 1行のサイズ
-- dbms_output.put_line('msize:'||msize||' :'||mbuff);
exit when msize <=0;
if ( msize > SEPSIZE and REGEXP_INSTR(mbuff,'\,',1,2,1,'i') > 0 ) then
-- 1行のサイズが大きく2つ以上のカンマを含む場合
-- カンマで改行（但し先頭にカンマがある場合は対象外）
xbuff := REGEXP_REPLACE(mbuff, ',',CHR(10)||', ',2,0,'i');
spos := 0;
ssize := 0;
loop
spos := spos + ssize + 1;
sbuff := REGEXP_SUBSTR(xbuff,'.*',spos,1,'i');
ssize := length(sbuff);
exit when ssize <= 0 or ssize is null;
vrec.SQL_ID := to_char(ssize);
vrec.RID := qlno; -- 行番号をセット
vrec.LSQL := sbuff; -- １行分をセット
PIPE ROW(vrec); -- 結果セットをパイプへ
qlno := qlno +1;
end loop;
-- dbms_output.put_line('ssize:'||ssize||' qlno:'||qlno);
continue;
end if;
-- dbms_output.put_line('mpos:'||mpos||' msize:'||msize);
vrec.SQL_ID := P_SQL_ID;
vrec.RID := qlno; -- 行番号をセット
vrec.LSQL := mbuff; -- １行分をセット
PIPE ROW(vrec); -- 結果セットをパイプへ
end loop;
RETURN ;
END PICKUP_SQL;
/* ---------------------------------------------------------------------
* (ファンクション名）: get_sql_text
*
* (機能概要）：SQL_IDで指定するSQL_FULLTEXTをV$SQLAREAより取得する。
*
* --------------------------------------------------------------------- */
FUNCTION get_sql_text(
P_SQL_ID IN VARCHAR2
)
return clob
is
vsql clob;
BEGIN
/* RACで動作している場合を想定 */
select w.SQL_FULLTEXT into vsql from
(
select SQL_FULLTEXT
,row_number() over( partition by SQL_ID order by INST_ID) j
FROM SYS.GV_$SQLAREA
WHERE SQL_ID = P_SQL_ID
) w where j=1;
return(vsql) ;
END;
END DEVTOOL;
/
