oracle

SQL_IDでSQL_FULLTEXTを出力する

過去実行した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 "性別"
,'1行目
2行目
-- 3行目 ここは有効
/*
4行目 ここも有効
 */
5行目' as "備考"
from dual

実行結果

テーブルファンクションの登録

事前作業としての権限付与

今回作成するプロシージャは内部でSYS.GV_$SQLAREAへアクセスするため作成するスキーマ(今回の例ではSOONIとします)には SYS.GV_$SQLAREA へのSELECT権限付与が必要です。注意点としてスキーマへ直接権限付与する必要があります。ロール経由での付与ではプロシージャのコンパイルエラーが発生します。ストアドプロシージャのコンパイルはスキーマが直接保持している権限しか確認しないためです。

-- 案1 オブジェクト権限付与 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. 「,」の後ろに2つ以上の半角スペースが続いた場合は1つにする
 *  3. 1行あたりの最大文字数を超える場合に限り、対象行をカンマで改行する
 *
 * --------------------------------------------------------------------- */
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; -- 1行分をセット
            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; -- 1行分をセット
    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;
/

スポンサーリンク