在Oracle存储过程中搜索文本 [英] Searching for Text within Oracle Stored Procedures

问题描述

我需要使用TOAD搜索Oracle数据库中的所有存储过程。我正在寻找开发人员使用MAX + 1而不是NEXTVAL获取下一个ID号的任何地方。

I need to search through all of the stored procedures in an Oracle database using TOAD. I am looking for anywhere that the developers used MAX + 1 instead of the NEXTVAL on the sequence to get the next ID number.

我一直在做SQL Server多年并知道几种方法来做到这一点，但没有人帮助我在这里。

I've been doing SQL Server for years and know several ways to do it there but none are helping me here.

我试过使用

SELECT * FROM user_source
WHERE UPPER(text) LIKE '%blah%'

返回结果，但仅用于我的默认模式，不适用于我需要搜索的模式。

Results are returned but only for my default schema and not for the schema I need to be searching in.

我也尝试了下面的内容，但它只是错误

I also tried the below but it just errors

SELECT * FROM SchemaName.user_source
WHERE UPPER(text) LIKE '%blah%'

推荐答案

 SELECT * FROM ALL_source WHERE UPPER(text) LIKE '%BLAH%'

编辑添加其他信息：

EDIT Adding additional info:

 SELECT * FROM DBA_source WHERE UPPER(text) LIKE '%BLAH%'

不同之处在于dba_source将具有所有存储对象的文本。 All_source将具有执行查询的用户可访问的所有存储对象的文本。 Oracle数据库参考11g第2版（11.2）

The difference is dba_source will have the text of all stored objects. All_source will have the text of all stored objects accessible by the user performing the query. Oracle Database Reference 11g Release 2 (11.2)

另一个区别是您可能无法访问dba_source。

Another difference is that you may not have access to dba_source.

这篇关于在Oracle存储过程中搜索文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！