实验环境

搭建平台：VMware Workstation

OS：RHEL 6.10

Grid&DB：Oracle 11.2.0.4

SQL参考

with x as

(select /*+ materialize */ owner,name,type,line,text,rownum rn from dba_source where

(upper(text) like '%END%LOOP%' or upper(text) like '%FOR%LOOP%'))

select a.owner, a.name, a.type

  from x a, x b

 where ((upper(a.text) like '%END%LOOP%' and

       upper(b.text) like '%END%LOOP%' and a.rn + 1 = b.rn) or

       (upper(a.text) like '%FOR%LOOP%' and

       upper(b.text) like '%FOR%LOOP%' and a.rn + 1 = b.rn))

   and a.owner = b.owner

   and a.name = b.name

   and a.type = b.type

   and a.owner = 'TEST';  --用户名