Oracle PL/SQL/PL SQL/Variable Scope

Identifiers defined in the declaration of the child block are only in scope and visible within the child block itself.

  
SQL>
SQL>
SQL> declare
  2    l_parent_number number;
  3  begin
  4    -- l_parent_number is visible and in scope
  5    l_parent_number := 1;
  6
  7    declare
  8      l_child_number number := 2;
  9    begin
 10      -- l_child_number is visible and in scope
 11      dbms_output.put_line("parent + child = " ||
 12                            to_char(l_parent_number + l_child_number));
 13    end;
 14
 15    -- l_child_number is now not visible nor in scope:
 16    l_child_number := 2;
 17  end;
 18  /
  l_child_number := 2;
  *
ERROR at line 16:
ORA-06550: line 16, column 3:
PLS-00201: identifier "L_CHILD_NUMBER" must be declared
ORA-06550: line 16, column 3:
PL/SQL: Statement ignored

SQL>
SQL>

if you try to reference a variable before you declare it because PL/SQL requires an identifier be declared before we use it in our code

  
SQL>
SQL>
SQL> declare
  2    myNumber number := another_number;
  3    another_number number := 10;
  4  begin
  5    null;
  6  end;
  7  /
  myNumber number := another_number;
                     *
ERROR at line 2:
ORA-06550: line 2, column 22:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 2, column 12:
PL/SQL: Item ignored

SQL>
SQL>
SQL> declare
  2    another_number number := 10;
  3    myNumber number := another_number;
  4  begin
  5    null;
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL>
SQL>

Illustrates the scope of various identifiers

 
SQL>
SQL> --Illustrates the scope of various identifiers.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     a_name  VARCHAR2(30) := "A Name";
  3
  4  PROCEDURE name_print IS
  5  BEGIN
  6     DBMS_OUTPUT.PUT_LINE(a_name);
  7  END;
  8
  9
 10  BEGIN
 11     DBMS_OUTPUT.PUT_LINE(a_name);
 12
 13     DECLARE
 14         b_name  VARCHAR2(30) := "a Name";
 15     BEGIN
 16         DBMS_OUTPUT.PUT_LINE("Inside nested block");
 17        DBMS_OUTPUT.PUT_LINE(a_name);
 18        DBMS_OUTPUT.PUT_LINE(b_name);
 19        name_print;
 20     END;
 21
 22         DBMS_OUTPUT.PUT_LINE("Back in the main block");
 23         --But we cannot compile the following line because b_name
 24        --is not defined in this block.
 25        --DBMS_OUTPUT.PUT_LINE(b_name);
 26        name_print;
 27  END;
 28
 29
 30  /
A Name
Inside nested block
A Name
a Name
A Name
Back in the main block
A Name
PL/SQL procedure successfully completed.
SQL>

Override your scope access to containing blocks by reusing an identifier in a nested block

  
SQL>
SQL> DECLARE
  2    current_block VARCHAR2(10) := "Outer";
  3    outer_block   VARCHAR2(10) := "Outer";
  4  BEGIN
  5    dbms_output.put_line("[current_block]["||current_block||"]");
  6    DECLARE
  7
  8      current_block VARCHAR2(10) := "Inner";
  9    BEGIN
 10      dbms_output.put_line("[current_block]["||current_block||"]");
 11      dbms_output.put_line("[outer_block]["||outer_block||"]");
 12    END;
 13    dbms_output.put_line("[current_block]["||current_block||"]");
 14  END;
 15  /
[current_block][Outer]
[current_block][Inner]
[outer_block][Outer]
[current_block][Outer]
PL/SQL procedure successfully completed.
SQL>

Reference Type in another block as well

  
SQL>
SQL> CREATE OR REPLACE TYPE NameList AS
  2    VARRAY(20) OF VARCHAR2(30);
  3  /
Type created.
SQL>
SQL>
SQL>
SQL> DECLARE
  2    v_Names2 NameList;
  3  BEGIN
  4    NULL;
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL>

This type is local to this block

  
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE NameList AS
  2    VARRAY(20) OF VARCHAR2(30);
  3  /
Type created.
SQL>
SQL>
SQL> DECLARE
  2
  3    TYPE DateList IS VARRAY(10) OF DATE;
  4
  5    v_Dates DateList;
  6    v_Names NameList;
  7  BEGIN
  8    NULL;
  9  END;
 10  /
PL/SQL procedure successfully completed.
SQL>
SQL>

Variable scope in a PL/SQL

 
SQL>
SQL>
SQL> declare                                -- begin first block
  2    l_text varchar2(20);
  3    begin
  4      l_text := "First Block";
  5      dbms_output.put_line(l_text);
  6      declare                            -- begin second block
  7        l_more_text varchar2(20);
  8      begin
  9        l_more_text := "Second Block";
 10        dbms_output.put_line(l_more_text);
 11      end;                               -- end second block
 12    end;                                 -- end first block
 13  /
First Block
Second Block
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

Variable scope in variable "declare" block

 
SQL>
SQL>
SQL> -- variable scope
SQL>
SQL>  declare
  2      l_number number := l_another_number;
  3      l_another_number number := 10;
  4    begin
  5      null;
  6    end;
  7    /
    l_number number := l_another_number;
                       *
ERROR at line 2:
ORA-06550: line 2, column 24:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 2, column 14:
PL/SQL: Item ignored

variable scope with nested block

  
SQL>
SQL>  declare
  2      l_parent_number number;
  3    begin
  4      
  5      l_parent_number := 1;
  6
  7      declare
  8        l_child_number number := 2;
  9      begin
 10        
 11        dbms_output.put_line("parent + child = " ||
 12                              to_char(l_parent_number + l_child_number));
 13      end;
 14
 15      
 16      l_child_number := 2;
 17    end;
 18    /
    l_child_number := 2;
    *
ERROR at line 16:
ORA-06550: line 16, column 5:
PLS-00201: identifier "L_CHILD_NUMBER" must be declared
ORA-06550: line 16, column 5:
PL/SQL: Statement ignored

SQL>