有关pl/sql存储程序文本的问题 [英] question about pl/sql stored program text

问题描述

我使用TOAD进行PL/SQL开发.在TOAD中，当我键入过程名称并按f4时，可以看到此过程的源代码.我认为TOAD从v $ sqltext视图获取源代码.为了确认我的想法，我写了一个查询:

I use TOAD to do my PL/SQL development. In TOAD when i type a procedure name and press f4, I can see this procedure's source code. I think TOAD get the source code from v$sqltext view. To confirm my thought, I wrote a query:

select * from v$sqltext

但是当我执行上层查询时，Oracle给我一个错误:

but when I execute the upper query, Oracle give me an error:

ORA-00942:表或视图不存在 存在 00942. 00000-表或视图不存在" *原因:
*操作:第29行的错误:第15列的

ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause:
*Action: Error at Line: 29 Column: 15

所以我认为TOAD是从其他地方而不是v $ sqltext视图获取过程源的.有人可以告诉我吗?非常感谢.

So I think TOAD get the procedure's source from other place instead of v$sqltext view. Anyone can tell me about this? Great thanks.

推荐答案

存储过程的完整查询(不在软件包中):

The full query for a stored procedure (not in a package):

select text
from   all_source
where  owner = 'MYSCHEMA'
and    type = 'PROCEDURE'
and    name = 'MY_PROCEDURE'
order by line;

如果您以用户MYSCHEMA身份连接，则可以使用USER_SOURCE:

If you are connected as user MYSCHEMA than you can use USER_SOURCE:

select text
from   user_source
where  type = 'PROCEDURE'
and    name = 'MY_PROCEDURE'
order by line;

TYPE的其他值是:

Other values for TYPE are:

• TYPE BODY
• 功能
• TRIGGER
• TYPE
• JAVA源
• 包装体
• 包装

这篇关于有关pl/sql存储程序文本的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！