프로시저나 펑션내용 검색하기

USER_SOURCE 뷰를 이용하여 검색하면 된다.

SELECT *

FROM   USER_SOURCE

WHERE  TYPE = 'PROCEDURE'

AND    text like  '%hello%'

ORDER BY name, line

만약 USER_SOURCE 뷰가 없다면 아래와 같이 생성해주면 됩니다.

CREATE OR REPLACE VIEW USER_SOURCE

(NAME, TYPE, LINE, TEXT)

AS 

select o.name,

decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',

               11, 'PACKAGE BODY', 13, 'TYPE', 14, 'TYPE BODY',

               'UNDEFINED'),

s.line, s.source

from sys.obj$ o, sys.source$ s

where o.obj# = s.obj#

  and o.type# in (7, 8, 9, 11, 13, 14)

  and o.owner# = userenv('SCHEMAID')

union

select o.name, 'JAVA SOURCE', s.joxftlno, s.joxftsrc

from sys.obj$ o, x$joxfs s

where o.obj# = s.joxftobn

  and o.type# = 28

  and o.owner# = userenv('SCHEMAID')