Oracle PL/SQL - Block with Multiple and Duplicate Labels

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Introduction
  4. Qualifying Redeclared Global Identifier with Block Label

Introduction

The following code has two labels for the outer block, my_code and my_label_one.

The second label appears again in the inner block.

In the inner block, my_label_one.my_val_two refers to the local variable my_val_two, not to the global variable my_val_two, which results in the error ZERO_DIVIDE.

Demo

SQL>
SQL> <<my_code>>-- from w  w w .j  ava2s .c om
  2  <<my_label_one>>
  3  DECLARE
  4    my_val   NUMBER := 22;
  5    my_val_two NUMBER := 7;
  6  BEGIN
  7    <<my_label_one>>
  8    DECLARE
  9      my_val_two NUMBER := 0;
 10    BEGIN
 11      DBMS_OUTPUT.PUT_LINE('using my_code.my_val_two');
 12      DBMS_OUTPUT.PUT_LINE(my_val/my_code.my_val_two);
 13
 14      DBMS_OUTPUT.PUT_LINE('using my_label_one.my_val_two');
 15      DBMS_OUTPUT.PUT_LINE(my_val/my_label_one.my_val_two);
 16
 17    EXCEPTION
 18      WHEN ZERO_DIVIDE THEN
 19        DBMS_OUTPUT.PUT_LINE('Divide-by-zero error: can''t divide '|| my_val || ' by ' || my_val_two);
 20      WHEN OTHERS THEN
 21        DBMS_OUTPUT.PUT_LINE('Unexpected error.');
 22    END my_label_one;
 23  END my_code;
 24  /
using my_code.my_val_two
3.14285714285714285714285714285714285714
using my_label_one.my_val_two
Divide-by-zero error: can't divide 22 by 0

PL/SQL procedure successfully completed.

SQL>
SQL>

Related Topic