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.

   <source lang="sql">
 

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>

 </source>
   
  

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

   <source lang="sql">
 

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>

 </source>
   
  

Illustrates the scope of various identifiers

   <source lang="sql">

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>

 </source>
   
  

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

   <source lang="sql">
 

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>

 </source>
   
  

Reference Type in another block as well

   <source lang="sql">
 

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>

 </source>
   
  

This type is local to this block

   <source lang="sql">
 

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>

 </source>
   
  

Variable scope in a PL/SQL

   <source lang="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>

 </source>
   
  

Variable scope in variable "declare" block

   <source lang="sql">

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

 </source>
   
  

variable scope with nested block

   <source lang="sql">
 

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>

 </source>