1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
| SELECT *
FROM
( SELECT line
, last_value( proc IGNORE NULLS )
OVER ( PARTITION BY name
ORDER BY line
RANGE BETWEEN 10000 PRECEDING
AND 0 FOLLOWING ) proc
, text
FROM
( SELECT line
, name
, DECODE( proc, 0, null, UPPER(text) ) proc
, DECODE( proc, 0, TEXT, null ) text
FROM
( SELECT line
, name
, INSTR( UPPER(text), 'PROCEDURE' )
+ INSTR( UPPER(text), 'FUNCTION' ) proc
, TRIM(text) text
FROM user_source
WHERE NAME = :mon_package
AND ( UPPER (text) LIKE '%UPPER%'
OR UPPER (text) LIKE '%LOWER%'
OR UPPER (text) LIKE '%LIKE%'
OR UPPER (text) LIKE '%PROCEDURE%'
OR UPPER (text) LIKE '%FUNCTION%'
)
AND TYPE LIKE 'PACKAGE BODY'
ORDER BY line ) ) )
WHERE text IS NOT NULL ; |
Partager