使用Oracle字典视图发现PL / SQL包级别类型 [英] Discover PL/SQL package-level types using Oracle dictionary views

问题描述

我可以发现使用字典视图在Oracle PL / SQL包中声明的类型吗？即当我声明这一点：

 创建或替换包my_types AS 
 TYPE t_cursor_type IS REF CURSOR; 
 TYPE t_table_type IS TABLE OF some_table％rowtype; 
 END my_types; 
  

我想正式发现 t_cursor_type 和 t_table_type 。他们似乎没有列在

  SYS.ALL_TYPES 
  

正式我的意思是我想要一些正式的元数据，即查询 USER_SOURCE won

解决方案

无需任何特殊编译标志的复杂解决方案

以下是我为3.9版中的jOOQ代码生成器设计的解决方案，以发现PL / SQL RECORD 类型。它只会发现实际引用的类型：

  SELECT x.type_owner，x.type_name， x.type_subname，a。* 
 FROM all_arguments a 
 JOIN（
 SELECT 
 type_owner，type_name，type_subname，
 MIN（owner）KEEP（DENSE_RANK FIRST ORDER BY owner ，package_name，subprogram_id，sequence）owner，
 MIN（package_name）KEEP（DENSE_RANK FIRST ORDER BY owner，package_name，subprogram_id，sequence）package_name，
 MIN（subprogram_id）KEEP（DENSE_RANK FIRST ORDER BY owner，package_name ，subprogram_id，sequence）subprogram_id，
 MIN（sequence）KEEP（DENSE_RANK FIRST ORDER BY owner，package_name，subprogram_id，sequence）sequence，
 MIN（next_sibling）KEEP（DENSE_RANK FIRST ORDER BY owner，package_name，subprogram_id ，序列）next_sibling，
 MIN（data_level）KEEP（DENSE_RANK FIRST ORDER BY owner，package_name，subprogram_id，sequence）data_level 
 FROM（
 SELECT 
 LEAD（序列，1，序列）OVER（
 PARTITION BY owner，package_name，subprogram_id，data_level 
 ORDER BY序列
）next_sibling，
 a。 type_owner，
 a.type_name，
 a.type_subname，
 a.owner，
 a.package_name，
 a.subprogram_id，
 a.sequence， 
 a.data_level，
 a.data_type 
 FROM all_arguments a 
）a 
 WHERE data_type ='PL / SQL RECORD'
 GROUP BY type_owner，type_name ，type_subname 
）x 
 ON（a.owner，a.package_name，a.subprogram_id）
 =（（x.owner，x.package_name，x.subprogram_id））
 AND a.sequence BETWEEN x.sequence AND x.next_sibling 
 AND a.data_level = x.data_level + 1 
 ORDER BY x.type_owner，x.type_name，x.type_subname，a.sequence 
; 
  

可以在这里找到关于上述技术的更多细节。

相对容易（但不完整）的解决方案取决于特殊的编译标志

我刚刚发现这个非常有趣的网站，其中列出了使用字典视图的查询在这里的zep的回答中提到。使用该问题的包，请使用以下查询：

  WITH plscope_hierarchy 
 AS（ SELECT line 
，col 
，name 
，TYPE 
，usage 
，usage_id 
，usage_context_id 
 FROM all_identifiers 
 WHERE owner = USER 
 AND object_name ='MY_TYPES'
 AND object_type ='PACKAGE'）
 SELECT LPAD（''，3 *（LEVEL  -  1））
 || TYPE 
 || ''
 ||名字
 || '（'
 || usage 
 ||'）'
 identifier_hierarchy 
 FROM plscope_hierarchy 
 START WITH uses_context_id = 0 
 CONNECT BY PRIOR usage_id = usage_context_id 
 ORDER SIBLINGS BY line，col; 
  

获得此结果

 包装MY_TYPES（声明）
 REFCURSOR T_CURSOR_TYPE（DECLARATION）
 NESTED TABLE T_TABLE_TYPE（DECLARATION）
  pre> 
 
 
不幸的是，嵌套表类型没有进一步解决。
 
Can I discover types declared in Oracle PL/SQL packages using dictionary views? I.e. when I declare this:
CREATE OR REPLACE PACKAGE my_types AS
  TYPE t_cursor_type IS REF CURSOR;
  TYPE t_table_type IS TABLE OF some_table%rowtype;
END my_types;
I'd like to formally discover t_cursor_typeand t_table_type. They don't seem to be listed in
SYS.ALL_TYPES
By "formally" I mean I'd like some "formal meta-data", i.e. querying USER_SOURCE won't do the trick.
解决方案
Complex solution that works without any special compilation flags

Here's a solution that I've devised for the jOOQ code generator in version 3.9 to discover PL/SQL RECORD types. It only discovers those types that are actually referenced:
SELECT x.type_owner, x.type_name, x.type_subname, a.*
FROM all_arguments a
JOIN (
  SELECT 
    type_owner, type_name, type_subname,
    MIN(owner        ) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) owner,
    MIN(package_name ) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) package_name,
    MIN(subprogram_id) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) subprogram_id,
    MIN(sequence     ) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) sequence,
    MIN(next_sibling ) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) next_sibling,
    MIN(data_level   ) KEEP (DENSE_RANK FIRST ORDER BY owner, package_name, subprogram_id, sequence) data_level
  FROM (
    SELECT 
      LEAD(sequence, 1, sequence) OVER (
        PARTITION BY owner, package_name, subprogram_id, data_level 
        ORDER BY sequence
      ) next_sibling,
      a.type_owner,
      a.type_name, 
      a.type_subname,
      a.owner,
      a.package_name,
      a.subprogram_id,
      a.sequence,
      a.data_level,
      a.data_type
    FROM all_arguments a
  ) a
  WHERE data_type = 'PL/SQL RECORD'
  GROUP BY type_owner, type_name, type_subname
) x
ON (a.owner, a.package_name, a.subprogram_id) 
= ((x.owner, x.package_name, x.subprogram_id))
AND a.sequence BETWEEN x.sequence AND x.next_sibling
AND a.data_level = x.data_level + 1
ORDER BY x.type_owner, x.type_name, x.type_subname, a.sequence
;
More details about the above technique can be found here.

Relatively easy (but incomplete) solution that depends on a special compilation flag

I've just discovered this extremely interesting website, which lists a query that uses the dictionary views mentioned in zep's answer here. Using the package from the question, use this query:
WITH plscope_hierarchy
        AS (SELECT line
                 , col
                 , name
                 , TYPE
                 , usage
                 , usage_id
                 , usage_context_id
              FROM all_identifiers
             WHERE     owner = USER
                   AND object_name = 'MY_TYPES'
                   AND object_type = 'PACKAGE')
SELECT    LPAD (' ', 3 * (LEVEL - 1))
       || TYPE
       || ' '
       || name
       || ' ('
       || usage
       || ')'
          identifier_hierarchy
  FROM plscope_hierarchy
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
ORDER SIBLINGS BY line, col;
Yielding this result
PACKAGE MY_TYPES (DECLARATION)
   REFCURSOR T_CURSOR_TYPE (DECLARATION)
   NESTED TABLE T_TABLE_TYPE (DECLARATION)
Unfortunately, the nested table type is not resolved any further.

                        
这篇关于使用Oracle字典视图发现PL / SQL包级别类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！