1

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?)

Improve this question

1 Answer 1

Reset to default
0

Your search will have to be more complicated. Stored code can be in the package header which is not done that often. Most often it is in PACKAGE BODY. If you modified your query to read

SELECT DISTINCT name, type FROM all_source WHERE UPPER(text) 
LIKE UPPER(‘%string%’) and type ='PACKAGE BODY';

This gives all the code that meets your criteria in package bodies. The line number is all you can get out of all_source. The fields available in all_source are

OWNER VARCHAR2(30)     Owner of the object 
NAME  VARCHAR2(30)     Name of the object 
TYPE  VARCHAR2(12)     Type of object: FUNCTION, JAVA SOURCE, PACKAGE,PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY 
LINE  NUMBER           Line number of this line of source 
TEXT  VARCHAR2(4000)   Text source of the stored object

When you search for type PROCEDURE or FUNCTION you are looking at the stand alone code blocks that are not part of a package.

If the style of coding includes local procedures or functions within procedures or functions in a package body this could get complicated fast. You could look for the mandatory syntax that precedes a code block: PROCEDURE xxx or FUNCTION xx is and the code that ends it END ;

So your search would be:

  • find all procedure or function names in a package or package body and the line number
  • find the line number of the text you are looking for
  • write some logic to find the name of the code block that contains the text

Or you could use a GUI tool like TOAD or SQL Developer which will do this for you.

Improve this answer
2
  • thanks for clarifying that type=PROCEDURE would bring only stand alone procedures that are not part of a package body. I suspected that, but i needed a confirmation Commented Apr 9, 2019 at 7:18
  • actually, I do not get the line number, but I can get a code snippet containing that text, with SELECT DISTINCT name, type, text FROM all_source ... Commented Apr 9, 2019 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.