How do I get a list of function names in a PL SQL file?

Question

I am trying to write a program which will print a list of all the function names in a PL/SQL file and the subsequent function calls of each function.

Eg -

FUNCTION Fn_ABC (field_status    IN     VARCHAR)
  BEGIN
     Dbg('In Fn_ABC ');
     IF NOT  Fn_xyz(field_status)  THEN
     Dbg('Failed in Fn_ABC');
        field_status      := 'T';
        RETURN FALSE;
     END IF;
END Fn_ABC;

The output on running the required code on the above file should be:

Fn_ABC
    Fn_xyz

A depth first traversal through each function seems to be the logical choice but I am confused as to how to run it to get each function name.

@Sam This is not a duplicate you posted. Your post is referrring to SQL-SERVER and question is on Oracle PL/SQL. — XING, Jan 22 '18 at 06:30
I wasn't sure what a "PL/SQL file" was but it appears that it refers to PL/SQL source code in an OS file. Parsing a file rather than querying the dictionary makes the task orders of magnitude harder, as normally PL/SQL is for database processing and doesn't have anything to do with files. — William Robertson, Jan 22 '18 at 10:11

score 1 · Answer 1 · answered Jan 22 '18 at 06:30

1

No need to traverse the .sql file. Once you have created the function in database, just run this query to get all functions which your function depends on.

SELECT NAME, REFERENCED_NAME
  FROM ALL_DEPENDENCIES
 WHERE NAME = 'FN_ABC' AND TYPE = 'FUNCTION' AND REFERENCED_TYPE = 'FUNCTION';

answered Jan 22 '18 at 06:30

Kaushik Nayak

30,772
5
32
45

1

This will show the objects which reference `FN_ABC` **provided it's a standalone function**. USER_DEPENDENCIES shows Packages and Tables but doesn't slow packaged Procedures or variables. To see those lower level objects we need to use PL\Scope, which is discussed in the duplicate thread. – APC Jan 22 '18 at 10:38
@APC : Thats cool. Thanks. I wish if I worked with ( or under!) someone like you :) – Kaushik Nayak Jan 22 '18 at 10:53
@APC, PL/Scope surely? – William Robertson Jan 22 '18 at 11:50
@KaushikNayak - thanks, I appreciate the sentiment. William might not agree :) – APC Jan 23 '18 at 08:35
@WilliamRobertson - good spot. And it's more effort to type `\` on a mobile keyboard. What was I thinking? – APC Jan 23 '18 at 08:36

How do I get a list of function names in a PL SQL file?

1 Answers1