Oracle and PL/SQL Recipes: A Problem-Solution Approach

1-9. Referring to Variables from Nested Blocks

Problem

A variable that is defined in an outer code block needs to be used within an inner block. However, there is also a variable of the same name within the inner block. Thus, two variables with the same name are in scope, and you need a mechanism for differentiating between them.

Solution

Label the code blocks, and use the labels to qualify the variable references. For instance, if a variable dept_name is defined in an outer code block, which is labeled outer_block, then you can use the fully qualified name outer_block.dept_name to reference that variable. Let's take a look at an example:

<<outer_block>> DECLARE    mgr_id NUMBER(6) := '&current_manager_id';    dept_count  number := 0; BEGIN ...