データタイプANYDATAの参照方法

概要

Oracle Databaseには複数のデータ型(VARCHAR2、NUMBER等々)のデータを格納することができるANYDATAという特殊なデータ型が存在しています。

Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 19c 272 ANYDATAタイプ

ANYDATAタイプには、指定のタイプのインスタンスおよびそのタイプの説明が含まれています。つまり、ANYDATAは自己記述型です。ANYDATAは、データベースに永続的に格納できます。

通常、テーブル設計・作成時に意図してこのデータ型を指定することはないと思いますが、SPA(SQL Performance Analyzer)などで利用するSTS(SQL Tuning Set)に格納されたバインド変数値で使用されていたりします。
そのため、バージョンアップ検証などでSPAを使用した際に確認対象のSQLで使用しているバインド変数値を確認したい場合などにはこのデータを参照するために特殊なアクセス・ファンクションを使う必要があります。

STS(SQL Tuning Set)とは

SPA(SQL Performance Analyzer)やアドバイザ等に対するINPUT情報として利用できます。
下記の情報を含むデータベース・オブジェクトです。

SQL文（SQLテキスト）
実行コンテキスト（スキーマ、アプリケーション・モジュール名、バインド変数など）
実行統計（実行時間、CPU時間、バッファ読み取り量、ディスク読み取り量など）
実行計画

このSTSの情報を各種ツールにかけて分析を実行します。

f:id:undercovergeek:20210109103403p:plain

ANYDATAの参照

STSに含めたバインド変数を使用したSQL

SQL_ID:1y431vxyc0xj7

variable var1 number;
variable var2 char(10);
variable var3 varchar2(10);
variable var4 varchar2(19);
execute :var1 := 1;
execute :var2 := 'a1';
execute :var3 := 'b1';
execute :var4 := '03-06-2010 18:00:00';
select * from tab1 
where col1 = :var1 and col2 = :var2 and col3= :var3
and col4 = to_date(:var4, 'DD-MM-YYYY HH24:MI:SS');

そのまま参照では見れない（「ANYDATA()」が返却される）

SQL> select SQL_ID,VALUE FROM ALL_SQLSET_BINDS WHERE SQL_ID='1y431vxyc0xj7';
SQL_ID                                  VALUE()
--------------------------------------- --------------------------------------------------
1y431vxyc0xj7                           ANYDATA()
1y431vxyc0xj7                           ANYDATA()
1y431vxyc0xj7                           ANYDATA()
1y431vxyc0xj7                           ANYDATA()

参照方法(ファンクションを使って参照)

GETTYPENAMEメンバー・ファンクションを使用してそれぞれキャストして返す（※キャラクタ型はそのままアクセスファンクション）

SQL> SELECT
  ANYDATA.GETTYPENAME(value) TYPE_NAME,
      CASE ANYDATA.GETTYPENAME(value)
        WHEN 'SYS.NUMBER'   THEN TO_CHAR(ANYDATA.ACCESSNUMBER(value))
        WHEN 'SYS.DATE'     THEN TO_CHAR(ANYDATA.ACCESSDATE(value))
        WHEN 'SYS.VARCHAR2' THEN ANYDATA.ACCESSVARCHAR2(value)
        WHEN 'SYS.CHAR'     THEN ANYDATA.ACCESSCHAR(value)
        ELSE NULL
      END AS DATA
FROM DBA_SQLSET_BINDS
WHERE SQL_ID = '1y431vxyc0xj7'
;
  2    3    4    5    6    7    8    9   10   11   12  
TYPE_NAME            DATA
-------------------- --------------------
SYS.NUMBER           1
SYS.CHAR             a1
SYS.VARCHAR2         b1
SYS.VARCHAR2         03-06-2010 18:00:00

バインド変数が確認できました。
今回参照した以外のデータ型も参考資料にあるアクセスファンクションで参照できます。

参考資料

Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 19c
272 ANYDATAタイプ
https://docs.oracle.com/cd/F19136_01/arpls/ANYDATA-TYPE.html#GUID-08696B1A-5E92-45C6-89E9-00F934311797

GAGA LIFE.

インフラエンジニアブログ