# 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