Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# 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