Get all the variables in an Oracle Package, Procedure or Function

This demo illustrates how to use general sql parser to get all the variables in an Oracle Package, Procedure or Function that are in scope based on a line number.

For example, if we specify a line number that is within a specific procedure in a package, this demo will let you know which variables are defined for that procedure, not any others. The same for stand alone procedures and functions that are not part of a package.

Let’s take this SQL for example:

01  create or replace
02  package body pkg_dbgd as
03    function tst_1(i in integer) return integer is
04    begin
05      if i between 5 and 10 then
06        return 2*i;
07      end if;
08  
09      if i between 0 and 4 then
10        return tst_2(3+i);
11      end if;
12  
13      if i between 11 and 15 then
14        return tst_2(i-2);
15      end if;
16  
17      return i;
18    end tst_1;
19  
20    function tst_2(var1 in integer) return integer as
21  	myreturn integer;
22    begin
23  	  myreturn := var1-1;
24    
25      if var1 between 6 and 8 then
26        myreturn := fn_add_numbers(var1,10);
27      end if;
28  
29      if var1 between 1 and 5 then
30        myreturn := fn_add_numbers(var1,5);
31      end if;
32  	
33      return myreturn;
34    end tst_2;
35  end pkg_dbgd

Lets say which variables would be available in scope at line 25,
this demo will show you myreturn (Integer) and var1 (integer).
that would be all the variables for the function tst_2.

Download this demo: C# version