facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: Nested Blocks

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.

You have already been introduced to PL/SQL blocks. Blocks are the fundamental structure within which PL/SQL code is written. They can be nested inside of each other to organize code, to limit variable scope or visibility, or to affect exception handling. Scope is the context in which a given variable can be accessed within a program. Scope is relevant when considering where a variable will be declared and where it will be referenced. Depending upon where it is declared, a variable might be accessible using its name, accessible using a qualified name or not accessible at all. Both traditional variables and exceptions are affected by scope considerations. In addition, the use of the EXCEPTION section of a block can affect the way errors are handled by your program.

Lesson Goals

  • Learn about nested blocks
  • Learn about variable scope
  • Learn about variable visibility

Nesting Blocks

The ability to nest blocks in PL/SQL will allow you to better organize your programs. Blocks can be used to prevent variables from being accessed improperly and to allow exceptions to be handled effectively.

Code Sample:

Nested-Blocks/Demos/nested_block.sql
begin

   dbms_output.put_line('In the main block.');

   begin
   
    dbms_output.put_line('In a nested block.');
   
   end;
   
end;

Code Explanation

This is a simple example of how PL/SQL blocks can be nested inside of one another. When the example is run, the two lines of text are printed out in sequence. This shows that when a BEGIN keyword is encountered during normal execution (rather than in the context of a raised error) control will drop to the nested block.

Scope of Variables

The scope of an identifier is the area within a program unit where it can be referenced.

Code Sample:

Nested-Blocks/Demos/nested_block_variable_scope.sql
declare
  x NUMBER := 1;
begin

   dbms_output.put_line('In the main block.  x = '
                        || x);

   declare 
     x NUMBER := 2;
   begin
   
    dbms_output.put_line('In a nested block. x = '
                         || x);
   end;
   
   dbms_output.put_line('In the main block.  x = '
                        || x);
   
end;

Code Explanation

This example shows how a variable with the same name as a variable in an enclosing block effectively masks the outer variable. When the example is run, the value of x varies based upon the context of the DBMS_OUTPUT.PUT_LINE call.

Code Sample:

Nested-Blocks/Demos/nested_block_variable_scope2.sql
declare
  x NUMBER := 1;
begin

   dbms_output.put_line('In the main block.  x = '
                        || x);

   declare 
     --Removed the local variable x 
   begin
    dbms_output.put_line('In a nested block. x = '
                         || x);
   end;
   
   dbms_output.put_line('In the main block.  x = '
                        || x);
   
end;

Code Explanation

In this example, the local variable x from the inner block is removed. As a result the variable from the outer block is visible.

Code Sample:

Nested-Blocks/Demos/nested_block_variable_scope_qualifier.sql
<<outer_block>>
declare
  x NUMBER := 1;
begin

   dbms_output.put_line('In the main block.  x = '
                        || x);

   declare 
     x varchar(1) := 'a';
   begin
    dbms_output.put_line('In nested block. outer x = '
                         || outer_block.x);
                         
    dbms_output.put_line('In nested block. inner x = '
                         || x); 
   end;
   
   dbms_output.put_line('In the main block.  x = '
                        || x);
   
end;

Code Explanation

This example demonstrates the use of a labeled block which is used to identify a variable that is named in two different scopes within the script. NOTE: There is a bug in some versions of Oracle software that causes an error (ORA-600) network disconnect when outer labeled blocks are used. This was left in the lesson to emphasize the point that the Oracle database can have bugs that need to be repaired through patches applied by a DBA.

Scope of Exceptions

The use of nested blocks will also affect the path of execution when exceptions are raised. When an exception occurs, any exception handler for the block that is associated with the exception will run. If there is no handler for the block, the exception propagates to the enclosing block. If there is no enclosing block, the exception is reported as a runtime error to the calling environment.

Code Sample:

Nested-Blocks/Demos/exception_inner_block_ignored.sql
declare
  x NUMBER;
begin
   
   dbms_output.put_line('In the main block.');
   
   begin
    dbms_output.put_line('In a nested block.');
    
    x := 1/0;
    
    dbms_output.put_line('Still in a nested block.');  
   end;
   
   dbms_output.put_line('At end of the main block.');
   
end;

Code Explanation

In this example, an ORA-01476: divisor is equal to zero is raised and thrown to the outer block. It continues to propagate since it was not handled.

Code Sample:

Nested-Blocks/Demos/exception_outer_block.sql
declare
  x NUMBER;
begin
   
   dbms_output.put_line('In the main block.');
   x := 1/0;  
   dbms_output.put_line('Still in the main block.');
   
   begin
    dbms_output.put_line('In nested block.');
   exception
    when others then
      dbms_output.put_line('Caught an exception');
   end;
   
   dbms_output.put_line('At end of the main block.');
   
end;

Code Explanation

If an exception does occur in an outer block, a subsequent inner block will be skipped (as will any other executable lines of code in the remainder of the outer block prior to the EXCEPTION section). Any exception section in an inner block will not handle an exception raised in an outer block.

Code Sample:

Nested-Blocks/Demos/exception_inner_block_handled.sql
declare
  x NUMBER;
begin
   
   dbms_output.put_line('In the main block.');
   
   begin
    dbms_output.put_line('In a nested block.');
    x := 1/0;  
    
   exception
      when others then
        dbms_output.put_line('caught an exception');
   end;
   
   dbms_output.put_line('At end of the main block.');
   
end;

Code Explanation

An exception handler in an inner block can be used to address exceptions raised within its scope.

Code Sample:

Nested-Blocks/Demos/exception_inner_block_handled_raised.sql
declare
  x NUMBER;
begin
   
   dbms_output.put_line('In the main block.');
   
   begin
    dbms_output.put_line('In a nested block.');
    x := 1/0;  
   exception
      when others then
        dbms_output.put_line('caught an exception');
        raise;
   end;
   
   dbms_output.put_line('At end of the main block.');
   
end;

Code Explanation

If needed, an exception can be handled in an inner block and subsequently re-raised (using the RAISE keyword) to propagate to the outer block.

Nested Subprograms

Nesting does not only apply to blocks themselves. Like other programming languages, PL/SQL allows you to nest control structures (conditional statements and loops). In addition, subprograms can be nested in certain situations but it is more common to use packages as containers for subprograms. It is not important to understand the details of the subprograms presented in the examples. Simply note that it is possible to create functions or procedures in other subprograms, whether anonymous blocks of PL/SQL code or stored.

Code Sample:

Nested-Blocks/Demos/nested_subprogram_in_anonymous.sql
declare
 CURSOR crs is 
            SELECT street_address, city,
                   state_province, postal_code
            FROM locations;
              
 type location_type is record
  (street_address   locations.street_address%type,
   city             locations.city%type,
   state_province   locations.state_province%type,
   postal_code      locations.postal_code%type);
     
 location_rec location_type;

 procedure process_locations
 as
 begin
  if crs%rowcount = 5 then
   -- force an exception for the 5th location
   raise value_error;
  end if;
  dbms_output.put_line(location_rec.street_address
                       || ', '
                       || location_rec.city
                       || ', '
                       || location_rec.state_province
                       || ' '
                       || location_rec.postal_code);
  exception
  when others then
   dbms_output.put_line('Problem with Location #'
                        || crs%rowcount);
 end process_locations;

begin

 if not crs%isopen then
  open crs;
 end if;

 loop
  fetch crs into location_rec;
    
  exit when crs%notfound;
 
  process_locations;
 end loop;

 if crs%isopen then
  close crs;
 end if;
  
exception
 when others then
  if crs%isopen then
   close crs;
  end if;
end;
/

Code Explanation

One or more subprograms (functions or procedures) can be nested within an anonymous block of PL/SQL code by declaring them in the DECLARE block. This effectively creates a subprogram that can be called one or more times from within the executable section of the anonymous block of PL/SQL code.

Note that any variables declared within the nested subprogram are only within the scope of the nested program and are not visible to the enclosing anonymous block of PL/SQL code.

Code Sample:

Nested-Blocks/Demos/nested_subprograms.sql
create or replace PROCEDURE outer_procedure  IS

   x VARCHAR2(10):= 'outer';
  
   FUNCTION inner_function  RETURN VARCHAR2 IS

      x VARCHAR2(10):= 'inner';

   BEGIN
      RETURN x;     
   END;
   
BEGIN
  dbms_output.put_line('Outer: ' || x);
  dbms_output.put_line('Inner: ' ||
                       inner_function());
END;

Code Explanation

A function or procedure can be nested within another stored subprogram. The nesting occurs in the DECLARE block. This effectively creates a subprogram that is private to the enclosing subprogram.

Variables within a nested subprogram are not visible to the enclosing subprogram. Note the following line in the procedure, results in an error that indicates subprogram or cursor 'INNER_FUNCTION' reference is out of scope

dbms_output.put_line('Inner: '||inner_function.x);

In a similar manner, a call to the following will result in an error as well that indicates subprogram or cursor 'OUTER_PROCEDURE' reference is out of scope

outer_procedure.inner_function()

Exception Propagation in Nested Blocks

Duration: 10 to 15 minutes.
  1. Look at the code in the exercise.
  2. Answer the following questions:
    • Will the code compile as written?
    • Is the variable my_exception visible at the time it is raised? Why or why not?
    • Will my_exception be handled in any/all of the exception handling sections?
    • What do you expect the output of the program to be?
    • See the solution below or execute the code for yourself.

Code Sample:

Nested-Blocks/Exercises/nested_blocks_and_exceptions.sql
declare
  my_exception exception;
begin

   dbms_output.put_line('Begin processing in outer block.');

  begin
    dbms_output.put_line('Begin processing in inner block.');
    raise my_exception;
    -- Will never get here.
    dbms_output.put_line('Completed inner block.');
  exception
    when my_exception then
      dbms_output.put_line('Caught exception in the inner block: '
                           || substr(sqlerrm, 1, 100));
  end;

  dbms_output.put_line('Completed outer block.');  

exception
  when others then
    dbms_output.put_line('Caught exception in the outer block.');
end;

Code Explanation

Solution:

Nested-Blocks/Solutions/nested_blocks_and_exceptions.txt
Begin processing in outer block.
Begin processing in inner block.
Caught exception in the inner block: User-Defined Exception
Completed outer block.

Code Explanation

Block Variable Visibility

Duration: 10 to 15 minutes.
  1. Create a block that contains a declaration of a numerical variable named x.
  2. Retrieve a count of records in the employees table into x.
  3. Create a inner block with a declaration section where a second numeric variable named x is declared.
  4. Within the inner block, retrieve a count of records in the departments table into x.
  5. Display the value of x (containing the count of records in the departments table).
  6. Within the outer block (following the inner block) display the number of records in the employees table (retrieved into x in the outer block earlier).

Solution:

Nested-Blocks/Solutions/inner_outer_block.sql
declare
 x NUMBER;
begin
  SELECT count(*)
  into x
  from employees;
  
  declare
    x NUMBER;
  begin
    SELECT count(*)
    into x
    from departments;
    dbms_output.put_line('Departments ' || x);
  end;

  dbms_output.put_line('Employees ' || x);
end;
/

Code Explanation