【Oracle DB】依存関係のあるDBオブジェクトの階層情報を抽出する

# やりたいこと
Oracle Database内で依存関係を持つオブジェクトのストラクチャ型の部品表（BOM）が欲しい。

# 環境
Oracle Database 23ai Free

# DBオブジェクトの相関図
ぐっちゃぐちゃです。ですが、私の経験上ではこのくらい複雑な構成も稀に見ます。
```plantuml
@startuml
object TABLE_A
object TABLE_B
diamond AB_DIA
object VIEW_C

object TABLE_D
object SYNONYM_E
object TABLE_F
object TABLE_G
diamond EFG_DIA
object VIEW_H
object SYNONYM_I

diamond CI_DIA
object VIEW_J

TABLE_A <|-- AB_DIA
TABLE_B <|--AB_DIA
AB_DIA <|-- VIEW_C

TABLE_D<|-SYNONYM_E
SYNONYM_E<|-EFG_DIA
TABLE_F <|-- EFG_DIA
TABLE_G <|-- EFG_DIA
EFG_DIA <|-- VIEW_H
VIEW_H <|-- SYNONYM_I

VIEW_C <|-- CI_DIA
SYNONYM_I <|-- CI_DIA
CI_DIA <|-- VIEW_J

@enduml
```

# DDL
上記相関図を実現するべく、以下のスクリプトを実行します。
```SQL
CREATE TABLE TABLE_A (COL_A1 NUMBER PRIMARY KEY, COL_A2 VARCHAR2(10 CHAR));
CREATE TABLE TABLE_B (COL_B1 NUMBER PRIMARY KEY, COL_B2 NUMBER NOT NULL, COL_B3 VARCHAR2(10 CHAR));
CREATE VIEW VIEW_C AS SELECT A.COL_A1 AS COL_C1, B.COL_B1 AS COL_C2, B.COL_B3 AS COL_C3 FROM TABLE_A A JOIN TABLE_B B ON A.COL_A1 = B.COL_B2;
CREATE TABLE TABLE_D (COL_D1 NUMBER PRIMARY KEY, COL_D2 VARCHAR2(10 CHAR));
CREATE SYNONYM SYNONYM_E FOR TABLE_D;
CREATE TABLE TABLE_F (COL_F1 DATE NOT NULL, COL_F2 NUMBER NOT NULL, COL_F3 NUMBER NOT NULL);
CREATE TABLE TABLE_G (COL_G1 DATE NOT NULL, COL_G2 NUMBER NOT NULL, COL_G3 NUMBER NOT NULL);
CREATE VIEW VIEW_H AS SELECT FG.COL_H1, FG.COL_H2, FG.COL_H3, E.COL_D2 AS COL_H4 FROM (SELECT COL_F1 AS COL_H1, COL_F2 AS COL_H2, COL_F3 AS COL_H3 FROM TABLE_F UNION ALL SELECT COL_G1 AS COL_H1, COL_G2 AS COL_H2, COL_G3 AS COL_H3 FROM TABLE_G) FG JOIN SYNONYM_E E ON E.COL_D1 = FG.COL_H2;
CREATE SYNONYM SYNONYM_I FOR VIEW_H;
CREATE VIEW VIEW_J AS SELECT COL_C2 AS COL_J1, COL_H4 AS COL_J2 FROM VIEW_C C LEFT JOIN SYNONYM_I I ON I.COL_H3 = C.COL_C1;
```

# SQL
BOM出力の実践です。以下のクエリで検索を実行。
ポイントは階層問い合わせを利用する点です。
```SQL:query
SELECT
    LEVEL
   ,referenced_type
   ,CONNECT_BY_ROOT name || SYS_CONNECT_BY_PATH(TO_CHAR(referenced_name), '/') AS structure
   ,CONNECT_BY_ISLEAF AS is_leaf
FROM
    user_dependencies
START WITH
    name = 'VIEW_J'
CONNECT BY PRIOR
    referenced_name = name
;
```
クエリを実行すると以下の結果が得られます。`VIEW_J`が依存している全てのオブジェクトが出力できています。`IS_LEAF`列が"1"になっているデータが末端になるオブジェクトです。
```text:result
     LEVEL REFERENCED_TYPE     STRUCTURE                                             IS_LEAF
---------- ------------------- -------------------------------------------------- ----------
         1 SYNONYM             VIEW_J/SYNONYM_I                                            0
         2 VIEW                VIEW_J/SYNONYM_I/VIEW_H                                     0
         3 SYNONYM             VIEW_J/SYNONYM_I/VIEW_H/SYNONYM_E                           0
         4 TABLE               VIEW_J/SYNONYM_I/VIEW_H/SYNONYM_E/TABLE_D                   1
         3 TABLE               VIEW_J/SYNONYM_I/VIEW_H/TABLE_F                             1
         3 TABLE               VIEW_J/SYNONYM_I/VIEW_H/TABLE_G                             1
         1 VIEW                VIEW_J/VIEW_C                                               0
         2 TABLE               VIEW_J/VIEW_C/TABLE_A                                       1
         2 TABLE               VIEW_J/VIEW_C/TABLE_B                                       1

9行が選択されました。 
```
見た目の好みもあると思いますので、適宜アレンジしながら使っていただければと思います。
本例では階層問い合わせで使える`CONNECT_BY_ROOT`演算子や`LEVEL`、`CONNECT_BY_ISLEAF`擬似列などを参考例として使用しています。
スキーマを跨いだ検索をしたい場合は`all_dependencies`を参照しましょう。