使用 Oracle 字典视图发现 PL/SQL 包级类型

2021-12-24 00:00:00 dictionary package types oracle plsql

我可以使用字典视图发现在 Oracle PL/SQL 包中声明的类型吗?IE.当我声明这一点时:

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;

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

I'd like to formally discover t_cursor_typeand t_table_type. They don't seem to be listed in

SYS.ALL_TYPES

正式"我的意思是我想要一些正式的元数据"，即查询 USER_SOURCE 不会成功.

By "formally" I mean I'd like some "formal meta-data", i.e. querying USER_SOURCE won't do the trick.

推荐答案

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

这是我为 3.9 版中的 jOOQ 代码生成器设计的解决方案，用于发现 PL/SQL RECORD 类型.它只发现那些实际引用的类型:

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
;

可以在此处找到有关上述技术的更多详细信息.

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

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;

产生这个结果

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.

相关文章