PL/SQL- Variable Scope and Visibility

Programming in PL/SQL requires you to understand the existence and availability of declared variables for correct coding. PL/SQL variable scope and visibility lets you understand where and when the variables can be used. Let’s understand this  

PL/SQL Variables Scope

The scope of a variable is the block or blocks in which a PL/SQL variable is accessible. It means where it is declared and used. In PL/SQL variable scope is the block in which it is declared. The scope of a variable also extends to the nested blocks nested within the block where a variable is declared.

PL/SQL Variables Scope is of two kinds

Local– A variable declared in a block and accessed in the same block is local

Global– A variable declared in a block but accessed in all the sub-blocks is global

PL/SQL Variable Visibility

The visibility of a variable is defined as the portion of the PL/SQL block where the variable can be accessed without using a qualifier like a block label.

A variable cannot be declared more than once in the same block. A variable with same name can be declared in two different blocks. An inner block can have a variable name with same name that of a global variable. The local variable will override the global variable.

If multiple same level sub blocks are defined in a block, they can have variables with same name as that of global variables. Such variables exist as local in all these peer sub-blocks without conflict with each other. These blocks cannot reference variables declared in these peer blocks.

Note: You can declare variables of same name in a block and its inner blocks. In such case the inner block accesses its local variable. If you want to use a global variable in an inner block, the variable must be qualified with the label of the block.

How variables are accessed in blocks?

When a variable is accessed in an inner block, PL/SQL processor identifies whether a local variable with that name exists in the inner block. If it fails to find a local variable it tries to locate this variable in the outer PL/SQL block as global variable.

Example 1

In this example local and global variables are declared. You can see that inner block can use local as well as global variables. In outer block only global variable  can be accessed.

DECLARE
gl_name varchar2(40):='Global Variable';
BEGIN
    DBMS_OUTPUT.PUT_LINE(gl_name);
    DECLARE
    lc_name varchar2(20):='Local Variable';
    BEGIN
    DBMS_OUTPUT.PUT_LINE(lc_name);
    DBMS_OUTPUT.PUT_LINE(gl_name);
    gl_name:='Updated in INNER BLOCK';
    END;
    DBMS_OUTPUT.PUT_LINE(gl_name);
END;   

Example 2

In this example local and global variables are declared. When inner block’s local variable is accessed in  outer block an error is displayed.

Example 3

In this example local and global variables with same name are declared. You can see that inner block can global variable with a qualifier – the label of the outer block.

<<mainblock>>
DECLARE
samename varchar2(40):='Global Variable';
BEGIN
    DBMS_OUTPUT.PUT_LINE(samename);
    DECLARE
    samename varchar2(40):='Local Variable';
    BEGIN
    DBMS_OUTPUT.PUT_LINE(samename);
    DBMS_OUTPUT.PUT_LINE(mainblock.samename);
    samename:='Updated in INNER BLOCK';
    DBMS_OUTPUT.PUT_LINE('local variable updated in inner block '||samename);
    DBMS_OUTPUT.PUT_LINE('Global variable updated in inner block '||mainblock.samename);
    END;
    DBMS_OUTPUT.PUT_LINE('Global variable after exiting inner block '||samename);
    samename:='Global variable updated in OUTER BLOCK';
    DBMS_OUTPUT.PUT_LINE(samename);
END;

Example 4

In this example three inner blocks are created at same level with same name for local variables. You can see that inner block same variables do not conflict with each other’s local variables. This explains PL/SQL Variable Scope

DECLARE
same varchar2(20):='same name';
BEGIN
DBMS_OUTPUT.PUT_LINE(same);
DECLARE
samepeer varchar2(20):='same peer 1';
BEGIN
    DBMS_OUTPUT.PUT_LINE(samepeer);
END;
DECLARE
samepeer varchar2(20):='same peer 2';
BEGIN
    DBMS_OUTPUT.PUT_LINE(samepeer);
END;
DECLARE
samepeer varchar2(20):='same peer 3';
BEGIN
    DBMS_OUTPUT.PUT_LINE(samepeer);
END;
END;

admin