Oracle PL/SQL - Scope and Visibility of Identifiers

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Introduction
  4. %TYPE Attribute

Introduction

The scope of an identifier is where you can reference the identifier without qualifying it.

An identifier is local to the PL/SQL unit that declares it. If that unit has inner units, the identifier can be access from the inner units.

If an inner unit redeclares a global identifier, then inside the inner unit, both identifiers are in scope, but only the local identifier is visible.

Global

In this situation the global variable is shadowed by the local variable.

To reference the global identifier, the inner unit must qualify it with its unit name of the unit that declared it.

If that unit has no name, then the inner unit cannot reference the global identifier.

A PL/SQL unit cannot reference identifiers declared in other units at the same level.

Example

The following code shows the scope and visibility of several identifiers.

The first sub-block redeclares the global identifier a.

To reference the global variable a, the first sub-block would have to qualify it with the name of the outer block.

The outer block has no name. Therefore, the first sub-block cannot reference the global variable a.

It can reference only its local variable a.

Because the sub-blocks are at the same level, the first sub-block cannot reference d, and the second sub-block cannot reference c.

Demo

SQL>
SQL> -- Outer block:-- from   w  w w .  jav  a 2 s.  co m
SQL> DECLARE
  2    a CHAR;  -- Scope of a (CHAR) begins
  3    b REAL;    -- Scope of b begins
  4  BEGIN
  5       -- Visible: a (CHAR), b
  6
  7       -- First sub-block:
  8       DECLARE
  9          a INTEGER;  -- Scope of a (INTEGER) begins
 10          c REAL;     -- Scope of c begins
 11       BEGIN
 12          -- Visible: a (INTEGER), b, c
 13          NULL;
 14       END;          -- Scopes of a (INTEGER) and c end
 15
 16       -- Second sub-block:
 17
 18       DECLARE
 19         d REAL;     -- Scope of d begins
 20       BEGIN
 21         -- Visible: a (CHAR), b, d
 22         NULL;
 23       END;          -- Scope of d ends
 24
 25       -- Visible: a (CHAR), b
 26  END;            -- Scopes of a (CHAR) and b end
 27   /

PL/SQL procedure successfully completed.

SQL>

Related Topic