blocks|key|4896375|text|它不能从all_types或all_objects获得，但是作为替代，您可以向user_source查询类型的名称，它应该会显示出来。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4896376|entityMap^0|4|9|E|B|13|B|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|O|8|@]|D|@]|E|$]]]|G|$]]

It won't be available from <code>all_types</code> or <code>all_objects</code> but as an alternative you can query <code>user_source</code> for the name of the type, and it should show up.

blocks|key|2824541|text|编辑:从Zep找到的文档中，语句仅适用于oracle+11.1之前的版本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2824542|对你来说坏消息是：|2824543|这些类型仅在包执行时存在，因此它们永远不会出现在oracle字典中。换句话说，这些类型不在数据库中，而是在包中。|2824544|给你的好消息是：|2824545|包在数据字典中，所以我们可以运行一些sql来搜索包含您关心的关键字的过程和包；在本例中为'TYPE'：|2824546|SELECT+*
FROM+dba_source
WHERE+type+IN+('PROCEDURE','PACKAGE','PACKAGE+BODY')
AND+text+LIKE+'%25TYPE%25IS%25';|code-block|syntax|javascript|2824547|从那里定义您关心的所有者，并执行一些正则表达式或其他花哨的代码来提取所需的数据。|2824548|或者，如果要查看数据字典中的类型，则必须在包外部创建它们，然后从包中引用该类型。|2824549|参考文献：|2824550|Oracle®+Database+Reference+11g+Release+1+(11.1)+-+ALL_TYPES|offset|length|2824551|entityMap|0|LINK|mutability|MUTABLE|url|http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_2112.htm^0|0|0|0|0|0|0|0|0|0|0|1N|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|18|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|19|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1A|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|1B|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1D|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1E|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1F|8|@]|9|@$W|1G|X|1H|1|1I]]|A|$]]|$1|Y|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|15]]]]

EDIT: From documentation found by Zep, statement only applies oracle versions prior to 11.1

The bad news for you:

Those types only exist upon execution of the package, so they would never be seen in the oracle dictionary. In other words, those types are not in the database, rather they are in the package.

The good new for you:

The package is in the data dictionary so we could run some sql to search procedures and packages that contain the keywords you care about; in this example 'TYPE':

<pre><code>SELECT *
FROM dba_source
WHERE type IN ('PROCEDURE','PACKAGE','PACKAGE BODY')
AND text LIKE '%TYPE%IS%';
</code></pre>

From there define the owner you care about and the do some regex or other fancy code to pull the data you need.

Alternatively, if you want to see the types in the data dictionary you would have to create them outside the package and then reference that type from the package.

References:

<a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_2112.htm" rel="nofollow">Oracle® Database Reference 11g Release 1 (11.1) - ALL_TYPES</a>

blocks|key|1208973|text|仅从11.1开始。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1208974|从手册中：|1208975|http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_plscope.htm#ADFNS02204|offset|length|1208976|http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams189.htm#REFRN10271|1208977|SQL/Scope是一个编译器驱动的工具，它在程序单元编译时收集有关PL/+|1208978|源代码中标识符的数据，并使其在静态数据字典视图中可用。收集的数据包括有关标识符类型、用法(声明、定义、引用、调用、赋值)以及源代码中每个用法的位置的信息。|blockquote|1208979|1208980|1208981|演示|1208982|CREATE+OR+REPLACE+PACKAGE+my_types+AS
++TYPE+t_cursor_type+IS+REF+CURSOR;
++TYPE+t_table_type+IS+TABLE+OF+employees%25rowtype;
++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>+|code-block|syntax|javascript|1208983|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|0|2J|0|0|0|2J|1|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|16|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|17|8|@]|9|@$F|18|G|19|1|1A]]|A|$]]|$1|H|3|I|5|6|7|1B|8|@]|9|@$F|1C|G|1D|1|1E]]|A|$]]|$1|J|3|K|5|6|7|1F|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|1G|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1J|8|@]|9|@]|A|$]]|$1|S|3|T|5|U|7|1K|8|@]|9|@]|A|$V|W]]|$1|X|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]]|Y|$Z|$5|10|11|12|A|$13|E]]|14|$5|10|11|12|A|$13|I]]]]

Only from the 11.1 onward.

From the manual:

<a href="http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_plscope.htm#ADFNS02204">http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_plscope.htm#ADFNS02204</a>

<a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams189.htm#REFRN10271">http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams189.htm#REFRN10271</a>

<blockquote>
 PL/Scope is a compiler-driven tool that collects data about identifiers in PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assignment) and the location of each usage in the source code.
</blockquote>

DEMO

<pre><code>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&gt; 
</code></pre>

blocks|key|4896454|text|无需任何特殊编译标志即可工作的复杂解决方案|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4896455|下面是我为版本3.9中的jOOQ代码生成器设计的一个用于发现PL/SQL+RECORD类型的解决方案。它只发现那些实际被引用的类型：|offset|length|style|CODE|4896456|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+%2B+1
ORDER+BY+x.type_owner,+x.type_name,+x.type_subname,+a.sequence
;|code-block|syntax|javascript|4896457|More+details+about+the+above+technique+can+be+found+here。|4896458|依赖于特殊编译标志的相对简单(但不完整)的解决方案|4896459|我刚刚发现了this+extremely+interesting+website，它列出了一个使用字典视图mentioned+in+zep's+answer+here的查询。使用问题中的包，使用以下查询：|4896460|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))
+++++++%7C%7C+TYPE
+++++++%7C%7C+'+'
+++++++%7C%7C+name
+++++++%7C%7C+'+('
+++++++%7C%7C+usage
+++++++%7C%7C+')'
++++++++++identifier_hierarchy
++FROM+plscope_hierarchy
START+WITH+usage_context_id+=+0
CONNECT+BY+PRIOR+usage_id+=+usage_context_id
ORDER+SIBLINGS+BY+line,+col;|4896461|产生这样的结果|4896462|PACKAGE+MY_TYPES+(DECLARATION)
+++REFCURSOR+T_CURSOR_TYPE+(DECLARATION)
+++NESTED+TABLE+T_TABLE_TYPE+(DECLARATION)|4896463|不幸的是，嵌套表类型不会被进一步解析。|4896464|entityMap|0|LINK|mutability|MUTABLE|url|https://blog.jooq.org/2016/11/08/use-jooq-to-read-write-oracle-plsql-record-types/|1|http://www.oracle.com/technetwork/articles/grid/o50plsql-165471.html|2|https://stackoverflow.com/a/6413976/521799^0|0|11|6|0|0|0|1K|0|0|0|6|Y|1|1H|U|2|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1D|8|@$D|1E|E|1F|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|1G|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1H|8|@]|9|@$D|1I|E|1J|1|1K]]|A|$]]|$1|O|3|P|5|6|7|1L|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1M|8|@]|9|@$D|1N|E|1O|1|1P]|$D|1Q|E|1R|1|1S]]|A|$]]|$1|S|3|T|5|J|7|1T|8|@]|9|@]|A|$K|L]]|$1|U|3|V|5|6|7|1U|8|@]|9|@]|A|$]]|$1|W|3|X|5|J|7|1V|8|@]|9|@]|A|$K|L]]|$1|Y|3|Z|5|6|7|1W|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|1X|8|@]|9|@]|A|$]]]|11|$12|$5|13|14|15|A|$16|17]]|18|$5|13|14|15|A|$16|19]]|1A|$5|13|14|15|A|$16|1B]]]]

<h3>Complex solution that works without any special compilation flags</h3>

Here's a solution that I've devised for the jOOQ code generator in version 3.9 to discover PL/SQL <code>RECORD</code> types. It only discovers those types that are actually referenced:

<pre class="lang-sql prettyprint-override"><code>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
;
</code></pre>

<a href="https://blog.jooq.org/2016/11/08/use-jooq-to-read-write-oracle-plsql-record-types/" rel="nofollow noreferrer">More details about the above technique can be found here</a>.

<h3>Relatively easy (but incomplete) solution that depends on a special compilation flag</h3>

I've just discovered <a href="http://www.oracle.com/technetwork/articles/grid/o50plsql-165471.html" rel="nofollow noreferrer">this extremely interesting website</a>, which lists a query that uses the dictionary views <a href="https://stackoverflow.com/a/6413976/521799">mentioned in zep's answer here</a>. Using the package from the question, use this query:

<pre class="lang-sql prettyprint-override"><code>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;
</code></pre>

Yielding this result

<pre class="lang-none prettyprint-override"><code>PACKAGE MY_TYPES (DECLARATION)
 REFCURSOR T_CURSOR_TYPE (DECLARATION)
 NESTED TABLE T_TABLE_TYPE (DECLARATION)
</code></pre>

Unfortunately, the nested table type is not resolved any further.

blocks|key|3110337|text|尝试使用集合作为包函数/过程字典的参数，然后查看into+dictionary+user_arguments|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3110338|select+*
++from+user_arguments+a
+where+a.object_name+=+'MYFUNCTIONNAME'
+order+by+a.sequence;|code-block|syntax|javascript|3110339|entityMap^0|14|E|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Try to use collection as an argument of package function/procedurę and next see into dictionary user_arguments:

<pre><code>select *
 from user_arguments a
 where a.object_name = 'MYFUNCTIONNAME'
 order by a.sequence;
</code></pre>

blocks|key|2824700|text|现在(18c%2B+IIRC)有all_plsq_*视图可以完成这项工作。有关记录类型，请参阅user_plsql_types和all_plsql_type_attrs。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2824701|entityMap^0|E|A|19|G|1Q|K|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|O|8|@]|D|@]|E|$]]]|G|$]]

Nowadays (18c+ IIRC) there are <code>all_plsq_*</code> views that do the job. For record types see <code>user_plsql_types</code> and <code>all_plsql_type_attrs</code>.

Can I discover types declared in Oracle PL/SQL packages using dictionary views? I.e. when I declare this:

<pre><code>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;
</code></pre>

I'd like to formally discover <code>t_cursor_type</code>and <code>t_table_type</code>. They don't seem to be listed in

<pre><code>SYS.ALL_TYPES
</code></pre>

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

Discover PL/SQL package-level types using Oracle dictionary views

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

是否可以使用字典视图发现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_tabl...

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

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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