I intend to find all code in my database containing some string. I run a query such as:
SELECT DISTINCT name, type FROM all_source WHERE UPPER(text) LIKE UPPER(‘%string%’);
and a lot of results, let's say 100 results, contain 'PACKAGE BODY' on the TYPE column. Because a package body is too large, I want to get only the name of the procedure/function containing that text.
So I add the following clause
AND TYPE IN ('PROCEDURE', 'FUNCTION')
now i get only a few results, let's say 10 results, and I am confused.
Since a package body can contain only functions and procedures (apart from package constants), if 100 packages contain the text 'abc', how can only 10 procedures contain the same text? Text 'abc' withing a package can only fit within a procedure or a function, so if there are 100 occurences withing packages, there must be at least 100 occurences within functions/procedures.
I would need some explanation of this phenomena and, more importantly,I would like to know how can I narrow the search of text, to get the name of the containing procedure/function, instead of the parent package?
(a colleague suggested that some procedures are only imported, but not compiled; could this be a cause?)