[Oracle Graph Server 20.4] PGQL CREATE 文で発行される SQL と PGQL を表示する方法

String query = String.format(
    "CREATE PROPERTY GRAPH %s " +
    " VERTEX TABLES(n1 LABEL hoge PROPERTIES(id, name, rate), n2 LABEL hoge2 PROPERTIES(id, name, rate)) " +
    " EDGE TABLES(e1 SOURCE KEY(n1_id) REFERENCES n1 DESTINATION KEY (n2_id) REFERENCES n2 LABEL el NO PROPERTIES)",
    graphName);
PgqlPreparedStatement pst = pgqlConn.prepareStatement(query);

// Create 文で発行される SQL,PL/SQL
PgqlSqlCreateTransImpl createTrans = (PgqlSqlCreateTransImpl) pst.translateStatement();
Object[] createTranslations = createTrans.getCreateTranslation();
Stream.of(createTranslations).filter(s -> s != null).forEach(s -> {
    switch(s.getClass().getName()){
        case "java.lang.String":
            System.out.println(s);
            break;
        case "[Ljava.lang.String;":
            Stream.of(((String[]) s)).filter(t -> t != null).forEach(System.out::println);
            break;
        default:
            System.out.println(s);
    }
});

{ call opg_apis.create_pg(graph_owner => :1, graph_name => :2, tbs_set => null, options => 'SKIP_INDEX=T') }
INSERT /*+ append */ INTO "TEST_USER".TEST_20_4_GRAPHVT$
(VID, VL, K, T, V, VN, VT)
SELECT VID, VL, K, T, V, VN, VT
FROM (
  SELECT 
    VID, VL, K, T, V, VN, VT
  FROM(
    WITH T0 AS (
      SELECT /*+ materialize */
        round(sys_op_combined_hash(n'"N1"' || '|' || TABLE_KEY)/2) AS VID
        , VL, "ID", "NAME", "RATE"
      FROM ( 
        SELECT 
          "ID" AS TABLE_KEY
          , n'HOGE' AS VL, "ID", "NAME", "RATE"
        FROM "TEST_USER"."N1"
      )
    )
    SELECT VID, VL, K, T, to_nchar(VN, 'TM9', 'NLS_Numeric_Characters=''.,''') AS V, VN, VT
    FROM (
      SELECT
        VID, VL, K,
          to_number(4) AS T,
          to_nchar(null) as V, to_number(property_value) as VN, to_timestamp_tz(null) VT
      FROM T0
      UNPIVOT (
        property_value FOR K IN (
          "ID" AS n'ID'
        )
      )
    )
  UNION ALL
    SELECT VID, VL, K, T, V, VN, VT
    FROM (
      SELECT
        VID, VL, K,
          to_number(1) AS T,
          to_nchar(property_value) as V, to_number(null) as VN, to_timestamp_tz(null) VT
      FROM T0
      UNPIVOT (
        property_value FOR K IN (
          "NAME" AS n'NAME'
        )
      )
    )
  UNION ALL
    SELECT VID, VL, K, T, to_nchar(VN, 'TM9', 'NLS_Numeric_Characters=''.,''') AS V, VN, VT
    FROM (
      SELECT
        VID, VL, K,
          to_number(4) AS T,
          to_nchar(null) as V, to_number(property_value) as VN, to_timestamp_tz(null) VT
      FROM T0
      UNPIVOT (
        property_value FOR K IN (
          "RATE" AS n'RATE'
        )
      )
    )
  )
)
INSERT /*+ append */ INTO "TEST_USER".TEST_20_4_GRAPHVT$
(VID, VL, K, T, V, VN, VT)
SELECT VID, VL, K, T, V, VN, VT
FROM (
  SELECT 
    VID, VL, K, T, V, VN, VT
  FROM(
    WITH T0 AS (
      SELECT /*+ materialize */
        round(sys_op_combined_hash(n'"N2"' || '|' || TABLE_KEY)/2) AS VID
        , VL, "ID", "NAME", "RATE"
      FROM ( 
        SELECT 
          "ID" AS TABLE_KEY
          , n'HOGE2' AS VL, "ID", "NAME", "RATE"
        FROM "TEST_USER"."N2"
      )
    )
    SELECT VID, VL, K, T, to_nchar(VN, 'TM9', 'NLS_Numeric_Characters=''.,''') AS V, VN, VT
    FROM (
      SELECT
        VID, VL, K,
          to_number(4) AS T,
          to_nchar(null) as V, to_number(property_value) as VN, to_timestamp_tz(null) VT
      FROM T0
      UNPIVOT (
        property_value FOR K IN (
          "ID" AS n'ID'
        )
      )
    )
  UNION ALL
    SELECT VID, VL, K, T, V, VN, VT
    FROM (
      SELECT
        VID, VL, K,
          to_number(1) AS T,
          to_nchar(property_value) as V, to_number(null) as VN, to_timestamp_tz(null) VT
      FROM T0
      UNPIVOT (
        property_value FOR K IN (
          "NAME" AS n'NAME'
        )
      )
    )
  UNION ALL
    SELECT VID, VL, K, T, to_nchar(VN, 'TM9', 'NLS_Numeric_Characters=''.,''') AS V, VN, VT
    FROM (
      SELECT
        VID, VL, K,
          to_number(4) AS T,
          to_nchar(null) as V, to_number(property_value) as VN, to_timestamp_tz(null) VT
      FROM T0
      UNPIVOT (
        property_value FOR K IN (
          "RATE" AS n'RATE'
        )
      )
    )
  )
)
INSERT /*+ append */ INTO "TEST_USER".TEST_20_4_GRAPHGE$
(EID, SVID, DVID, EL, K, T, V, VN, VT)
SELECT EID, SVID, DVID, EL, K, T, V, VN, VT
FROM (
  SELECT 
    EID, SVID, DVID, EL, K, T, V, VN, VT
  FROM(
    WITH T0 AS (
      SELECT /*+ materialize */
        round(sys_op_combined_hash(n'"E1"' || '|' || TABLE_KEY)/2) AS EID, SVID, DVID
        , EL
      FROM ( 
        SELECT 
          "ID" AS TABLE_KEY,
          round(sys_op_combined_hash(n'"N1"' || '|' || "N1_ID")/2) AS SVID,
          round(sys_op_combined_hash(n'"N2"' || '|' || "N2_ID")/2) AS DVID
          , n'EL' AS EL
        FROM "TEST_USER"."E1"
        WHERE "N1_ID" IS NOT NULL AND "N2_ID" IS NOT NULL
      )
    )
    SELECT 
      EID, SVID, DVID
      , EL,
      to_nchar(null) AS K,
      to_number(null) AS T,
      to_nchar(null) AS V,
      to_number(null) AS VN,
      to_timestamp_tz(null) AS VT
    FROM T0
  )
)
INSERT /*+ append */ INTO "TEST_USER".TEST_20_4_GRAPHVD$ 
SELECT VID, VL, CNT
FROM (SELECT VID, VL, COUNT(*) AS CNT
      FROM "TEST_USER".TEST_20_4_GRAPHVT$
      GROUP BY VID, VL)
INSERT /*+ append */ INTO "TEST_USER".TEST_20_4_GRAPHGT$ 
SELECT EID, EL, SVID, DVID, sys_op_numtoraw(sys_op_combined_hash(EL)) AS ELH, lengthb(EL) AS ELS, CNT
FROM (SELECT EID, EL, SVID, DVID, COUNT(*) AS CNT
      FROM "TEST_USER".TEST_20_4_GRAPHGE$
      GROUP BY EID, SVID, DVID, EL)
{ call opg_apis.create_pg(graph_owner => :1, graph_name => :2, dop => :3, tbs_set => null, options => 'SKIP_TABLE=T') }
{ call opg_apis.analyze_pg(graph_name => :1, degree => :2, cascade => DBMS_STATS.AUTO_CASCADE, no_invalidate => DBMS_STATS.AUTO_INVALIDATE, force => false, options => null, graph_owner => :3) }