Oracle Variable Scope

Question

I'm fairly new to Oracle, but have had a good search. Can someone explain the scoping rules in relation to this:

BEGIN
    DECLARE 
        Variable1 number := 1;

    BEGIN
       DECLARE 
        Variable2 number := 2;


        BEGIN  
            dbms_output.put_line('Variable1: ' || Variable1); 
            dbms_output.put_line('Variable2: ' || Variable2); 
        END;

        BEGIN  
            dbms_output.put_line('Variable1: ' || Variable1); 
            dbms_output.put_line('Variable2: ' || Variable2); 
        END;
    END;
END;

errors with:

Error report - ORA-06550: line 17, column 55: PLS-00201: identifier 'VARIABLE2' must be declared ORA-06550: line 17, column 17: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Why is Variable2 unavailable in my second attempt to output it? Is there a way to achieve access to Variable2 within the second BEGIN...END block, or is this just the way Oracle works?

I'm using Oracle 12c. Thanks!

Turo · Accepted Answer · 2017-10-31 13:22:36Z

3

I think your indentation is fooling you: A block is (the DECLARE part is optional)

DECLARE
   ...
BEGIN
   ...
END

and the second output is clearly after the block in which Variable2 is declared.

answered Oct 31, 2017 at 13:22

Turo

5,0442 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cpkendall Over a year ago

Thanks, @Turo, that explains it perfectly...essentially swapping the declares to approve the appropriate begins, solved my problem.

MT0 · Accepted Answer · 2017-10-31 13:30:21Z

A PL/SQL block has the format:

DECLARE
  -- your variables
BEGIN
  -- your statements
END;

The DECLARE part of the block is optional (but is still part of the block)

So your code is:

BEGIN                         -- Block 1 Start
  DECLARE                     -- Block 1.1 Start
    Variable1 number := 1;
  BEGIN
    DECLARE                   -- Block 1.1.1 Start
      Variable2 number := 2;
    BEGIN  
      NULL;
    END;                      -- Block 1.1.1 End

    BEGIN                     -- Block 1.1.2 Start
      NULL;
    END;                      -- Block 1.1.2 End
  END;                        -- Block 1.1 End
END;                          -- Block 1 End

You have 4 PL/SQL blocks:

Block 1 has no local variables.
Block 1.1 is nested inside Block 1 and declares the local variable variable1
Block 1.1.1 is nested inside Block 1.1 (which is nested inside Block 1) and declares the variable variable2 and can access its surrounding block's scopes so can see variable1.
Block 1.1.2 is nested inside Block 1.1 (which is nested inside Block 1) and has no local variables but can access its access its surrounding block's scopes so can see variable1. variable2 is not in an ancestor's scope but is in a sibling and is not visible.

Collectives™ on Stack Overflow

Oracle Variable Scope

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related