使用Oracle字典视图发现PL/SQL程序包级类型
是否可以使用字典视图发现Oracle PL/SQL程序包中声明的类型？例如，当我声明这一点时：
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。它们似乎没有列在
SYS.ALL_TYPES所谓“正式”，我的意思是我想要一些“正式的元数据”，即查询USER_SOURCE不会起作用。
回答 6
Stack Overflow用户
发布于 2014-12-16 01:42:13
无需任何特殊编译标志即可工作的复杂解决方案
下面是我为版本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, 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。
依赖于特殊编译标志的相对简单(但不完整)的解决方案
我刚刚发现了this extremely interesting website，它列出了一个使用字典视图mentioned in zep's answer here的查询。使用问题中的包，使用以下查询：
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)不幸的是，嵌套表类型不会被进一步解析。
Stack Overflow用户
发布于 2011-06-20 23:52:06
仅从11.1开始。
从手册中：
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_plscope.htm#ADFNS02204
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams189.htm#REFRN10271
SQL/Scope是一个编译器驱动的工具，它在程序单元编译时收集有关PL/
源代码中标识符的数据，并使其在静态数据字典视图中可用。收集的数据包括有关标识符类型、用法(声明、定义、引用、调用、赋值)以及源代码中每个用法的位置的信息。
演示
CREATE OR REPLACE PACKAGE my_types AS
TYPE t_cursor_type IS REF CURSOR;
TYPE t_table_type IS TABLE OF employees%rowtype;
type t_associative is table number index by varchar2(20);
END my_types;
alter package my_types compile plscope_settings='IDENTIFIERS:ALL' reuse settings;
select *
from user_identifiers ui
where ui.object_type = 'PACKAGE'
and ui.usage = 'DECLARATION'
and ui.usage_context_id = '1';
NAME SIGNATURE TYPE OBJECT_NAME OBJECT_TYPE USAGE USAGE_ID LINE COL USAGE_CONTEXT_ID
------------------------------ -------------------------------- ------------------ ------------------------------ ------------- ----------- ---------- ---------- ---------- ----------------
T_ASSOCIATIVE 9A18FE6BCB72110F39CED9E08B932ECB ASSOCIATIVE ARRAY MY_TYPES PACKAGE DECLARATION 4 4 8 1
T_TABLE_TYPE 77067FE9732B492C166D38221DC3DF37 NESTED TABLE MY_TYPES PACKAGE DECLARATION 3 3 8 1
T_CURSOR_TYPE EDEC9260784B7721BC3F3DAB293F23DD REFCURSOR MY_TYPES PACKAGE DECLARATION 2 2 8 1
zep@dev> Stack Overflow用户
发布于 2011-06-20 20:41:17
编辑:从Zep找到的文档中，语句仅适用于oracle 11.1之前的版本
对你来说坏消息是：
这些类型仅在包执行时存在，因此它们永远不会出现在oracle字典中。换句话说，这些类型不在数据库中，而是在包中。
给你的好消息是：
包在数据字典中，所以我们可以运行一些sql来搜索包含您关心的关键字的过程和包；在本例中为'TYPE'：
SELECT *
FROM dba_source
WHERE type IN ('PROCEDURE','PACKAGE','PACKAGE BODY')
AND text LIKE '%TYPE%IS%';从那里定义您关心的所有者，并执行一些正则表达式或其他花哨的代码来提取所需的数据。
或者，如果要查看数据字典中的类型，则必须在包外部创建它们，然后从包中引用该类型。
参考文献：
Oracle® Database Reference 11g Release 1 (11.1) - ALL_TYPES
https://stackoverflow.com/questions/6410522
复制相似问题
腾讯云开发者
